LinkBack URL
About LinkBacksI 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.
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" <[email protected]> wrote in message
news:[email protected]...
> 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(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.
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.
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.
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.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
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
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.
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
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks Tony.
I will bring this up for discussion among the mods and admins and see how it goes.
OK, thanks for the feedback!
Re: AVERAGE function returns #DIV/0! error
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
Last edited by wlilley93; 11-13-2019 at 12:16 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks