Need Averaging Help With Workbook Please! - 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.

Need Averaging Help With Workbook Please! - DailyMoodTool.xls (1/1)
 

Hi
The formula in cell AG5 needs to be
=IF(COUNT$(B5:$AF5)=0,"",AVERAGE($B5:$AF5))
the formula in B35 needs to be
=IF(COUNT($B5:B$34)=0,"",AVERAGE(B$5:B$34))
--
Regards
Roger Govier

wrote in message
...
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.

Need Averaging Help With Workbook Please! - DailyMoodTool.xls (1/1)
 

In article ,
"Roger Govier" <roger@technology4unospamdotcodotuk wrote:

Hi
The formula in cell AG5 needs to be
=IF(COUNT$(B5:$AF5)=0,"",AVERAGE($B5:$AF5))
the formula in B35 needs to be
=IF(COUNT($B5:B$34)=0,"",AVERAGE(B$5:B$34))
--
Regards
Roger Govier

wrote in message
...
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.



It did not work? I got an error message? Why can't I just do:

=AVERAGE(AG5:AG34)? Would that not average the columns. It does
display the function in the cell. It just does not compute when I
enter number into the cells? I am confused :)

Need Averaging Help With Workbook Please! - DailyMoodTool.xls (1/1)
 

Hi
My apologies. Those fingers were not typing what I was thinking<g
The formulae should have read
In AG5
=IF(COUNT($B5:$AF5)=0,"",AVERAGE($B5:$AF5))
In B34
=IF(COUNT(B$5:B$34)=0,"",AVERAGE(B$5:B$34))

The problem is that when there is no data, you get a #DIV/0 error, which is
what you will see if you widen any of your columns where the error is
showing.
The COUNT function is eliminating this error, by placing a null in the cell
if there are no values to average.

If you try to use
=AVERAGE(AG5:AG34)
when there are #DIV/0 errors in the range AG5:AG34, then that will also
return a #DIV/0 error, or again if there are no entries in the range you
will get an error.
Once you have error trapped the other entries, then you could enter in AG36
=IF(COUNT(AG5:AG34)=0,"",AVERAGE(AG5:AG34))

Be aware though that the value obtained from that calculation could be
different to the value obtained if you went the other way
=IF(COUNT(B35:AF35)=0,"",AVERAGE(B35:AF35))

The true overall average would be
=IF(COUNT(B5:AF34)=0,"",AVERAGE(B5:AF34))

--
Regards
Roger Govier

wrote in message
...
In article ,
"Roger Govier" <roger@technology4unospamdotcodotuk wrote:

Hi
The formula in cell AG5 needs to be
=IF(COUNT$(B5:$AF5)=0,"",AVERAGE($B5:$AF5))
the formula in B35 needs to be
=IF(COUNT($B5:B$34)=0,"",AVERAGE(B$5:B$34))
--
Regards
Roger Govier

wrote in message
...
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.



It did not work? I got an error message? Why can't I just do:

=AVERAGE(AG5:AG34)? Would that not average the columns. It does
display the function in the cell. It just does not compute when I
enter number into the cells? I am confused :)