I used AVERAGE function in my spreasheet and received #DIV/0! error from it.
Is there a safe proof way to omit the invalid values out of the average
calculation.

Try this. I assumed your range was A1:A10, change this to fit your data.
Enter this formula by using CTRL+SHIFT+ENTER as it is an array formula:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10,""))


--
Regards,
Dave


"KhaVu" wrote:

I used AVERAGE function in my spreasheet and received #DIV/0! error from it.
Is there a safe proof way to omit the invalid values out of the average
calculation.

=IF(COUNT(A1:A10),AVERAGE(A1:A10),"")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KhaVu" wrote in message
...
I used AVERAGE function in my spreasheet and received #DIV/0! error from
it.
Is there a safe proof way to omit the invalid values out of the average
calculation.

#DIV/0! means there are no numbers in the range you are tryng to average.
Make sure the range contains at least one number and check that values that
look like numbers actually are numbers (no leading apostrophes or extra
spaces).

If the range will contain no values until they are input, you may want to
use something like this:

=IF(COUNT(A1:A10)=0,"no data to average",AVERAGE(A1:A10))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"KhaVu" wrote:

I used AVERAGE function in my spreasheet and received #DIV/0! error from it.
Is there a safe proof way to omit the invalid values out of the average
calculation.

Small update to my previous formula, adds the VALUE() function to convert
numbers stored as text to numerical values, still entered CTRL+SHIFT+ENTER:

=AVERAGE(IF(ISNUMBER(VALUE(A1:A10)),VALUE(A1:A10), ""))


--
Regards,
Dave


"David Billigmeier" wrote:

Try this. I assumed your range was A1:A10, change this to fit your data.
Enter this formula by using CTRL+SHIFT+ENTER as it is an array formula:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10,""))


--
Regards,
Dave


"KhaVu" wrote:

I used AVERAGE function in my spreasheet and received #DIV/0! error from it.
Is there a safe proof way to omit the invalid values out of the average
calculation.

David - my range is a link references from another worksheet and the
worksheet is in that network drive, here is a sample formula:

=AVERAGE('G:\Consumer Service\Telecom Reports\Monthly\ClientLogic Summary
Data 06\[CL Jan 30.xls]010106:010406'!$D$8)

Do you have a shorter/easier solution for this.

Thanks,

Kha

"David Billigmeier" wrote:

Try this. I assumed your range was A1:A10, change this to fit your data.
Enter this formula by using CTRL+SHIFT+ENTER as it is an array formula:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10,""))


--
Regards,
Dave


"KhaVu" wrote:

I used AVERAGE function in my spreasheet and received #DIV/0! error from it.
Is there a safe proof way to omit the invalid values out of the average
calculation.

By shorter/easier do you just mean you would like a solution where you don't
have to type the 'G:\ .....' link every time you reference the range? If so,
try this:

1) Go to <Insert<Name<Define...
2) Paste your reference in the "Refers to:" box at the bottom (the reference
starting with 'G:\ ....', and type a name for this (i.e. SummaryData)
3) Now, all you have to do is type SummaryData in your formula every time
you want to reference this. For example:

=AVERAGE(IF(ISNUMBER(VALUE(SummaryData)),VALUE(Sum maryData),""))

Does that help?
--
Regards,
Dave


"KhaVu" wrote:

David - my range is a link references from another worksheet and the
worksheet is in that network drive, here is a sample formula:

=AVERAGE('G:\Consumer Service\Telecom Reports\Monthly\ClientLogic Summary
Data 06\[CL Jan 30.xls]010106:010406'!$D$8)

Do you have a shorter/easier solution for this.

Thanks,

Kha

"David Billigmeier" wrote:

Try this. I assumed your range was A1:A10, change this to fit your data.
Enter this formula by using CTRL+SHIFT+ENTER as it is an array formula:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10,""))


--
Regards,
Dave


"KhaVu" wrote:

I used AVERAGE function in my spreasheet and received #DIV/0! error from it.
Is there a safe proof way to omit the invalid values out of the average
calculation.

Thank you David, Ron, and Bob for your solutions. All seem to work, I just
need to take a little time to understand each solution.

Kha

"David Billigmeier" wrote:

By shorter/easier do you just mean you would like a solution where you don't
have to type the 'G:\ .....' link every time you reference the range? If so,
try this:

1) Go to <Insert<Name<Define...
2) Paste your reference in the "Refers to:" box at the bottom (the reference
starting with 'G:\ ....', and type a name for this (i.e. SummaryData)
3) Now, all you have to do is type SummaryData in your formula every time
you want to reference this. For example:

=AVERAGE(IF(ISNUMBER(VALUE(SummaryData)),VALUE(Sum maryData),""))

Does that help?
--
Regards,
Dave


"KhaVu" wrote:

David - my range is a link references from another worksheet and the
worksheet is in that network drive, here is a sample formula:

=AVERAGE('G:\Consumer Service\Telecom Reports\Monthly\ClientLogic Summary
Data 06\[CL Jan 30.xls]010106:010406'!$D$8)

Do you have a shorter/easier solution for this.

Thanks,

Kha

"David Billigmeier" wrote:

Try this. I assumed your range was A1:A10, change this to fit your data.
Enter this formula by using CTRL+SHIFT+ENTER as it is an array formula:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10,""))


--
Regards,
Dave


"KhaVu" wrote:

I used AVERAGE function in my spreasheet and received #DIV/0! error from it.
Is there a safe proof way to omit the invalid values out of the average
calculation.