Forum Discussion
Need a formula that has a running calculation
I have a spreadsheet with 6 columns:
1 - Monday totals
2 - Tuesday totals
3 - Wednesday totals
4 - Thursday totals
5 - Friday totals
6 - Difference
For #6, I need a formula that takes Monday totals and subtracts Tuesdays, then Wednesdays, then Thursdays, etc. So it would be one formula that calculates this on a daily basis once I update one of the numbers.
Is there such a formula?
Thanks
7 Replies
=SUM(INDIRECT("A2:A"&COUNTA(A:A)))-SUM(INDIRECT("B2:B"&COUNTA(B:B)))-SUM(INDIRECT("C2:C"&COUNTA(C:C)))-SUM(INDIRECT("D2:D"&COUNTA(D:D)))-SUM(INDIRECT("E2:E"&COUNTA(E:E)))
Maybe with this formula as shown in the attached file.
- Actually we figured out how to solve it: =IF(F14>0,F14-B14,IF(E14>0,E14-B14,IF(D14>0,D14-B14,IF(C14>0,C14-B14,0))))
Thanks!- glad you found a solution. I might suggest to simplify using IFS:
=IFS(F14>0,F14-B14,E14>0,E14-B14,D14>0,D14-B14,C14>0,C14-B14,1,0)
or you could try something more compact like:
=OFFSET(B14,0,COUNT(C14:F14))-B14
but if you understand the IF/IFS better then maybe stick with that.
- I might suggest using OFFSET instead INDIRECT since if things move or columns inserted etc... the text in INDIRECT will not change while cell references in OFFSET will.
That aside i would ask RonnieLCCC to explain a little more about the issue. Basically if you take A1-SUM(B1:E1) that will give you what you're asking (for row 1) assuming Wed, Th, F are empty until you insert the numbers. If you need the whole column as shown above then
=sum(A:A) - sum(B:E) should work
the only thing I'm guessing (and that is the problem, I'm guessing) is that maybe you have columns of M-F and rows for each week and at any given point you will be filling in a particular row and you want to sum mondays - T,W,Th,F but only up to the day you have filled in (i.e. prior rows are filled in but if this week is only on W then only sum M - T - W)? Try this:
= SUM(A:A) - SUM(B:B)*(count(B:B)=count(A:A)) - SUM(C:C)*(count(C:C)=count(A:A))- SUM(D:D)*(count(D:D)=count(A:A))- SUM(E:E)*(count(E:E)=count(A:A))
or in a more compact form:
=SUM(A:A) - IFERROR(SUM(OFFSET(B:B,0,0,,COUNT(INDEX(A:E,COUNTA(A:A),))-1)),0)- re-reading the OP, I'm thinking it is my first formula
=A1-SUM(B1:E1)
and then fill down as needed
- Thanks I'll try!
