Computing Averages in MS Excel

From: David Cressey <david.cressey_at_earthlink.net>
Date: Thu, 08 Dec 2005 13:42:28 GMT
Message-ID: <8LWlf.1110$3Z.973_at_newsread1.news.atl.earthlink.net>



A little while ago, dawn mentioned in the monster NULL thread that she was busy trying to
reduce NULLS from SQL into an SQL-wrapper.

After she said that she couldn't simply filter out the missing data, the subject dropped.
I'll note in passing that 2vl do, eventually, run into the necessity for dealing with missing data. I suspect that Pick programmers do, too.

Anyway, that led me to reframe the question, as follows:

How to present missing data to users in MS Excel, and do it in such a way that it won't skew averages?

So I cranked up a blank worksheet, and I started out by defining a rectangular area on it.
I set up four cells that would take functions on that area: AVERAGE, COUNT, SUM, and SUM divided by COUNT.

I then proceeded to enter various things in the cells, to see what I would get. I was pleased with the way COUNT works:

COUNT includes cells that contain numbers, and also cells that contain formulas that evaluate to numbers.
COUNT excludes empty cells, labels (cells that contain text), and cells that contain formulas that evaluate to text strings.

I didn't mess around with dates.

AVERAGE and SUM use the same criterion that COUNT does. And SUM divided by COUNT always gave the same answer as AVERAGE.

So here's my answer (before looking at any documentation): In MS Excel, I would use an empty cell to represent the SQL NULL. If my users asked what an empty cell means, I'd say, "it means there isn't anything in it." I would expect 99% of users to go on to the next issue without missing a beat.

For the oddball user that's as interested in "nothing" as the argument clinic patients in c.d.t., I would get down and dirty, and explain the difference between an Excel cell that's empty, and one that contains an empty character string. But I would make a mental note to find out why that person is so interested.

It usually means the person would rather work with my inputs than with my outputs. I understand that point of view, having been on the other side of the fence. But that's a whole other discussion. Received on Thu Dec 08 2005 - 14:42:28 CET

Original text of this message