Adding single cell in multiple worksheet then averaging

  • Thread starter Thread starter maril
  • Start date Start date
M

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?
 
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
 
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?
 
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
 
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?
 
Took some thinking but I got the answer:
=AVERAGE(startsheet:endsheet!A1,"0",startshee:endsheet!A1)
 
Back
Top