#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
 

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
8,053
Office Version
  1. 365
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,210
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)
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top