S
sbwestin
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How does Excel handle averaging numbers in a column when some of the cells are blank?
C
CyberTaz
Assuming you're using the =AVERAGE() function, it's designed to ignore blank
cell or those that contain text. IOW, if the range is 10 cells & 4 of them
are empty the function totals the 6 values & divides the sum by 6. If those
4 cells contain 0s it will divide by 10 because 0 is a value.
cell or those that contain text. IOW, if the range is 10 cells & 4 of them
are empty the function totals the 6 values & divides the sum by 6. If those
4 cells contain 0s it will divide by 10 because 0 is a value.
S
sbwestin
Thank you for your response.
I am using the =AVERAGE()function. My column has 34 rows, of which 4 cells are blank (they do not contain a "0"
. The average Excel calculated was 40.69, standard deviation of 4.24.
I then copied and pasted just the 30 rows of numbers to eliminate the blank cells - the new average was 41.49, standard deviation of 4.47.
This is obviously a problem when many more rows are involved! Any thoughts?
I am using the =AVERAGE()function. My column has 34 rows, of which 4 cells are blank (they do not contain a "0"
. The average Excel calculated was 40.69, standard deviation of 4.24. I then copied and pasted just the 30 rows of numbers to eliminate the blank cells - the new average was 41.49, standard deviation of 4.47.
This is obviously a problem when many more rows are involved! Any thoughts?
C
CyberTaz
Are you certain that the 'blank' cells are actually blank & don't contain
zero values? Select each of them & take a look at the formula bar. If Excel>
Preferences> View has the check removed on "Show zero values" cells
containing zeroes *display* as blank but the zero values stored in them are
included in the computations.
Since your column containing the blanks averages lower than the column
without them I tend to suspect that to be the reason. Testing here yields
exactly the same result for both columns regardless of cell formatting.
HTH |:>)
Bob Jones
[MVP] Office:Mac
zero values? Select each of them & take a look at the formula bar. If Excel>
Preferences> View has the check removed on "Show zero values" cells
containing zeroes *display* as blank but the zero values stored in them are
included in the computations.
Since your column containing the blanks averages lower than the column
without them I tend to suspect that to be the reason. Testing here yields
exactly the same result for both columns regardless of cell formatting.
HTH |:>)
Bob Jones
[MVP] Office:Mac