Forum Discussion
Excel 2007 average discrepency
JoeUser2004 Thanks for the reply. Even though it's been 50 years since my last algebra class your answer was very clear & understandable. I Googled "average of averages" & found some fascinating websites that dealt with this subject & learned about Simpson's Paradox. I was pretty sure it wasn't a problem with Excel but because of another problem with blank cells I wasn't positive. When I first d/l the data in a spreadsheet & sort from high to low by any column that has blank cells it always puts the blank cells in the top so I have to block mark the empty cells & hit delete. Then when I re-sort the high row will be at the top & the blank cells at the bottom as should be. Try as I might I can't find a way to figure out what character is in the cells so I can remove them from the entire sheet all at once.
Thank you once again
Re: ``When I first d/l the data in a spreadsheet & sort from high to low by any column that has blank cells it always puts the blank cells in the top [....] can't find a way to figure out what character is in the cells so I can remove them``
I suspect the "blank" (i.e. blank-appearing) cells contain null strings or non-breaking spaces (ASCII 160; HTML nbsp) that you might have copy-and-pasted-value.
Confirm that formulas of the form =ISTEXT(A1) return TRUE.
If those are the only text in the range, you can delete them with the following procedure.
1. Select the range.
2. Press function key f5 (Go To), and click Special.
3. Select Constants, and deselect Numbers, Logicals and Errors. That is, select only Text.
4. Click OK.
5. Confirm that only the blank-appearing cells are selected. If so, press Delete.
Caveat: This runs the risk of deleting legitimate text inadvertently. It would be prudent to make a backup copy of the file first, so that you have something to fall back to if and when you discover any mistaken deletions when it is too late to undo (press ctrl+z).