Totalling discrepancies

[fodman]

Hello all,

help much appreciated on the following problem:

Column A contains "Expected number of units"

Column B contains "Actual number of units"

Column C contains "Difference"



Expected Actual Diff
2 3 1
-1 5 6
8 1 -7



What i need is for the sum of Column C (diff) to give me 14, as there are 14 discrepancies shown.


Thanks in advance.

[Elkar]

Try this:

=SUM(ABS(C2:C4))

Enter this as an array formula. Press Ctrl-Shift-Enter instead of just
Enter. If done correctly, { } should appear around the formula in the
formula bar.

HTH,
Elkar


"fodman" wrote:

>
> Hello all,
>
> help much appreciated on the following problem:
>
> Column A contains "Expected number of units"
>
> Column B contains "Actual number of units"
>
> Column C contains "Difference"
>
>
>
> Expected Actual Diff
> 2 3 1
> -1 5 6
> 8 1 -7
>
>
>
> What i need is for the sum of Column C (diff) to give me 14, as there
> are 14 discrepancies shown.
>
>
> Thanks in advance.
>
>
> --
> fodman
> ------------------------------------------------------------------------
> fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941
> View this thread: http://www.excelforum.com/showthread...hreadid=552422
>
>

[fodman]

Excellent, works perfectly.

Could you please explain that Formula?

[Elkar]

The ABS fuction returns the Absolute Value of a number. Basically, it just
strips off any negative signs, treating all numbers as positve.

The use of an Array Formula, tells Excel to apply the function (in this
case, ABS) to each individual cell in the range, rather than to the range as
a whole.

HTH,
Elkar

"fodman" wrote:

>
> Excellent, works perfectly.
>
> Could you please explain that Formula?
>
>
> --
> fodman
> ------------------------------------------------------------------------
> fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941
> View this thread: http://www.excelforum.com/showthread...hreadid=552422
>
>

[Alan McQuaid via OfficeKB.com]

Hello,

I'm assuming that in Column C your formula is "=B2-A2" or something to that
effect. Instead, try "=ABS(B2-A2)" and this will give you the desired effect

Alan

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200606/1

[fodman]

Thanks Alan,

that will be put to good use elsewhere in the spreadsheet but for this particular column it is only the sum of discrepancies i needed to total. Each individual negative or positive would have to remain.

[drvortex]

Ok,

I used this thread to try and answer my question but I'm still have some issues. I will do my best to describe what I'm trying to do.

I have a range, lets say A1:A5. The numbers are positive and negative numbers....just the differences from two other cells.

However, I have another criteria set in the cells A1:A5 that if the criteria is not TRUE it will place an "X" in the cell. Now...for the fun part.

I have cells A1-A3 w/ values but A4 is an X and A5 has a value. Whenever I attempt to do the SUM(ABS(A1:A5)) it gives an error because of the X. How do I put in another criteria stating to only SUM the Absolute Value of cells that contain numbers and don't even add the cell w/ "X"???

This is what I tried but didn't work...still errors.

=SUMIF(A1:A5,"<>X",ABS(A1:A5))

I also tried to use the Shift+Ctrl+Enter thing to make it as an array. Any help would be great. Thanks all.

Jason