Totalling discrepancies

  • Thread starter Thread starter fodman
  • Start date Start date
F

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.
 
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
 
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
 
Thanks Alan,

that will be put to good use elsewhere in the spreadsheet but for thi
particular column it is only the *sum *of discrepancies i needed t
total. Each individual negative or positive would have to remain
 
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
 
Back
Top