Tried everything! SUM, SUMPRODUCT, OFFSET, MATCH, INDIRECT, etc. etc. Just want to do what should be pretty basic for Excel:
In A1 - 3000 or any value
In A2 - another value
In B1 (or any cell) return the sum of A1-A2, no problem with that , however, further values in A3, A4, A5 etc, i want Excel to subtract each one from the first value in A1 (at the time the value is input) and return me the answer in B1, (want the answer in the same cell) so really a continuing subtraction. Excel will do it really easily with addition but not subtraction. keep getting VALUE, REF errors all over the place. You are all far more advanced than me so please, please, help, getting desperate! Thank you.
Well, you can easily do something like:
=A1-SUM(A2:A5)
(or whatever range you have).
Remember that:
x - (a + b + c + d) = x - a - b - c - d
EDIT: Misunderstood your question.
Here's a formula to always calculate the difference between the first value in a column and the last value in that column:
=A1-INDEX(A:A,MATCH(9^99,A:A))
MATCH(9^99,A:A) finds the position of the last value in the column, that INDEX takes to return the actual value.
If you don't mind some performance issues:
=A1-LOOKUP(9^99,A:A)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments