IFNA Blank Cell Output?

[lhunter700]

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

[#2]

@lhunter700 

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).

[#3]

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!

[#4]

@lhunter700 

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.

[#5]

Yes, in my case that would be really messy! But it is possible. Thanks for the suggestion. I'll have to think on it.

[#6]

Do you know if I were to replace N/A with 0.000, could i then differentiate those cells from cells with 0 with respect to conditional formatting and IF statements? Not sure if Excel considers place value when it comes to 0.

[#7]

@lhunter700 

No, 0 and 0.0 and 0.000 are exactly the same to Excel.

[#8]

I was pretty sure that was the case, but thought I'd ask anyway. Thanks for all of your help.

[#9]

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.

[#10]

@lhunter700 

It really depends on the actual cells and formulas, but you might use something like

=IF(COUNTIF(range, "")=COUNTA(range), "", SUM(range)

[#11]

Hi,
Just wanted to let you know I was able to use the info you provided to come up with a solution. Basically I created a formula that "counts the number of blank cells in the range of interest & when that count does not equal the total number of cells in that range, the range is summed, otherwise a blank value is returned". This is similar to what you said, only I manually entered the number of cells in the range. Like this:

=IF(COUNTIF(A2:C2,"")<>3,SUM(A2:C2),"")

Thanks again - you were a life saver!