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

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

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

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

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

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.


--
fodman
------------------------------------------------------------------------
fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941
View this thread: http://www.excelforum.com/showthread...hreadid=552422

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


--
drvortex
------------------------------------------------------------------------
drvortex's Profile: http://www.excelforum.com/member.php...o&userid=15896
View this thread: http://www.excelforum.com/showthread...hreadid=552422