How to make a cell return 0 instead of #VALUE!

M

malakingaso

All,

I need some quick help. I have a simple forumla and when the number is
negative I need the cell to be 0; however, right now it shows up as
#VALUE!.

Here is the formula:

=IF(G12-40<0,0,G12-40)

Thanks in advance for you help.
 
M

mc32

Are you sure that the zeroes in your formula are truly zero, and not the
letter "O"? I tried out the same formula and it returns the proper
zero.
 
M

malakingaso

Thanks for the suggestion. I just realized that the only time i
returns #VALUE is when there is no value in column g.

Thanks
 
V

vezerid

This is wierd. This formula should return #VALUE! only if G12 contains
a non-numeric value. If there is NO value, a blank cell would count as
0. Is there a chance that you type a space character in the cell?

At any rate, try the equivalent formula

=MAX(G12-40, 0)

and see what happens.

HTH
Kostis Vezerides
 
M

malakingaso

I tired that new formula and when column G is blank it still returns
#VALUE!.

Does anyone have any other suggestions?
 
D

daddylonglegs

Does G12 contain a formula?

If that formula returns a "formula blank" i.e. "" you might get a
#VALUE! result, either change formula so that it returns zero or use

=MAX(SUM(G12,-40),0)
 
Top