Averaging Averages For Patient Care

**Michael Roback** · 04-25-2008, 01:16 AM

I have put together a tool for my patients that is basically a worksheet with the dates across the top, and symptoms down the side. Patients enter a numerical value to describe how they are doing and I have done an average in the far right column =AVERAGE(XX:XX) and it works fine. Each date ends in the last cell with an average of all numerical values put in for that date. Again =AVERAGE(XX:XX) and it works fine. Problem is that I want to average all responses for the month. So I take the last column of averaged individual symptoms for the month and want a general average of all of them again using the AVERAGE(XX:XX) function and though it is accepted in the cell, it does not work. I know that averaging averages can be tough. I did write a function that looked like =AVERAGE(A1,A2,A3.....) problem is that all of the cells have to be occupied to produce an overall average. Months have different number of days, and patients will start using the form at different points, at times less than a full month of data, so it will not work. Any ideas of what would work. If anyone cares to look at my spreadsheet, it can be viewed at my temp blog ...
http://robacktemp.blogspot.com It is the orange cell that I am trying to solve!!! Thanks for any help you can offer. Thanks.

**shg** · 04-25-2008, 01:21 AM

I don't see any workbook at the link.

You can zip and post it here.

**Michael Roback** · 04-25-2008, 10:54 AM

Okay, here is my workbook zipped to look at. I would really appreciate any help that can be offered. It is the orange box that currently has a conventional =AVERAGE(AG5:AG34) that is not working. Thanks.

**shg** · 04-25-2008, 02:38 PM

Your formulas look fine; the AVERAGE function ignores blanks and text. Have you plugged in some dummy data and looked at results?

**Michael Roback** · 04-25-2008, 05:07 PM

Yes, and I get the totals for cumulative average across the bottom fine as well as the totals by symptom across the month along the right had side fine. It is just the overall average of the average of all symptom responses across the month that does not compute, as well as that of the average of the average of all cumulative symptom averages by day. I have plugged in dummy data without any success! The only thing that I got to work was the =AVERAGE(XX,XX,XX .....) as I mentioned before but that only works if every cell is completed, and patients start the log at different times and may skip a day or two! If you care to, if you haven't, plug a few values in and you will see what I mean. The averages at the bottom work fine, as well as those to the right. But the average of the averages in the orange box has not worked for me, regardless of what I have tried. I am just not that experienced with this! I would appreciate any additional feedback. I am stumped. I want to get this tool up on my blog this weekend, as I think it will help my patients, but do not want to do so until the problem is solved

Thanks.

**shg** · 04-25-2008, 05:26 PM

So the row and column averages have what you want, correct?

For the entire average, could could do it one of three ways: take the average of the row averages, the average of the column averages, or the average of the interior.

The average of the (row) / (column) averages intrinsically gives equal weight to each (row) / (column) without regard to the number of responses (to that question) / (on that date), which is probably (guessing, here) not what you want.

The average of the interior is an average of all the responses, with each response equally weighted. I'd guess that's what you want.

A statistician on a winter camp awoke with feet blistered from being too close to the fire, and hands frostbitten from being too far away. On average, though, she was comfortable.