Averaging Averages For Patient Care

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.

I don't see any workbook at the link.

You can zip and post it here.

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.

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

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.

Ideally it would be great to have an average of all responses, relative to the number of responses that were entered. However, even if I had an average of either row, that would generally suffice, as this does not really need to be an exact measure. it is just a suggestion of a trend! But trying to average each row has not worked, so maybe averaging the interior may be the most accurate and the most doable, but I have no clue as to how to do it. What formula would I put in the orange box?!!! Can you see what I mean? Did you notice that both row and column averages work fine, but the overall average of averages in either direction do not! What am I missing?

Originally Posted by oldchippy

May be in AG36

=AVERAGE(B5:AF34)

Okay, , this does produce a calculation in box AG36. If I understand it, this averages all of the responses on the worksheet, relative to the number of responses? Is there a statistical downside or skewed average by doing this rather than averaging either row or column? It seems like such a simple solution. I am shocked. Please explain Also, is there a way of turning off extra pages in the workbook, so that only one page appears when it is uploaded to a blog, and downloaded remotely? Thanks again.