Hi
Can anyone advise me please of a formula to calculate the average for a
range where only numerals are present. i.e. I have a column with a number of
text values in which the numeral value has no entry?
Thank you in advance.

Regards
Tanya

The AVERAGE function ignores text


Per Excel Help for the AVERAGE function:
>>If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.<<

Are you experiencing something different?

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Tanya" wrote:

> Hi
> Can anyone advise me please of a formula to calculate the average for a
> range where only numerals are present. i.e. I have a column with a number of
> text values in which the numeral value has no entry?
> Thank you in advance.
>
> Regards
> Tanya

Sun, 1 Jul 2007 06:28:01 -0700 from Tanya
<(E-Mail Removed)>:

> Can anyone advise me please of a formula to calculate the average for a
> range where only numerals are present. i.e. I have a column with a number of
> text values in which the numeral value has no entry?

Have you checked Excel help for the function =AVERAGE() ? It sure
looks to me like it marches your requirements.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Hi Ron,
I'm sorry, I didn't make myself clear, you are quite right, if there are
values to average, the AVERAGE function will ignore text, however my table
has no values until I update the table, in the mean time I get #DIV/0!

Is there a way to avoid this error message?

It probably sounds like a ludicrous situation, but I am working with class
results for a school year and until the task has been completed by the class
and results recorded there are no values to average.

Kind Regards
Tanya

"Ron Coderre" wrote:

> The AVERAGE function ignores text
>
>
> Per Excel Help for the AVERAGE function:
> >>If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.<<
>
> Are you experiencing something different?
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Tanya" wrote:
>
> > Hi
> > Can anyone advise me please of a formula to calculate the average for a
> > range where only numerals are present. i.e. I have a column with a number of
> > text values in which the numeral value has no entry?
> > Thank you in advance.
> >
> > Regards
> > Tanya

One way:

=IF(COUNT(B1:B10)>0,AVERAGE(B1:B10),"")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tanya" <(E-Mail Removed)> wrote in message
news:639D3470-510F-47B7-987A-(E-Mail Removed)...
> Hi Ron,
> I'm sorry, I didn't make myself clear, you are quite right, if there are
> values to average, the AVERAGE function will ignore text, however my table
> has no values until I update the table, in the mean time I get #DIV/0!
>
> Is there a way to avoid this error message?
>
> It probably sounds like a ludicrous situation, but I am working with class
> results for a school year and until the task has been completed by the
class
> and results recorded there are no values to average.
>
> Kind Regards
> Tanya
>
> "Ron Coderre" wrote:
>
> > The AVERAGE function ignores text
> >
> >
> > Per Excel Help for the AVERAGE function:
> > >>If an array or reference argument contains text, logical values, or
empty cells, those values are ignored; however, cells with the value zero
are included.<<
> >
> > Are you experiencing something different?
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Tanya" wrote:
> >
> > > Hi
> > > Can anyone advise me please of a formula to calculate the average for
a
> > > range where only numerals are present. i.e. I have a column with a
number of
> > > text values in which the numeral value has no entry?
> > > Thank you in advance.
> > >
> > > Regards
> > > Tanya

Thank you, I appreciate your help.
Kind Regards
Tanya

"Ragdyer" wrote:

> One way:
>
> =IF(COUNT(B1:B10)>0,AVERAGE(B1:B10),"")
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Tanya" <(E-Mail Removed)> wrote in message
> news:639D3470-510F-47B7-987A-(E-Mail Removed)...
> > Hi Ron,
> > I'm sorry, I didn't make myself clear, you are quite right, if there are
> > values to average, the AVERAGE function will ignore text, however my table
> > has no values until I update the table, in the mean time I get #DIV/0!
> >
> > Is there a way to avoid this error message?
> >
> > It probably sounds like a ludicrous situation, but I am working with class
> > results for a school year and until the task has been completed by the
> class
> > and results recorded there are no values to average.
> >
> > Kind Regards
> > Tanya
> >
> > "Ron Coderre" wrote:
> >
> > > The AVERAGE function ignores text
> > >
> > >
> > > Per Excel Help for the AVERAGE function:
> > > >>If an array or reference argument contains text, logical values, or
> empty cells, those values are ignored; however, cells with the value zero
> are included.<<
> > >
> > > Are you experiencing something different?
> > >
> > > Does that help?
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP
> > >
> > >
> > > "Tanya" wrote:
> > >
> > > > Hi
> > > > Can anyone advise me please of a formula to calculate the average for
> a
> > > > range where only numerals are present. i.e. I have a column with a
> number of
> > > > text values in which the numeral value has no entry?
> > > > Thank you in advance.
> > > >
> > > > Regards
> > > > Tanya
>
>