Average over multiple Sheets with DIV/0 Error

Hi Guys

i was trying a bit out. i think it could work with a bit of work;
i just enter
=average(if(isnumber(sheet1A8),sheet1A8);if(isnumber(sheet2A8),sheet1A8);if(isnumber(sheet3A8),sheet1A8);......;if(isnumber(sheetnA8),sheet1A8))

but now excel drives me crazy.

it now only takes the sum of all cells that contains numbers (how it should). but it divides them trough the all the cells he checked (including the cells not containing numbers).

e.g. 2,6,DIV/0,4

my calculation: the average of all values that are numbers are: 2+6+4=12 divided by 3 makes 4

excel calculation: 2+6+4=12 divided by 4 makes 3.

do i really have to enter
=sum(if(isnumber(sheet1A8),sheet1A8);if(isnumber(sheet2A8),sheet2A8);if(isnumber(sheet3A8),sheet3A8);......;if(isnumber(sheetnA8),sheetnA8))) / count(sheet1A8;sheet2A8;sheet3A8;....;sheetnA8)

is there any easyier way?

Try this.

On your sheet that has all the sums
Make another colume or row that has this line
=If(iserror(A1)=False,A1,0)
and fill right or down to get out all the errors to 0's

Now you should have a Column/Row of good numbers
sum em up

=sum(Range)

-Adil

that one took some hard thinking...

thank you, but it doesnt realy solve my problem.

basically i need a shorter form for
=sum(if(isnumber(sheet1A8),sheet1A8);if(isnumber(sheet2A8),sheet2A8);if(isnumber(sheet3A8),sheet3A8) ;......;if(isnumber(sheetnA8),sheetnA8))) / count(sheet1A8;sheet2A8;sheet3A8;....;sheetnA8)

take attention that those are different sheets, and there are some more sheets in between those sheets. so i definitlyhave to ad every single sheet into this formula manualy.

Ok, i get your problem now.
You want to have like a Fill down but instead of cells you want to fill sheets.

Every sheet in A8 is a number you want to add, that may or may not have an error.

Ok, well you need to make a macro but even then it's complicated.

You need to use &'s to split up the formula, so you can cycle the numbers in the sheet. I believe its quite complicated, but i dont see any easy way to do it.

Sorry

Originally Posted by hund

Hi Folks

need assistance

in a real big workbook with 150 sheets, i have to calculate in a seperate sheet the average of the same cell in every third sheet.
i would do this by hand, but i have trouble with some cells who are div/0 errors. i cant leave them out or delete them, becuase maybe somewhen they will change their value and get a real number.

so i tried with
{=average(if(isnumber(value),value))}

but i dont know how to be able to enter the value. excel wants me to put in a range like "A1:A10". instead of this i should be able somehow to enter "sheet1A8, sheet3A8, sheet5A8,...".

i have to do this for 250 cells. they all are calculating the same average of their cells behind. like in this average sheet in cell S8 the average of the S8-cells who are not an error of every specific sheet shall be entered.

hope you guys understand my english...

my workaround idea so far is a makro that makes the error disapear in every sheet by entering in every cell a if(iserror) test. but this would mean changing 50 times 250 cells, and i rather would only resolve this problem in the last sheet.... but then i would be at least able to enter =average(sheet1S8, sheet3S8, sheet4S8,....)

ty

Try this:

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:8"))&"!A8"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:8"))&"!A8"),">0"))