# #value error

#### Larry Fish

##### New Member
I get an #value. I know the reason for this but I would rather leave cells blank than put a Zero in them. Is there a way around this? This is the formula that causes the error.

=if(b4<5,b4,5)

Thanks for the help!!

Larry

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

##### MrExcel MVP
Larry Fish said:
I get an #value. I know the reason for this but I would rather leave cells blank than put a Zero in them. Is there a way around this? This is the formula that causes the error.

=if(b4<5,b4,5)

Thanks for the help!!

Larry

What is in B4, a #VALUE! error. If so:

=IF(ISNUMBER((B4 < 5)+0), B4, 5)

#### Larry Fish

##### New Member
B4 will have a number placed in the cell in the future.

Thanks I think this will help.

#### Oaktree

##### MrExcel MVP

I think there's an error in this formula
Code:
`` =IF(ISNUMBER((B4 < 5)+0), B4, 5)``

If B4 = 6, this returns 6. But, I believe the OP's intention was to return 5.

Perhaps this would suit:

Code:
`` =IF(ISNUMBER(B4),IF(B4<5,B4,5),5)``

##### MrExcel MVP
Oaktree said:

I think there's an error in this formula
Code:
`` =IF(ISNUMBER((B4 < 5)+0), B4, 5)``

If B4 = 6, this returns 6. But, I believe the OP's intention was to return 5.

Perhaps this would suit:

Code:
`` =IF(ISNUMBER(B4),IF(B4<5,B4,5),5)``

Right. Continuing in the same vein of the original:

=IF(SUM(ISNUMBER(B4 < 5)+0), B4, 5)

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,546
Messages
5,838,021
Members
430,526
Latest member
NiceGuyWithExcel2007

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