Forum Discussion
Using IF(ISBLANK getting #Value! error
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
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!
12 Replies
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!
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.
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.
- It worked, thank you. I need to learn more about Excel.
- What are the values in C4 and E3? Are they numeric or text?
KevinNumericKevin Jones
