Help with =IF(SUM(G64:G69)<0,SUM(B64:B69),"")

  • Thread starter Thread starter pieta4mi
  • Start date Start date
P

pieta4mi

In this function, =IF(SUM(G64:G69)<0,SUM(B64:B69),"") entering a 0 in
the range G64:G69 does not return "", but rather 0. If the range of
G64:G69 has no values, I need a return of a blank cell in range
B64:B69, not a 0. Help!

Scott
 
If the sum of G64:G69 is 0, then your formula will not trap it, as you are
looking for a value less than 0. Also, it depends what the values in B64:B69
are! The way you put your question though, it would seem that you want the
sum of G64:G69, if it is not 0, in B64 etc, else you want nothing. If that
is the case, then in B64 enter

=IF(SUM(G64:G69)<>0,SUM(G64:G69),""), or if you want the same answer in
B65:B69 as well, either enter =B64 in those cells, or use absolute references
in the formula, and copy to the other cells as well.
 
this might be a "noise" level issue
try
=IF(abs(SUM(G64:G69))<0.001,SUM(B64:B69),"")

round() or multiple others could be used.
 
I can't seem to duplicate the problem. If I have no values in
G64:G69, or if there are values that total zero, I get the blank cell.

As bj writes, it's true that you may see displayed (or at least
expect) a sum of zero from a range that actually sums to a very, very
small number, and that small number can unexpectedly trigger the
formula. But you seem to indicate that placing a single zero in the
range G64:G69 and leaving the other cells blank gives you 0. Is this
true?

Mark Lincoln
 
If the sum of G64:G69 is 0, then your formula will not trap it, as you are
looking for a value less than 0. Also, it depends what the values in B64:B69
are! The way you put your question though, it would seem that you want the
sum of G64:G69, if it is not 0, in B64 etc, else you want nothing. If that
is the case, then in B64 enter

=IF(SUM(G64:G69)<>0,SUM(G64:G69),""), or if you want the same answer in
B65:B69 as well, either enter =B64 in those cells, or use absolute references
in the formula, and copy to the other cells as well.

--
Hth

Kassie Kasselman
Change xxx to hotmail






- Show quoted text -
Click to expand...

Kassie,

The values in the range of G64:G69 could be 0 or more. But if there
is no value such as 0,78, 34, etc in the range, only empty cells, I
want the function to return an empty cell for the sum of the range
B64:B69.

Hope you can help.

Scott
 
this might be a "noise" level issue
try
=IF(abs(SUM(G64:G69))<0.001,SUM(B64:B69),"")

round() or multiple others could be used.






- Show quoted text -
Click to expand...

Have tried something similar and does work.....just hoping to be more
exact.

Scott
 
I can't seem to duplicate the problem. If I have no values in
G64:G69, or if there are values that total zero, I get the blank cell.

As bj writes, it's true that you may see displayed (or at least
expect) a sum of zero from a range that actually sums to a very, very
small number, and that small number can unexpectedly trigger the
formula. But you seem to indicate that placing a single zero in the
range G64:G69 and leaving the other cells blank gives you 0. Is this
true?

Mark Lincoln




- Show quoted text -
Click to expand...

Yes, Mark, that is the case. In the G range of cells, a 0 or any
other number in one or more cells should have the formula adding up
the group of numbers in the B range of cells. If there are no
numbers in the G range, the formula should not sum the B range numbers
and only return a bank cell.

Scott
 
Back
Top