ISBLANK Formula Returns #VALUE if field is blank

barnesm

New Member
Joined
Mar 14, 2019
Messages
3
I am still learning ISBLANK, IFERROR, etc.

On the below how do I change formula that will return a blank result if Cell B5 is blank?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Generally, I think it is better to write:

=IF(B5="", "", B5*$C$1 + B5)

Notice that the formula above (suppose it is in C5) might return the null string (""). So the cell might appears blank. But =ISBLANK(C5) would return FALSE (!).

The reason is: ISBLANK is a misnomer. Think of it as ISEMPTY. A cell is "empty" when it has no value -- no constant and no formula.

C5 is not "empty"; it contains a formula. But it appears blank because its value is the null string.

In contrast, =C5="" returns TRUE if C5 is "empty" or (key point) its value is the null string. So =C5="" is TRUE if the cell appears blank.

And that is usually what we want when ask if a cell is "blank".

Arguably, there are times when we truly want to know if a cell is "empty". Of course, use ISBLANK for that purpose, and only for that purpose.

Also, there are other cell values that apppear blank -- a string of spaces and/or nonbreaking spaces, for example.

Instead of trying to handle all values of cells that appear blank, it would be prudent to be consistent and always use the null string when you want a cell value that should appear blank.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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
Back
Top