Feb 05 2022 06:48 AM
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
Feb 05 2022 07:01 AM
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:
Feb 05 2022 07:25 AM
Feb 05 2022 07:43 AM
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.
Feb 05 2022 08:14 AM
Feb 05 2022 10:48 AM
Feb 05 2022 11:45 AM
No, 0 and 0.0 and 0.000 are exactly the same to Excel.
Feb 07 2022 06:10 AM
Feb 07 2022 11:09 AM
Feb 07 2022 11:47 AM
It really depends on the actual cells and formulas, but you might use something like
=IF(COUNTIF(range, "")=COUNTA(range), "", SUM(range)
Feb 08 2022 07:46 AM