•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Applying multiple row formulas to one cell
Our take
Managing a list of bills can be challenging, especially when dealing with varying increases. To calculate the increase amount in cell E1 based on the values in C1 and D1, you can use a nested IF function. This approach allows you to handle scenarios where either cell may be blank. For example, you can combine your current formula with a calculation for percentage increases from D1. By mastering these formulas, you can enhance your Excel skills and simplify your financial tracking.
Greetings,
I have a list of bills with increases and some with percentage increases I am trying to write the proper formula for. I believe it would be a nested IF function.
For example, A1 = title, B1 would be current amount, C1 would be dollar increase, D1 would be percentage increase, E1 would be the increase amount, and F1 would be the total of E1 + B1.
How would I write the formula for E1 to calculate based off cells C1 or D1 either having an amount of be blank? So far I have figured out IF(C1="",0,C1+B1) but can't figure out how to also have D1 also apply same rules, except multiplying a percentage.
I know I could do this a more manual way, but I have a college degree that used a bit of Excel and am always trying to use it to keep skills sharp and learn things I can't figure out, on my own.
Appreciate any input!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to sum a conditional formula across multiple cells?Hi, I'm looking for an elegant solution to this problem. Mostly just a curiosity because I currently do have it working, but I know there is a cleaner way to do this. Basically I am trying to sum the number values from a row of 5 cells, where the cell may or may not start with a letter (i.e., the row could be "8, 9, S6.5, 8, A6" and I want the result of 8+9+6.5+8+6=37.5). I have many rows of this, and the summed value ends up in the column to the right of the 5 values. I've managed to do this with the following formula, where the result is in cell J5: =IF(ISTEXT(E5), RIGHT(E5, LEN(E5)-1), E5) + IF(ISTEXT(F5), RIGHT(F5, LEN(F5)-1), F5) + IF(ISTEXT(G5), RIGHT(G5, LEN(G5)-1), G5) + IF(ISTEXT(H5), RIGHT(H5, LEN(H5)-1), H5) + IF(ISTEXT(I5), RIGHT(I5, LEN(I5)-1), I5) But, if I recall from years ago, there is a way to make the formula act on an array of cells using ctrl+shift+enter so the formula would essentially look like this, and do the same thing: ={SUM(IF(ISTEXT(E5:I5), RIGHT(E5:I5, LEN(E5:I5)-1), E5:I5))} Thing is, it doesn't seem to cooperate, and I have gotten pretty rusty with excel. This array formula will sum values but not if there is text in the cell. Example data: M T W Th F SUM T8 T8 9 9 5 39 9 8 8 A8 8 41 8 8 8 8 8 40 S8 9.5 8 8 6 39.5 submitted by /u/ganduvo [link] [comments]
- Sum of a range based on 2 different criteria?Hello, I'm working on a document which is a holiday funds to keep track of individual person's remaining balance to pay. I want to use a formula to automatically calculate the sum of a range of cells which lie against a certain year under a person's name. Below as an example: https://preview.redd.it/1p19n1o8qrxg1.png?width=991&format=png&auto=webp&s=2e67c0337da92ae1feffe43fc2c7fbaba237d672 As above, I want to look up Name 1 in 1:1, look up Year 1 in A:A and then return the sum of those numbers in that Year 1 range to give me my final result. Is this possible? I've tried all sorts of SUM, SUMIF, SUMIFS, XLOOKUP and combinations but can't seem to get it to work. I also don't want to have to manually select the cells for year SUM as this is constantly updating and want it to be automated. Thank you! submitted by /u/celestialspace [link] [comments]
- Is there a way to Sumifs up to a certain column with multiple criteria?Hi everyone, I’m looking for a formula to sum all of the columns starting from a particular column up to a particular column. How do I best do that? On the data tab, Row 1 I have the following columns: A1 = date B1 = serial number C1 = customer name D1 = date 1, with the data being $ E1 = date 2, with the data being $ F1 = date 3, with the data being $ In a separate tab I have the same thing mirroring the data tab, but without the $, for the calculation what I intend to do is on E2, I want to say if $A2 = E$ 1, then sum all of D up to E column on the data tab provided that the value on B column of the data tab match B2 of the calc tab, and C column of the data tab match C2 of the calc tab. For F2, it would be the same thing, but sum column D up to column F instead of column E. I hope it makes sense. Thanks for your help! submitted by /u/botng [link] [comments]
- Tiered Results Formula with Multiple OutcomesI know the basics but I'm a lil stumped on a formula to use for this specific situation. I have to build payroll spreadsheets for every employee based on their pay plans. Some of their plans were changed where they get a larger percentages based on the gross total for that month. To keep it simple, how would I build something to give a different percent based on the total in one cell. Just to keep it simple for this example. 0-100 pays 1% 101-300 pays 1.25% 300+ pays 1.5% I've done similar IF formulas but never quite exactly like this situation. I appreciate the help, as I'm still just learning and couldn't find an exact answer from Google based on how I was trying to phrase it. submitted by /u/swidgen504 [link] [comments]