# IFNA Blank Cell Output?

Occasional Contributor

# 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

# Re: IFNA Blank Cell Output?

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

# Re: IFNA Blank Cell Output?

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.

Thanks so much!

# Re: IFNA Blank Cell Output?

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.

# Re: IFNA Blank Cell Output?

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

# Re: IFNA Blank Cell Output?

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.

# Re: IFNA Blank Cell Output?

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

# Re: IFNA Blank Cell Output?

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

# Re: IFNA Blank Cell Output?

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.

# Re: IFNA Blank Cell Output?

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

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

# Re: IFNA Blank Cell Output?

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!