# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] 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,""))


--
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.

----------


## Bob Phillips

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

--

HTH

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


"KhaVu" <KhaVu@discussions.microsoft.com> wrote in message
news:42FC4B70-C1A2-4D59-8F3F-E17F5B8D4BE0@microsoft.com...
> 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.

----------


## 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


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


--
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.

----------


## 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" 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 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


"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.

----------


## 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

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

----------


## SubKa

Hi David,

The function seems to work well only when the cell range is in the same column (in your example in column A). I wanted to calculate average for a set of three cells in a row. So, I tried the following formula:
=AVERAGE(IF(ISNUMBER(VALUE(B9:D9)),VALUE(B9:D9),""))
It returned the error message #VALUE!.

Is it possible to rewrite the formula to calculate average without getting #DIV/0 error for a set of cells in the same row?

Thank you.

----------


## arlu1201

SubKa,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## SubKa

Hi arfu1201,

I am absolutely confused by your message. I posted a follow up message which is the same issue discussed in the original thread. My point was that the solution offered worked well for averaging numbers in a single column (one column, many rows), but did not when it averaged numbers in multiple columns (one row, many columns). The situation is the same.

In all the forums that I am a member of, it would in fact be a violation of forum rules if I created a new thread which was already discussed in older threads! Posting new threads whereas the same issues have been already discussed in older threads would invite the wrath of users and admins. But, here you are asking me to violate this cardinal rule!

I did notice that the last message in the original thread was posted very many years ago. Are you trying to say that since the old thread ended its discussions very many years ago, I should create my own new thread? Kindly let me know.

SubKa

----------


## arlu1201

Firstly, this thread is many years old and hence you need to open a new thread to get better visibility.

If your question is just a slight enhancement of the current solution, then there are no issues in continuing here itself.  But if its a different solution or your file needs further customization, its better to create a separate thread and not confuse other users browsing this thread.

----------


## Tony Valko

Since this rule is open to interpretation it leads to confusion as to whether someone is violating the rule.

Either:

Get rid of this rule altogether and allow members to post in threads no matter how old they are.

Or:

Do not allow members to post in old threads under any circumstance.

Taking one or the other position should eliminate the  :Confused:

----------


## arlu1201

Thanks Tony.

I will bring this up for discussion among the mods and admins and see how it goes.

----------


## Tony Valko

OK, thanks for the feedback!  :Cool:

----------


## wlilley93

This post is aimed at beginners who have not followed earlier solutions posted in this thread, or have followed them but do not understand why they are solutions.

*Problem*

Sometimes, Excel interprets numbers as a string instead of as an integer.
This can lead to problems.
For instance, [1 + 1 = 2].
However, [a + b = #VALUE!], error, because there are no numbers to add together.
It follows that ["1" + "1" = #VALUE!], because there are no numbers to add together, only letters.
The average formula may display #DIV/0! because it is trying to divide what it thinks are letters, not numbers.

*How to spot*

You can usually tell whether Excel has realised its dealing with a number or a letter.

Ordinarily, a string (i.e. text) sits on the left of a cell, whereas numbers sit on the right:

Snap.png

When Excel incorrectly interprets a number as a string, the number will sit on the left too, as if it were a letter:

SNap 2.png


*Solution 1*

A quick solution is to click on the cell displaying the value you wish to average and hit Return.
This should jolt Excel into action and should make it realise you've entered a number, not a string.
The result will be that the number moves from the left of the cell to the right.

*Solution 2*

Sometimes you may have hundreds or thousands of numbers with the same issue.
This may happen if data has been exported from elsewhere.
It is not feasible to manually hit return on every cell.

Instead, try this:

1. Imagine the values you wish to use in your average formula are in Column A.
1. Insert a new 'helper' column next to the column that contains the values you are trying to use in your average formula. [e.g. Column B].
2. In B1, you would insert the formula [=A1+0].
3. This forces Excel to perform a numerical operation on the value in A1. 
4. The outcome of the operation is the same number as that displayed in A1. However, A1 does not equal B1. A1 is a letter, but B1 is a number.
5. Copy this formula down your Column B so that it applies to all values in Column A.
6. Highlight Column B. Copy it, and then right-click and paste it "as values" in Column A.
7. Column A will now display all the same numbers it did before, but excel will see numbers rather than letters.
8. Delete your helper column.
9. Use the values in Column A in your Avg formula.
10. Profit.

Hope this helps anyone who stumbles across this older thread.

Thanks
Will

----------

