If is not number

[zJenkins]

Hi,

I have a formula:

=IF(E4>20000, F4*H4*6.15, IF(E4<20000, F4*H4*4.78,0))

However, sometimes E4 is not a number. On those I'd like the formula to return a 0. How is this done?

Thanks

 

[gaz_chops]

I would say it will as it is!

Edit: No it won't!

Try

=IF(ISNUMBER(E4),IF(E4>20000,F4*H4*6.15,IF(E4<20000,F4*H4*4.78,0)),0)

 

[Jonmo1]

Welcome to the board.

Try

=IF(ISNUMBER(E4),IF(E4>20000, F4*H4*6.15, IF(E4<20000, F4*H4*4.78,0)),0)
or
=IF(E4>20000, F4*H4*6.15, IF(E4<20000, F4*H4*4.78,0))*ISNUMBER(E4)

 

[Joe4]

However, sometimes E4 is not a number. On those I'd like the formula to return a 0. How is this done?

It does exactly that for me. I entered "abc" in cell E4, and the formula returned 0.
Can you provide an example of that is in cells E4, F4, and H4 when it does not return a 0, like you want?

 

[bbott]

Try:

=IF(ISNUMBER(E4),F4*H4*IF(E4>20000,6.15,4.78),0)

 

[gaz_chops]

It does exactly that for me. I entered "abc" in cell E4, and the formula returned 0.
Can you provide an example of that is in cells E4, F4, and H4 when it does not return a 0, like you want?

I did the same, then entered a value in F4 & H4 and it returns a value even with text in E4, guess it sees text as less than 20000?

 

[Jonmo1]

I would say it will as it is!

Nope.
< and > operators consider text strings and logicals to be greater than numbers

If E4 is BLANK, then E4 is considered 0, therefor E4<20000 is TRUE = F4*H4*4.78
If E4 is TEXT (including formula blanks ""), then E4>20000 is TRUE = F4*H4*6.15
If E4 is a logical TRUE/FALSE then E4>20000 is TRUE = F4*H4*6.15

 

[gaz_chops]

Nope.
< and > operators consider text strings and logicals to be greater than numbers

If E4 is BLANK, then E4 is considered 0, therefor E4<20000 is TRUE = F4*H4*4.78
If E4 is TEXT (including formula blanks ""), then E4>20000 is TRUE = F4*H4*6.15
If E4 is a logical TRUE/FALSE then E4>20000 is TRUE = F4*H4*6.15

Thanks Jonmo1, realised and edited my reply.

 

[Joe4]

It does exactly that for me. I entered "abc" in cell E4, and the formula returned 0.
Can you provide an example of that is in cells E4, F4, and H4 when it does not return a 0, like you want?

Nope.
< and > operators consider text strings and logicals to be greater than numbers

If E4 is BLANK, then E4 is considered 0, therefor E4<20000 is TRUE = F4*H4*4.78
If E4 is TEXT (including formula blanks ""), then E4>20000 is TRUE = F4*H4*6.15
If E4 is a logical TRUE/FALSE then E4>20000 is TRUE = F4*H4*6.15

Ah yes, the issue was I left F4 and H4 blank, which is why it was returning 0.

 

[Jonmo1]

You also need to specify what happens if E4 is EXACTLY 20000
Should that be included in the E4>20000 or E4<20000
Or should exactly 20000 not be considered at all and return 0 ?

With that under consideration, you can simplify formula to
=IF(ISNUMBER(E4),IF(E4>20000,6.15,4.78),0)*F4*H4

This will put exactly 20000 in with the 4.78 group.
If you want exactly 20000 to be in the 6.15 group, change > to >=