SOLVED

Using IF(ISBLANK getting #Value! error

Occasional Contributor

Hi I need help with my formula. I entered =IF(ISBLANK(B4),"",B4-100) in cell C4 and it worked out just fine. Now I am trying to reference cell C4 using =IF(ISBLANK(C4),"",E3-C4) in cellE4 and I am getting an error #value!. I am not used to using these formulas so any help would be appreciated. 

 

Thank you

12 Replies
What are the values in C4 and E3? Are they numeric or text?

Kevin
best response confirmed by allyreckerman (Microsoft)
Solution

@brendababy02 Try this:

=IF(C4="","",E3-C4)

With =ISBLANK(B4) an empty cell returns TRUE in C4, otherwise FALSE. In your case the formula results in "", which is not empty, when B4 contains nothing (i.e. is blank). Thus, the formula always executes the FALSE argument, E3 - "" (a number minus a text). Hence, #VALUE!

It worked, thank you. I need to learn more about Excel.

@Riny_van_Eekelen Hi! I have a similar situation. I'm trying to populate a cell based on whether or not another cell has a returned value in it.

 

Capture.PNG

Looking at Line 5: If there is a value in D5 (which is brought in using an ISBLANK formula), then H5 needs to return D5*F5. However, if there is no value in D5, then H5 needs to fill in the value from a different cell on a different tab. The per price and flat fee values are being pulled from cells that contain VLOOKUP formulas, but I don't think that should be a problem(?).

 

My current formula based on your response in this discussion is: =IF(D5="",Worksheet!T1,D5*F5), but I'm not getting any result, just a blank cell, regardless of whether or not there is a value in D5.

 

Thoughts? Thanks!

@juliejo Would be helpful if you could show all the formulae used. Especially the one that populates D5.

What's in T1 on Worksheet1? What do you get when you only enter =D5*F5 in case D5 empty and in case it is not? Getting no result at all is odd.

Uploading your workbook (leaving out any confidential information) would even be better.

@Riny_van_Eekelen Worksheet is attached. It's kind of a messy work in progress.

 

Basically, I'm building a estimating worksheet for services offered. Some services are offered on a per word (it's a copyediting business) basis, and some are offered for a flat fee. On my form tab, I need the per price and word count to fill in if the service is per word and the total cost to auto-calculate. But if there's a flat fee, I want Per Price and Word Count to remain blank, and I want the flat fee to appear instead of a per-price total. Hope that makes sense.

 

I started out trying to use ISBLANK formulas (which seems to work in my Per Price column, but maybe that's just an illusion).

 

Thanks for taking a look!

@juliejo Tried to open your file. Noticed you have ActiveX controls. On a Mac right now, so that doesn't work for me. Perhaps someone else. Sorry!

@juliejo 

I set "show zero" option back, with that the reason of the issue is more clear

image.png

For R1 formula returns zero, not empty string

image.png

As a comment, even if it returns empty string, ISBLANK() to test it doesn't work. Cell is blank only if nothing returned to it. Any formula returns some value, zero or empty string or some number in your case.

In next sheet reference returns zero which is hided by your formatting option, 

image.png

Again, ISBLANK(Worksheet!R1) is always FALSE as far as you have any formula in it. Thus formula in D5 is equivalent of =Worksheet!R1. If in H5 in condition you compare with zero, not with empty string, you have desired result.

Oh, duh! The whole point of this exercise was to fix the ISBLANK issue, and then I walked right back into it. Thanks for setting me straight!!

@juliejo , you are welcome.

As a comment, I'd do not recommend hiding zero option in settings. Instead you may apply custom number format only to the cells where you don't like to show zeroes (after the testing) like

General;General;

where instead of first/second General could be desired number or currency format for positive and negative values accordingly.