AVERAGE function returns #DIV/0! error

[KhaVu]

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 Billigmeier]

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,""))

 

[Bob Phillips]

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

--

HTH

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

 

[Ron Coderre]

#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

 

[David Billigmeier]

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),""))

 

[KhaVu]

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]

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(SummaryData),""))

Does that help?
--
Regards,
Dave

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

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,""))

 

[KhaVu]

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

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(SummaryData),""))

Does that help?
--
Regards,
Dave

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

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


:

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.