Adding single cell in multiple worksheet then averaging

[maril]

I am stumped. My situation is I want to total let's say "A1" on multiple
worksheets but then want to average the total out by the number of
worksheets. Is there a formula that would address this?

 

[Mike H]

One way

Put a worksheet call 'Start' as you first worksheets and another call 'End'
as the last. You can hide them if you want.

Then the formula below will do what you want

=AVERAGE(start:End!A1)

Mike

 

[maril]

This helped some but I get a #Div/0 error when cells have "0" in it. I've
tried this formula but it seems it throws off the average by 1,
=average(startsheet!:endsheet!,a1,"0"). Can you tell me what I need to fix?

 

[Mike H]

Maril,

Try this

=AVERAGE(startsheet:endsheet!A1)

This will averge the contents of A1 on every sheet between 'startsheet' &
'endsheet' and Excel is clever enough to ignore blank cells. A zero in a cell
will be used in the calculation for average but wont give rise to a #Div/0
error. If your getting those then it's something else.

Mike

 

[maril]

Could it possibly be due to the fact that the cells have formulas in them?
If that's what's causing the problem, do you know how to fix it?

 

[maril]

Took some thinking but I got the answer:
=AVERAGE(startsheet:endsheet!A1,"0",startshee:endsheet!A1)