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...
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!
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.It really depends on the actual cells and formulas, but you might use something like
=IF(COUNTIF(range, "")=COUNTA(range), "", SUM(range)
- 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!
- 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.
No, 0 and 0.0 and 0.000 are exactly the same to Excel.
- I was pretty sure that was the case, but thought I'd ask anyway. Thanks for all of your help.
- Yes, in my case that would be really messy! But it is possible. Thanks for the suggestion. I'll have to think on it.