Forum Discussion
IFNA Blank Cell Output?
Good-morning!
This is my first time posting here. I have a question regarding replacing N/A with blank cells.
I am using the IFNA function to replace N/A values returned from VLOOKUP with blank cells, however when I do this, subsequent calculations using these cells result in errors (i realize the cell isn't truly blank). The only way I have found to fix it is to replace N/A with 0s but for my data it is really important to differentiate between no data (blank cell) and a 0 value. Is there any way to replace N/A with a true blank cell that will still work in further calculations? Any help would be greatly appreciated! Thanks
10 Replies
Unfortunately, formulas cannot return a truly blank value, and Microsoft has turned down a very popular request to add that as a new feature 😞
Whether an empty string "" as return value disrupts calculations depends on the calculation:
- A test ISBLANK(cell) will return FALSE for a cell with a formula returning "", but cell="" will return TRUE.
- A formula such as =A1+C1 will return #VALUE! if A1 or C1 contains a formula returning "", but =SUM(A1,C1) will ignore "" (and all other text values)
- The formula =COUNTA(...) will include cells with a formula returning "" in the count, but =COUNT(...) will only count cells with a numeric value (including dates).
- Thanks for the validation. Even though I couldn't figure it out, I thought there had to be a way to return a truly blank cell (definitely would be a great feature to add) so now I know.
Do you know of any way to replace a cell that is not truly blank with a truly blank cell?
Alternatively do you know a work-around for using these "blank cells" in division (as you outlined for addition in your reply above)?
The formula i am using is fairly simple (if long) but each cell (H4, K4, etc) is a cell that may have been N/A.
=SUM(H4/$H$3,K4/$K$3,L4/$L$3,M4/$M$3,N4/$N$3,O4/$O$3,P4/$P$3,Q4/$Q$3,R4/$R$3,S4/$S$3,T4/$T$3,U4/$U$3,V4/$V$3,W4/$W$3,X4/$X$3,Y4/$Y$3,Z4/$Z$3,AA4/$AA$3,AB4/$AB$3,AC4/$AC$3,AD4/$AD$3,AE4/$AE$3,AK4/$AK$3,AT4/$AT$3,AU4/$AU$3,AX4/$AX$3,AY4/$AY$3,AZ4/$AZ$3,BA4/$BA$3,BD4/$BD$3,BH4/$BH$3,BI4/$BI$3,BJ4)*(10000/G4)
Thanks so much!I hope that someone else will come up with a better suggestion. The workarounds I can think of now are:
1) Use =SUM(IF(H4="",0,H4/$H$3),IF(K4="",0,K4/$K$3, ...)*10000/G4
2) Create a custom VBA function that performs the checks.
