sum function; 0 vs. 0.00 vs. (0.00)

  • Thread starter Thread starter N Hegler
  • Start date Start date
N

N Hegler

I am trying to sum balances in Excel and make a formula
flag a cell with the word "delete" if the sum is zero.
The only problem is that Excel does not recognize the sum
of 0.00 or (0.00) as 0 in my formula. I have discovered
that if I resort the information so that the balances are
ascending (instead of by date, which is the preferred
order) my formula will work. Does any one know a way
around the sorting to get the formula to work properly?
Below is a sample of my formula to flag the cell
with "delete":
=IF(C143= "", "", IF(AND(C143=C147,SUM(H143:H147)
=0), "DELETE", IF(AND(C143=C146,SUM(H143:H146)
=0), "DELETE", IF(AND(C143=C145,SUM(H143:H145)
=0), "DELETE", IF(AND(C143=C144,SUM(H143:H144)
=0), "DELETE", "")))))

It is testing to see if, at the most, five consecutive
rows are the same and if their sum of the balances for
these rows are zero.
Any advice or assistance is appreciated!
Thanks!
 
Your formula appears to work as expected. Perhaps your
data is formatted in in the h143:h146 to 2 decimal places
and is not actually zero, or the comparison of c143 to
c147 has the same type of issue.

Lance
 
I thought that sending a sample of my information would
help:

COL C COL H
LANIER, C (174,134.99)
LANIER, C 337.34
LANIER, C 173,983.65
LANIER, C (186.00)
LOVE, M (194,678.29)
LOVE, M 194,500.00
LOVE, M 178.29

The first set of information for Lanier is working
correctly. The one for Love is not. Column H has a
format of number with two decimal places and the () mean
negative numbers. When you sum up column H for Love, the
status bar shows sum = (0.00). This to me means that
there is a negative zero total and there is no such
thing. Zero is neither positive nor negative! Being an
ex-math teacher, I have a problem with that! I have also
tried copy/paste with the name and no luck! Any other
suggestions?
Nissa
 
Unfortunately, these are not calculated values. They are
keyed directly into the worksheet. Any other ideas?
 
The sum of love is -8.15703060652595E-12

If your data is always 2 decimal places or less your could
round(sum(h5:h5),2) which would return 0

Lance
 
Back
Top