I am uploading a copy of the workbook that I am working on so that it is
clear what I am talking about. I have set up a tool for my patients
where they can log a numerical rating by symptom, by day and see an
average of how they are responding to each symptom at the end of the
month and with symptoms overall each day. I want to provide an average
of all symptoms at the end of the month. You will notice on the
workbook that the dates are across the top and the symptoms are down the
side. The formula for the daily average of all symptoms is
=AVERAGE(B5:B34) and it works fine. The formula to average each symptom
as numerical values are entered through the end of the month is
=AVERAGE(B5:AF5) and it works fine. Problem is that when I enter
=AVERAGE(AG5:AG34) it is accepted as a formula and shows up as ## in the
orange cell, however when I enter data in the workbook this cell does
not display a numerical value. I know it is a average of averages,
however, it wouldn't matter. I am merely telling it to average a column
of numerical values, aren't I. What am I missing. I am a newbie and
just wanting to make this work!! Can anyone help? What formula needs
to go into AG36 to make this work??

When Excel displays number signs (##), it's trying to tell you that the cell
isn't wide enough to display the result. Simply increase the width of the
column.

A good trick is to double click on the boundary of the column on the right
(when you get the double headed arrow). That will make the column wide
enough to display all results in the column.

Regards,
Fred.

"Michael Roback" wrote in message
...
I am uploading a copy of the workbook that I am working on so that it is
clear what I am talking about. I have set up a tool for my patients
where they can log a numerical rating by symptom, by day and see an
average of how they are responding to each symptom at the end of the
month and with symptoms overall each day. I want to provide an average
of all symptoms at the end of the month. You will notice on the
workbook that the dates are across the top and the symptoms are down the
side. The formula for the daily average of all symptoms is
=AVERAGE(B5:B34) and it works fine. The formula to average each symptom
as numerical values are entered through the end of the month is
=AVERAGE(B5:AF5) and it works fine. Problem is that when I enter
=AVERAGE(AG5:AG34) it is accepted as a formula and shows up as ## in the
orange cell, however when I enter data in the workbook this cell does
not display a numerical value. I know it is a average of averages,
however, it wouldn't matter. I am merely telling it to average a column
of numerical values, aren't I. What am I missing. I am a newbie and
just wanting to make this work!! Can anyone help? What formula needs
to go into AG36 to make this work??

To avoid the #DIV/0 errors which occur when you have no data, change your
formulas to:

=if(sum(...)=0,"",average(...))

Regards,
Fred.

"Michael Roback" wrote in message
...
I am uploading a copy of the workbook that I am working on so that it is
clear what I am talking about. I have set up a tool for my patients
where they can log a numerical rating by symptom, by day and see an
average of how they are responding to each symptom at the end of the
month and with symptoms overall each day. I want to provide an average
of all symptoms at the end of the month. You will notice on the
workbook that the dates are across the top and the symptoms are down the
side. The formula for the daily average of all symptoms is
=AVERAGE(B5:B34) and it works fine. The formula to average each symptom
as numerical values are entered through the end of the month is
=AVERAGE(B5:AF5) and it works fine. Problem is that when I enter
=AVERAGE(AG5:AG34) it is accepted as a formula and shows up as ## in the
orange cell, however when I enter data in the workbook this cell does
not display a numerical value. I know it is a average of averages,
however, it wouldn't matter. I am merely telling it to average a column
of numerical values, aren't I. What am I missing. I am a newbie and
just wanting to make this work!! Can anyone help? What formula needs
to go into AG36 to make this work??

One caveat, a SUM() of zero may be a legitimate return, better to use:

=IF(COUNT(....)=0,"",AVERAGE(.......))

or

=IF(COUNT(......),AVERAGE(.......),"")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Fred Smith" wrote in message
...
To avoid the #DIV/0 errors which occur when you have no data, change your
formulas to:

=if(sum(...)=0,"",average(...))

Regards,
Fred.

"Michael Roback" wrote in message
...
I am uploading a copy of the workbook that I am working on so that it is
clear what I am talking about. I have set up a tool for my patients
where they can log a numerical rating by symptom, by day and see an
average of how they are responding to each symptom at the end of the
month and with symptoms overall each day. I want to provide an average
of all symptoms at the end of the month. You will notice on the
workbook that the dates are across the top and the symptoms are down the
side. The formula for the daily average of all symptoms is
=AVERAGE(B5:B34) and it works fine. The formula to average each symptom
as numerical values are entered through the end of the month is
=AVERAGE(B5:AF5) and it works fine. Problem is that when I enter
=AVERAGE(AG5:AG34) it is accepted as a formula and shows up as ## in the
orange cell, however when I enter data in the workbook this cell does
not display a numerical value. I know it is a average of averages,
however, it wouldn't matter. I am merely telling it to average a column
of numerical values, aren't I. What am I missing. I am a newbie and
just wanting to make this work!! Can anyone help? What formula needs
to go into AG36 to make this work??