Blank cells appearing as zeros

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:17 PM GMT



I am working on Microsoft2003. The blank spaces are appearing as zeros. I do not want this to occur since I will be computing formulas later on. I have been using the following code to identify what is blank and what is not blank. Everything that is blank is appearing as N/A.

=IF(ISBLANK(SubjectRawData!Z9),"N/A",VLOOKUP(SubjectRawData!Z9, Rawtable!$A$2:$B$7,2))

This has been working for half the column of cells. It does not seem to work for the other half and I cannot figure out why this is. Once I get to a certain cell in the column, it will alter my orignal value and replace it with another random value. This is the code for the cell that does not seem to be working.



ANSWER: try using "" or na() rather than "N/A", and let me know how it works

---------- FOLLOW-UP ----------

QUESTION: No it does not work. Once I reach a certain cell in the column, it continues to alter the original value. I have tried this formula on other columns of data as well and it does the same thing. I'm thinking it must be something embedded in my database, but I am unsure of what it is.


to solve your problem I would need to see the file

now, remotely, I would look the formatting for cells SubjectRawData!Z153, SubjectRawData!Z9 and Rawtable!$A$1:$B$7 and specially that row when the formula stop working

You might have cell filled with spacebars, that appears to be blank but don't

I would try to change your formulas to: =VLOOKUP(SubjectRawData!Z153,Rawtable!$A$1:$B$7,2,0) instead of =VLOOKUP(SubjectRawData!Z153,Rawtable!$A$1:$B$7,2) and =IF(ISBLANK(SubjectRawData!Z9)=TRUE,na(),VLOOKUP(SubjectRawData!Z9, Rawtable!$A$2:$B$7,2,0)) instead of =IF(ISBLANK(SubjectRawData!Z9),"N/A",VLOOKUP(SubjectRawData!Z9, Rawtable!$A$2:$B$7,2))

Yet, I am not sure it will make a difference


©2024 eLuminary LLC. All rights reserved.