Forum Discussion
Running Comparison
I would like to maintain a running comparison of values from year to year. I would like to do so in the following fashion:
1 3 5 7 10 15 5 8 2 5 61 (26 after the same number of inputs from the previous line)
3 4 7 13 9 36
Current difference would be +10, however, if my next value was 3, the comparison then becomes 41 and 39 yielding a difference of -2.
How can I create the formula to maintain and display the current comparison.
Thank you for your help.
*
Robert V Giancarlo wrote:I have string values in the A column on my sheet identifying what each row is and my data values begin with the B column thru M column
That is important information. The formula has to reflect this:
=SUM(B7:INDEX(7:7,COUNT(B7:M7)+1))-SUM(B6:INDEX(6:6,COUNT(B7:M7)+1))
- Detlef_LewinSilver Contributor
Hello Robert
=SUM(A2:INDEX(2:2,COUNT(A2:J2)))-SUM(A1:INDEX(1:1,COUNT(A2:J2)))
- Robert V GiancarloCopper Contributor
Thanks Detlef, however, I had a rudimentary formula doing that.
What that did was sum all of the 1 row and then keep the current comparison from the populated 2 row to that sum.
What I am looking for is to see if there is a way to keep the comparison current, ie.:
Returning to my example:
A B C D E F G H I J (SUM)
ROW1 1 3 5 7 10 15 5 8 2 5 61 Your formula yields a result of -25.
ROW2 3 4 7 13 9 4 36 I would like to see result at E Column +10 and then result after F Column input to yield -1 where the result changes with each new input. In other words totals after E column are ROW1 = 26 and ROW2 = 36. After input to F2, totals are ROW1 = 41 and ROW2 = 40.
Can you help me to adjust your formula to perform this operation if that is possible?
Thank you again very much for your assistance.
Robert
- Detlef_LewinSilver Contributor
So you want a formula for every column?
For column A:
=IF(COUNT(A1:A2)=2,SUM(A$2:A2,-A$1:A1),"")
- Gracie MillsCopper Contributordo you want it on every column or just that particular column?