Sorry Uploaded The Wrong File :) Here is the right one!! - DailyMoodTool.xls (1/1)
 

I am doing a workbook for my patients. I have attached it here for
anyone that cares to look at it. I have a series of symptoms down the
left hand side and a date range across the top. I have averaged
response by symptom by the days in the month the patient places a
numerical value in the cell, and averaged all symptoms responded to with
a numerical value by day. What is stumping me is how to average all of
the daily averages. What I want to end up with is a running average by
symptom, a daily average of all symptoms, and a monthly average of all
daily averages. What is the formula. Notice the ORANGE box. This is
where I want the last calculation to go. I have tried
=AVERAGE(B35:AF35), and =AVERAGE(AG5:AG34) with no luck. It does place
the function in the cell, but when I start inputing numerical values in
the cells, it auto averages symptom by month, and all symptoms by day,
but nothing appears in the cell for the monthly average except the ##
sign for the function! I understand that Excel auto rounds out based on
the width of the cell, so I opened up the cell wider to see if that is
the problem, but it is not. Does this require a different formula,
because I am averaging a series of averages. I thought I could just
choose a range of any cells and it would average it??? Can anyone help.

Sorry Uploaded The Wrong File :) Here is the right one!! - DailyMo
 

You forgot to paste the link.
Upload your file in http://www.freefilehosting.net and paste the link here.

one way, try the following formula
average by date for all symptoms
=AVERAGE(IF((datecolumn=date),datatoaverage))
average by date and by symptom
=AVERAGE(IF((datecolumn=date)*(symptomcolumn=sympt om),datatoaverage))

Adopt for your data the ranges
These are array functions and are to be entered by ctrl+shift+enter not just
enter.
best wishes
Sreedhar

" wrote:

I am doing a workbook for my patients. I have attached it here for
anyone that cares to look at it. I have a series of symptoms down the
left hand side and a date range across the top. I have averaged
response by symptom by the days in the month the patient places a
numerical value in the cell, and averaged all symptoms responded to with
a numerical value by day. What is stumping me is how to average all of
the daily averages. What I want to end up with is a running average by
symptom, a daily average of all symptoms, and a monthly average of all
daily averages. What is the formula. Notice the ORANGE box. This is
where I want the last calculation to go. I have tried
=AVERAGE(B35:AF35), and =AVERAGE(AG5:AG34) with no luck. It does place
the function in the cell, but when I start inputing numerical values in
the cells, it auto averages symptom by month, and all symptoms by day,
but nothing appears in the cell for the monthly average except the ##
sign for the function! I understand that Excel auto rounds out based on
the width of the cell, so I opened up the cell wider to see if that is
the problem, but it is not. Does this require a different formula,
because I am averaging a series of averages. I thought I could just
choose a range of any cells and it would average it??? Can anyone help