Quizzes & Puzzles46 mins ago
Formulae in excel
Is it possible to have a formula in a cell that has 3 different possible results. What I am trying to do is this:
I want to use A2 and B2 as a reference. A2 will have a value of 0 or more than 1. B2 will have a monetary value. Based on this I would like a cell in another column (C) to do one of 3 things.
1. If the value of A2 = 0 then add the value of B2 to C1 thus creating a running total in Column C or
2. If the value of A2 =1or above then the value of the cell in C2 to be the same as B2 thus creating a new running total and start the totalizing in C over agin from that point.
This I have managed quite successfully but here's the hard part..
3. Make column C only show the values as far as entries go in Column A and B.
All my efforts so far result in either the formula needing to be dragged down daily or the entire column C reaching the bottom of the worksheet.
Any help greatly appreciated, come on kempie, where are you when you're needed?
I want to use A2 and B2 as a reference. A2 will have a value of 0 or more than 1. B2 will have a monetary value. Based on this I would like a cell in another column (C) to do one of 3 things.
1. If the value of A2 = 0 then add the value of B2 to C1 thus creating a running total in Column C or
2. If the value of A2 =1or above then the value of the cell in C2 to be the same as B2 thus creating a new running total and start the totalizing in C over agin from that point.
This I have managed quite successfully but here's the hard part..
3. Make column C only show the values as far as entries go in Column A and B.
All my efforts so far result in either the formula needing to be dragged down daily or the entire column C reaching the bottom of the worksheet.
Any help greatly appreciated, come on kempie, where are you when you're needed?
Answers
1 to 6 of 6
Best Answer
No best answer has yet been selected by camioneur. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.
G'Day
Try this formula:
=+IF(ISBLANK(A2),"",IF(A2=0,B2+C1,B2)) in column C from C2 down to where ever you want to finish. IsBlank(x) is true if there is nothing in cell x, otherwise false, so if there is nothing in the cell there will be nothing displayed in the that row in column C. If the second IF is tested that means there is something in Ax, so it will either add Bx and Cx-1 or copy Bx depending on the value in Ax.
Geoff
PS: Like kerplunk's suggestion you can change the second IF to whatever you like.
Try this formula:
=+IF(ISBLANK(A2),"",IF(A2=0,B2+C1,B2)) in column C from C2 down to where ever you want to finish. IsBlank(x) is true if there is nothing in cell x, otherwise false, so if there is nothing in the cell there will be nothing displayed in the that row in column C. If the second IF is tested that means there is something in Ax, so it will either add Bx and Cx-1 or copy Bx depending on the value in Ax.
Geoff
PS: Like kerplunk's suggestion you can change the second IF to whatever you like.
Sorry chaps, neither one works. If either of you would be good enough to mail me @ [email protected] I'll email a copy of my spreadsheet back to you, maybe what I ask isn't possible but if you don't ask......
G'Day
I think I have a solution for you (well at least a solution to resetting the total at the beginning of the month).
=IF(C2>0,IF(MONTH(D2)>MONTH(D1),G2,SUM(G2,L1)),0) where row 1 is your first data row. This formula goes in L2 and is copied down.
Explanation:
If Cx is zero then you get a zero, if not then you compare the months of Dx with Dx-1 (dates), if Dx is greater then you have the first used date of the new month (which does not have to be the first of the month) so your total is the value of Gx, otherwise the total is the running total upto Lx-1 plus Gx.
Hope this helps.
Do you mind if I have a play with the formulas and spreadsheet over the weekend? If not I will email you the results of my fiddling if you want it? Just email you answer.
Time for bed, it is nearly 2:30am Thursday ;)
Geoff
I think I have a solution for you (well at least a solution to resetting the total at the beginning of the month).
=IF(C2>0,IF(MONTH(D2)>MONTH(D1),G2,SUM(G2,L1)),0) where row 1 is your first data row. This formula goes in L2 and is copied down.
Explanation:
If Cx is zero then you get a zero, if not then you compare the months of Dx with Dx-1 (dates), if Dx is greater then you have the first used date of the new month (which does not have to be the first of the month) so your total is the value of Gx, otherwise the total is the running total upto Lx-1 plus Gx.
Hope this helps.
Do you mind if I have a play with the formulas and spreadsheet over the weekend? If not I will email you the results of my fiddling if you want it? Just email you answer.
Time for bed, it is nearly 2:30am Thursday ;)
Geoff
1 to 6 of 6
