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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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

Replies
1
Views
182
Replies
17
Views
347
Replies
5
Views
89
Replies
4
Views
168
Replies
1
Views
124