#value error

Larry Fish

New Member
Joined
Jan 24, 2005
Messages
13
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
 

Some videos you may like

Excel Facts

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Jan 24, 2005
Messages
13
B4 will have a number placed in the cell in the future.

Thanks I think this will help.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Aladin:

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)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Oaktree said:
Aladin:

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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,254
Members
412,313
Latest member
pauloalex
Top