Sum based on differences 1

[monkeylizard]

I need to total the differences between two columns into Over/Under totals. I need to know how far over Column A is than B, when value in column A is > B, and vice versa. I'm not talking about the net difference, but more of an IF logic.

Example:

ColumnA ColumnB
10 5
10 8
4 6

In Row 1, the difference is 5
In Row 2, the difference is 8
In Row 3, the difference is 2 (or -2, depending on perspective)

Because the first 2 have column A values larger than B, I want to put them together in one cell and show 13. I need another total for differences where B>A, so in this case a cell showing 2.

I think I can start with a column C with a formula "=Ax-Bx"
that would give me

Column C
5
8
-2

Then how can I sum the positives and sum the negatives )and multiply by -1 to reverse the sign) separately?


Or is there a simpler way to do this?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

erm...I noticed the obvious mathematical error.I know 10-8 is not 8. oops. But the questions still remain. Thanks.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[SkipVought]

Hi,

Have you tried a formula? 'Sometimes just a few hours of trial and error' is good for the soul.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

How about, in cell c4...
=SUMIF(C1:C3,">0")


Randy

 

[SkipVought]

using named ranges...
[tt]
ColumnA ColumnB
10 5
10 8
4 6

=SUMPRODUCT((ColumnA-ColumnB)*((ColumnA-ColumnB)>0))
=SUMPRODUCT((ColumnA-ColumnB)*((ColumnA-ColumnB)<0))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Two more ideas:

Then how can I sum the positives and sum the negatives )and multiply by -1 to reverse the sign) separately?

In column C use the formula =Abs(A1-B1) this ignores the sign of the differences. Not sure if that is what you want.

OR

Use column C for the overs and column D for the unders. Formulae =if(A1>B1,A1-B1,0)

Gavin

 

[sxschech]

I found this to sum absolute values:

From:

http://excel.tips.net/Pages/T002913_Summing_Absolute_Values.html

=SUM(ABS(A1:A10))

Don't press Enter; instead press Ctrl+Shift+Enter, which signifies this is an array formula. If the formula is entered correctly, you'll see braces around the formula in the Formula bar:

 

[GlennUK]

Then how can I sum the positives and sum the negatives )and multiply by -1 to reverse the sign) separately?

do the difference calcs and summing with ABS at the same time ( assuming that you want this done in one step ):

=SUM(ABS(A1:A3-B1:B3))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

P.S. my calculation is an array formula, so press Ctrl+Shift+Enter instead of Enter ( this will put curly brackets around the formula when entered ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[monkeylizard]

I had used Gavona's second idea, putting the overs in C and the unders in D. That actually works just fine.

SkipVought's SUMPRODUCT formulas give me the same result and much more concisely without the need for columns C and D.

Thank you everyone for your help.





Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.