On Feb 12, 7:43 am, Meenie wrote:
Now, we aren't looking for an exact number. Just a kind of baseline
score to show how each unit is doing on these different questions -
so is the averaging of the averages a big deal?

No, at least not based on what you describe. I question whether your
description is entirely accurate. But even if it is not, as you say,
you are not looking for an exactly number.

(I do quibble with your scoring method. I suspect that is more likely
to introduce misleading results than the average of averages. But
that's another subject altogether. And it is just a feeling right
now; nothing concrete.)

First, an average of averages is mathematically the same as the
average of all the individual numbers when each average has the same
denominator.

You say that each of your first set of averages is each based on 5
data. I assume you mean a sampling of patients, with 5 in each
sample. We must presume that there is never less than 5 patients in a
week in any unit.

Be that as it may, __if__ the first set of averages is each based on 5
data, the average of those averages is an accurate average of all the
data together, at least mathematically. (See the caveat below.)

Likewise, the average of the monthly averages would be an accurate
representation of all the data if you use the same number of weeks per
month for the monthly averages. Note: Some months might be
considered to have 5 weeks, depending on how you count a week.

Caveat: The average of averages is accurate in this manner
__mathematically__. A difference may arise due to the way Excel does
arithmetic (i.e. binary computer arithmetic); but the difference is
not likely to be significant. A more significant difference arise is
you do an average of __rounded__ averages; for example, if you compute
=ROUND(AVERAGE(...),2) for the individual averages. Rounding is not a
significant issue if you rely on Excel formatting to perform the
rounding.


is there a better way to do this without making it into
something that is way too much work for a simple check
on how they're following procedures?

At a minimum, if you keep track of the number of data used to compute
each average, you could use SUMPRODUCT to compute a more accurate
average. Again, this should be needed only if the number of data in
each average varies.

(But this will not work if you explicitly round the averages.)

Arguably, a better approach is to keep track of the sum of the data as
well as the number of data used to compute each average. For example,
if you now compute AVERAGE(A1:A5), also compute SUM(A1:A5).

Each of those improvements do not sound like major changes to me. But
it depends on the layout of your worksheet. Also note that you can
hide the columns or row that contain these additional computations so
they do not cause confusion, if that's a concern.

If you need help with implementing these ideas, post back with some
details of the worksheet. Alternatively, you could send a file to me
at joeu2004 "at" hotmail.com.

But I want to reiterate that you probably do not need to implement
either improvement.


----- original posting -----

On Feb 12, 7:43 am, Meenie wrote:
I have Excel 2003 - but doesn't make much difference for this question.

I know you aren't supposed to do an average of averages but we do.
Here's what we do and why we do it... is there a better way?

We have a number of units that do a monthly audit of things in patients
chart.
there are 26 questions on this audit and they have to do it on 20 charts
each month.
They turn in 5 audits a week.
the questions are all answered with yes, no or n/a. examples of questions
a 1. Is the admission form completed. 2. Was the database signed by an RN.
3. If the patient's pain greater than 4, was the intervention documented.

Ok, so a score is assigned on each audit like this:
each audit is on 5 charts so there are 5 answers in each row.
If the question is answered "Yes" for each chart that would equal 100%
Each no = minus 20 so if there were 2 no's the score would be 60%
(seems like tough scoring, but all the answers should always be either yes
or n/a)
N/a does not count off anything.

then the scores for all 26 questions are averaged for the unit's average
weekly score.

These audits are done each week and at the end of the month, the average
scores for each week per unit are averaged for a score for the month. (that's
the 2nd time the numbers are averaged)

Each month the monthly averages are averaged for a YTD score for each unit.
(average #3)

Now, we aren't looking for an exact number. Just a kind of baseline score to
show how each unit is doing on these different questions - so is the
averaging of the averages a big deal?

We don't actually call them "averages" we call them "scores" and make a bar
chart as a "dashboard" of how things are going on each unit.
And in the long run, all the units come out well, but is there a better way
to do this without making it into something that is way too much work for a
simple check on how they're following procedures?

Thanks to anyone who has hung in this far :)