Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Usually I would just calculate the difference of each row like this: =B1-A1

Then I would sum up the values of the column like this =SUM(C1:C10) and get my desired value.

I don't want the C-column. Is it possible to calculate it immediately?

For example I tried: =SUM((B1-A1):(B10-A10)) (which doesn't work)

Usually I can find the answers somewhere in the documentation, but my Math-English vocabulary is a little bit rusty and with German documentation I haven't found what I was looking for.

I know I could put 10 values manually inside the sum. But this doesn't scale gracefully, I want to be able to change only a few values in the formula instead of adding 1000.

share|improve this question
up vote 5 down vote accepted

The following should work for this case:

=SUM(B1:B10) - SUM(A1:A10)
share|improve this answer
Actually i tried it with date but ended up with a weird value. But when i think about it it makes sense (hit brick on my head). Changed it to hours and the value is correct now. Do you know, how to do it with date format? – proxylittle Mar 26 '13 at 15:40
How can I ensure this number is always positive? I want to calculate how far off from an estimate we were... either positive or negative. – ATSiem Sep 25 '13 at 20:42
@ATSiem: It's probably long past useful, but the ABS() function ought to help. – telmer6 May 14 '15 at 0:02
How can you get the differences of textual columns? Etc Column A: A, B, C. Column B: A, B, B. – Masi Jun 16 at 12:19

If you want to calculate each value in turn, evaluate it as a positive number, THEN work out what that adds to then your best bet is an array formula. These are entered not by pressing ENTER at the end, but CTRL+SHIFT+ENTER. When done, you will notice a pair of braces either side ( { } ). Note - you must use this method, you cannot type the braces and CTRL+SHIFT+ENTER etc in

=sum(abs(b1:b10 - a1:10))

Clearly, you can replace sum with average if that better suits your needs.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .