# If is not number

#### zJenkins

##### Board Regular
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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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)

Last edited:
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)

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?

Try:

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

Last edited:
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?

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

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.

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.

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 >=

Last edited:

Replies
8
Views
137
Replies
3
Views
171
Replies
3
Views
148
Replies
3
Views
120
Replies
0
Views
280

1,207,088
Messages
6,076,514
Members
446,210
Latest member
AHMET KARAASLAN

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back