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 ce...
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.
- Just wanted to say I was able to adapt the formula with all of the IF statements (as you suggested). It doesn't look pretty but it works!
On a related note, is it possible in any way to sum cells with blank values and return a blank cell, as opposed to 0? In my main spreadsheet some of the inputs are the sum of nested cells. In some cases the cells are blank, while other times they are true 0 values. Is there a way to differentiate between the 2 cases? Thanks.
