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

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.


--
malakingaso
------------------------------------------------------------------------
malakingaso's Profile: http://www.excelforum.com/member.php...o&userid=31273
View this thread: http://www.excelforum.com/showthread...hreadid=509431

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

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.


--
mc32
------------------------------------------------------------------------
mc32's Profile: http://www.excelforum.com/member.php...o&userid=31272
View this thread: http://www.excelforum.com/showthread...hreadid=509431

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

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

Thanks.


--
malakingaso
------------------------------------------------------------------------
malakingaso's Profile: http://www.excelforum.com/member.php...o&userid=31273
View this thread: http://www.excelforum.com/showthread...hreadid=509431

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

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

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

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

Does anyone have any other suggestions?


--
malakingaso
------------------------------------------------------------------------
malakingaso's Profile: http://www.excelforum.com/member.php...o&userid=31273
View this thread: http://www.excelforum.com/showthread...hreadid=509431

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

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)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=509431

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

That worked! Thanks for the help.


--
malakingaso
------------------------------------------------------------------------
malakingaso's Profile: http://www.excelforum.com/member.php...o&userid=31273
View this thread: http://www.excelforum.com/showthread...hreadid=509431