0

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.

2

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)
  • Or, if you want an unlimited range (vertically), use =A1+(SUM(A:A)-A1). It will substract from A1 the value of whole column (excluding value of A1 itself). – Michał Sacharewicz Sep 15 '13 at 10:36
  • @MichałSacharewicz You surely mean =A1-(SUM(A:A)-A1) =P Could also do =A1*2-SUM(A:A). – Jerry Sep 15 '13 at 10:41
  • Thanks to Jerry and 50-3 for answers but sorry not working! Have already tried many combinations of this, Works the first time ie A1-A2 or A2-A1, gives me the right answer in B1, but then when I enter a value in A3, Excel doesn't give me the difference between A3 and A1 (which is what I want) it adds A2 and A3 together and subtracts that from A1, which is not what I want. So i want B1 to always calculate and return the difference between the first and last value as the values are entered. i.e. a continuing difference subtraction calculation. Hope not too confusing. – Jan Sep 15 '13 at 10:41
  • @Jerry: Of course, my typo :) Thanks! – Michał Sacharewicz Sep 15 '13 at 10:47
  • 1
    Thank you very much to everyone - especially to Jerry, just tried it, works! Like a dream! Would never have found that in a million years....now I can keep the values, as need those for a graph, and use the single value in the other cell to calculate average decline between first and last over a number of years. Eureka! Thanks again, ciao. – Jan Sep 15 '13 at 11:58
0

Just minus the sum of the other fields from a1

=a1-(a2+a3+a4+a5)

or:

=a1-sum(a2:a5)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.