Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 3 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

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 in

e.g. =sum(abs(b1:b10 - a1:10)) CTRL+SHIFT+ENTER

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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