LinkBack URL
About LinkBacks
Good Afternoon,
I need to average excluding Div/0 but it's not a range. It would be seperate cells not A1:A10 but A1, D3, C5. Any ideas???
Good Afternoon,
I need to average excluding Div/0 but it's not a range. It would be seperate cells not A1:A10 but A1, D3, C5. Any ideas???
How did you get the DIV/0 errors?
You can fix those formulas to return a blank instead of DIV/0 error, then you can use
=AVERAGE(A1,D3,C5) and it would ignore the blank cells, if any.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
No, the formula is right. Denominator has to be zero, so it would return Div/0. I tried the ISNumber but it only works in the range not seperate cells.
Yeah, but you can say, if denominator is 0, then return a blank, otherwise process as usual... then this will avoid the error...
You can use the if(iserror( formula as stated by NBVC. This way you can have the value be blank or zero. I would go for blank so that it doesn't incorrectly mess with your average.
50
100 0 #DIV/0!
100 2 50 50
100 2 50 50
100 2 50 50
=AVERAGE(H7:H11)
100 0 =E7/F7 =IF(ISERROR(G7),"",G7)
100 2 =E8/F8 =IF(ISERROR(G8),"",G8)
100 2 =E9/F9 =IF(ISERROR(G9),"",G9)
100 2 =E10/F10 =IF(ISERROR(G10),"",G10)
Last edited by mstwntd; 06-14-2010 at 03:31 PM.
No, that's not what I want. Blank can be interpreted as zero. here is what I am trying to do
=AVERAGE(IF(ISNUMBER(G28,G30,G33),G28,G30,G33)). For some reason, it doesn't work? Can you correct it?
Last edited by olga6542; 06-14-2010 at 03:30 PM. Reason: had to clarify
It's not a range. It's a seperate cells. When I do it in the range, A1:A5 but when I do seperate cells, it doesn't.
What is in G29 and G31? if it is text, then you can use:
=AVERAGE(IF(ISNUMBER(G28:G33),G28:G33))
otherwise,
=AVERAGE(IF(MOD(ROW(G28:G33),2)=0,IF(ISNUMBER(G28:G33),G28:G33)))
these formulas need to be confirmed with CTRL+SHIFT+ENTER not just ENTER to work
The point I am trying to make it's not within a range: it's not G29 through G33. It is a seperate cells. The actual cells are: G20, G26, G31 and G36. I can't perform average based on G:20-G36. It has to be a seperate cells, that's where I run into problems.
Perhaps:
Please Login or Register to view this content.
Is it going to give me average???
=SUM(SUMIF(INDIRECT({"G20", "G26", "G31","G36"}),"<>#DIV/0!"))/SUM(COUNTIF(INDIRECT({"G20", "G26", "G31","G36"}),"<>#DIV/0!"))
Test it and see.
It is doing a sum divided by a count... basically that is what an average is.
On mine if you want to include where there isn't a value as zero just replace the "" with a zero. Then you are all set.
E7 F7 G7
100 0 =E7/F7 =IF(ISERROR(G7),0,G7 you will get 0 that will fall into the average counts
Last edited by mstwntd; 06-14-2010 at 04:58 PM.
For NBVC's suggestion you can get the denominator with a simple COUNT function, i.e.
=SUM(SUMIF(INDIRECT({"G20","G26","G31","G36"}),"<>#DIV/0!"))/COUNT(G20,G26,G31,G36)
Audere est facere
No, that's not going to work because if I add the rows, the cell won't be updated.
Try like this then.....
=(IF(COUNT(G20),G20)+IF(COUNT(G26),G26)+IF(COUNT(G31),G31)+IF(COUNT(G36),G36))/MAX(1,COUNT(G20,G26,G31,G36))
I found a solution:
=IF(ISERROR(AVERAGE(G20,G26,G31,G36)),"0",AVERAGE(G20,G26,G31,G36))
Well I guess we assumed that you wanted to average the remaining numbers if any of them were DIV/0...
Your formula suggests that if any of the cells has an error.. then abandon ship.
Ours will just ignore the error cell and average the remainder...
That is where I was saying to remove the "" marks and put a 0 in to make it have the value of zero. You may want to remove the "" from the "0". The value shows 0 but it is in text format only and you won't be able to do any mathmatical functions on it.
Here is the formula I created to meet your needs in a different way:
=IF(ISERROR(AVERAGE(IF(ISERROR(G20),0,G20),IF(ISERROR(G26),0,G26),IF(ISERROR(G31),0,G31),IF(ISERROR(G36),0,G36))),0,AVERAGE(IF(ISERROR(G20),0,G20),IF(ISERROR(G26),0,G26),IF(ISERROR(G31),0,G31),IF(ISERROR(G36),0,G36)))
This way each cell you are pulling has the oppertunity to be a zero from the error message. Your formula will force the whole thing to 0 if only one of the the parts has the #DIV/0! message. Test it.
You may even need to have another * IF(ISERROR( * function on the outside of all of it if every example has the #DIV/0! message.
Thanks for advice on removing quotes. I didn't catch that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks