Blank cells that do not appear blank cells when applying COUNTA with ROWS

[mramo]

Hi,

Following a data extract from master data system (file downloads as *.xlsx), I inserted a new row above each of my headers and performed a, "=COUNTA(A3:A816)/ROWS(A3:A816)" formula and formatted that cell as a Percentage. The idea is that the formula return me a % of the field that are populated in each column.

When I apply the formula, I receive a return value "100.00%" which is untrue. It seems the formula is treating the blank fields as data entered. When I mark over a blank cell and then press DEL button, the formula value finally drops to below 100%!

Why is this happening and how can I resolve this to ensure my blank cells are treated as blanks?

Screenshot as a simple example.

 

[Joe4]

Identify one of those "blank" cells. Let's say it is row A14.
Enter this formula somewhere in another column, and see what it returns:

=LEN(A14)

If it returns anything other than 0, then those cells which you think are blank are really not blank.
If they just contain a blank space, you can fix your data by doing a "Find/Replace", replacing all instances of a single space with nothing.

If it is not a blank space, then we need to figure out when is in there.
This code would tell us:

=ASC(A14)

Just let us know what value that formula returns.

 

[mramo]

Hey Joe4, Pls see screenshot.

Performed these formulas against the blank cell under the header, Probationary Review.

Len is 0. When I click into that blank cell, no spacing exists within that cell.
When i try the ASC formula it returns a blank

 

[DanteAmor]

Maybe you copied the data from the network, sometimes "blank" cells contain control characters.
You can remove them with a macro.

check this:

VBA to Remove ASCII Character 32

I have two sheets ("Old" and "New") with various range of data and would like them to be formatted the same so that it removes ASCII character 32 and replaces them with a null text string "". Can someone help with creating this macro. Thanks.

www.mrexcel.com

 

[Joe4]

Len is 0. When I click into that blank cell, no spacing exists within that cell.
When i try the ASC formula it returns a blank

If that is indeed true, then COUNTA should work, as it should not count any cell that is completely empty.

Are you sure that you are applying your formula to the correct ranges?
In your original question, your formula references column A, but your image in your first post shows column O.
Then in your last post, it shows column AR.

 

[mramo]

So what I ended up doing is, with no solution in place, was to simply perform a FIND/REPLACE to fill all the blanks with a unique set of characters, then perform another FIND/REPLACE and replace them back with BLANKS. That was the only way to resolve the issue but I will need to do this each time I wish to use the COUNTA/ROWS formula - until we go live with SuccessFactors next year - which I hope will not replicate the issue I am already having.

Thanks for all your help so far.

 

[Joe4]

So what I ended up doing is, with no solution in place, was to simply perform a FIND/REPLACE to fill all the blanks with a unique set of characters, then perform another FIND/REPLACE and replace them back with BLANKS. That was the only way to resolve the issue but I will need to do this each time I wish to use the COUNTA/ROWS formula - until we go live with SuccessFactors next year - which I hope will not replicate the issue I am already having.

Thanks for all your help so far.

It sounds like there is some weird stuff coming over on this file - not totally uncommon with Excel files being generated by other programs or coming from the web.

If you can upload a copy of the file to a file sharing site and provide a link to it in this thread, we can analyze your file and see if there is something we can do to help you avoid having to do those manual steps (note: even if that was not possible, we could create a macro that you could run to "clean-up" the data and put the formulas in there for you).

 

[mramo]

Just messaged you privately.

It sounds like there is some weird stuff coming over on this file - not totally uncommon with Excel files being generated by other programs or coming from the web.

If you can upload a copy of the file to a file sharing site and provide a link to it in this thread, we can analyze your file and see if there is something we can do to help you avoid having to do those manual steps (note: even if that was not possible, we could create a macro that you could run to "clean-up" the data and put the formulas in there for you).

 

[Joe4]

I took at look at the file you sent, and I see what you are talking about.
COUNTA does not count blanks that are returned by formulas, or if they were formulas and copied over with Copy/Paste Special -> Values.
I am guessing something like this happened along the way.

Following what they did here: COUNTA() formula Not working as expected
try changing all instances of:

COUNTA(B3:B816)

like this:

=COUNTIF(B3:B816,"*?")