Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I average cells if a cell is at #div/0!
ok heres what I have.
in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0! in cell D11 = AVERAGE(D12:D15) which its at 85 in cell D16 =AVERAGE(D17:D210) which its at 50 in cell D3 I just want the average of cell D6,D11,and D16 is this possible and how? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I average cells if a cell is at #div/0!
On Sep 24, 5:16 pm, delete automatically
wrote: ok heres what I have. in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0! in cell D11 = AVERAGE(D12:D15) which its at 85 in cell D16 =AVERAGE(D17:D210) which its at 50 in cell D3 I just want the average of cell D6,D11,and D16 is this possible and how? Possible, yes. But why allow errors in your worksheet? Handle the errors in your formulas and you won't have this problem. =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I average cells if a cell is at #div/0!
Change the D6 formula to =IF(ISERROR(AVERAGE(D7:D10)),"",AVERAGE(D7:D10))
But be careful taking the average of averages. Are you sure that you don't need a weighted mean? -- David Biddulph "delete automatically" wrote in message ... ok heres what I have. in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0! in cell D11 = AVERAGE(D12:D15) which its at 85 in cell D16 =AVERAGE(D17:D210) which its at 50 in cell D3 I just want the average of cell D6,D11,and D16 is this possible and how? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I average cells if a cell is at #div/0!
I assume you mean
=IF(ISERROR(AVERAGE(D7:D10)),0,AVERAGE(D7:D10)) and not =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10)) (with the missing closing parenthesis, and with an O where you had a zero), but do you really want to return a zero in the error case, or wouldn't =IF(ISERROR(AVERAGE(D7:D10)),"",AVERAGE(D7:D10)) be better so that it doesn't affect the next stage average in D3? -- David Biddulph "JW" wrote in message oups.com... On Sep 24, 5:16 pm, delete automatically wrote: ok heres what I have. in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0! in cell D11 = AVERAGE(D12:D15) which its at 85 in cell D16 =AVERAGE(D17:D210) which its at 50 in cell D3 I just want the average of cell D6,D11,and D16 is this possible and how? Possible, yes. But why allow errors in your worksheet? Handle the errors in your formulas and you won't have this problem. =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I average cells if a cell is at #div/0!
Thanks for the help, that worked fine but I think I have another problem. I
will post it when I figure what I need "JW" wrote: On Sep 24, 5:16 pm, delete automatically wrote: ok heres what I have. in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0! in cell D11 = AVERAGE(D12:D15) which its at 85 in cell D16 =AVERAGE(D17:D210) which its at 50 in cell D3 I just want the average of cell D6,D11,and D16 is this possible and how? Possible, yes. But why allow errors in your worksheet? Handle the errors in your formulas and you won't have this problem. =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I average cells if a cell is at #div/0!
On Sep 24, 6:03 pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote: I assume you mean =IF(ISERROR(AVERAGE(D7:D10)),0,AVERAGE(D7:D10)) and not =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10)) (with the missing closing parenthesis, and with an O where you had a zero), but do you really want to return a zero in the error case, or wouldn't =IF(ISERROR(AVERAGE(D7:D10)),"",AVERAGE(D7:D10)) be better so that it doesn't affect the next stage average in D3? -- David Biddulph "JW" wrote in message oups.com... On Sep 24, 5:16 pm, delete automatically wrote: ok heres what I have. in cell D6 I have formula of =AVERAGE(D7:D10) which its at #div/0! in cell D11 = AVERAGE(D12:D15) which its at 85 in cell D16 =AVERAGE(D17:D210) which its at 50 in cell D3 I just want the average of cell D6,D11,and D16 is this possible and how? Possible, yes. But why allow errors in your worksheet? Handle the errors in your formulas and you won't have this problem. =IF(ISERR0R(AVERAGE(D7:D10),0,AVERAGE(D7:D10)) Yes, left a parenthesis out. Typo on my part. As for the average portion, could be either way. In my line of work, those zeros need to be included in the grand average or I'd be reporting false data and get kicked to the curb! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
from a group of cells.find average of cells containing values | Excel Discussion (Misc queries) | |||
Excel-only average cells if two cells in same row, meet two condit | Excel Worksheet Functions | |||
average cells, show 0 if nothing to average | Excel Discussion (Misc queries) | |||
How do I average a range of cells when one cell contains #N/A | Excel Discussion (Misc queries) | |||
Average non-adjacent cells if the cell does not contain zero | Excel Discussion (Misc queries) |