Greater than less than arguement returning value when text inputte

G

Guest

I have the following formula "=IF(M14>$G14,+$G14-$E14,0)".
Yet when I put "actual" or some text in m14, I receive the TRUE arguement
(+$G14-$E14) and gives me a value...I was hoping for #VALUE instead. Any
ideas when the arugement is recognizes the text as a numeric value.

If I insert the word "actual" in for M14 in the formula it will err, ie
"=IF(actual>$G14,+$G14-$E14,0) .. any ideas?
 
P

Peo Sjoblom

Text is always greater than numericals according to Excel, you can fix that
by using this instead

=IF(AND(ISNUMBER(M14),M14>$G14),$G14-$E14,0)


note that there is no need of the plus sign in front of G14 in your formula


--


Regards,


Peo Sjoblom
 
J

JE McGimpsey

One way:

=IF(ISNUMBER(M14),IF(M14>$G14,$G14-$E14,0),"#VALUE!")

or, if you want an actual #VALUE! error:

=IF(ISNUMBER(M14),IF(M14>$G14,$G14-$E14,0),1+"A")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Returning a value rather than TRUE or FALSE. 3
Help with another formula, please 3
Formula That Counts First Value Only In Array As A Condition 2
Testing for text using VBA 1
Get value in last non-zero value cell in column 5
greater than.. 5
Greater than Less than formula 2
Greater than Less than issue 6

Top