Running Comparison

[Robert V Giancarlo]

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.

[#2]

Hello Robert

=SUM(A2:INDEX(2:2,COUNT(A2:J2)))-SUM(A1:INDEX(1:1,COUNT(A2:J2)))

[#3]

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

[#4]

So you want a formula for every column?

For column A:

=IF(COUNT(A1:A2)=2,SUM(A$2:A2,-A$1:A1),"")

[#5]

 Thanks again Detlef.  I sent you a private message to try to explain my request clearer.  Please let me know if you got it.

[#6]

This is a public forum. Please do not respond via PN.

[#7]

Sorry.  I wanted to be more specific with you since you were the one communicating with me and offering the most help.

I'll keep it here from here on out.

Robert

[#8]

So Detlef has been very helpful and provided much input to this effort.  I'd like to try to clarify my request, still not even knowing if it is possible to do what I want.

I have the following inputs to the cells in an excel spreadsheet.

COLUMNS     A   B   C   D   E    F    G   H   I    J                 K

ROW1            1   3   5   7   10  15   5   8   2   5                                  

ROW2            3   4   7   13   9                                       (Running comparison formula which changes with each new input)  

K2 would be the cell I wish to display the result after each new input to the cells in ROW2.  Right now the value in K2 would be +10, comparing the sum of values in ROW1(A:E) and ROW2(A:E).  If I input 4 in cell F2, the result would now be -1 comparing the sum of values in ROW1(A:F) and ROW2(A:F).  Entering another value in G2 would again change the result which I would like to update the value in K2.

Is this possible to expound on or alter the forumlas that Detlef has provided?  Is this even possible to do?

Thank you all again for your help.

Robert

[#9]

That is exactly what my first formula does.

[#10]

So it does sir, when I try it on a new sheet and from the A column.

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 but I am getting an error when I input the formula.  Also after entering the second value (using rows 6 and 7 and columns B through M for data) in C7 the formula does not update.  After entering the subsequent value at D7 the formula updates to the result I should have obtained after C7.

I am editing my sheet as shown below.

=SUM(B7:INDEX(7:7,COUNT(B7:M7)))-SUM(B6:INDEX(6:6,COUNT(B7:M7)))

How do I correct this?

Robert

[#11 · Accepted solution]

*

---

@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))

[#12]

Sir, that looks like exactly what I am looking for.  Thank you very much for your time and assistance.

Robert

[#13]

do you want it on every column or just that particular column?