Adding single cell in multiple worksheet then averaging
 

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?

Adding single cell in multiple worksheet then averaging
 

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" wrote:

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?

Adding single cell in multiple worksheet then averaging
 

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" wrote:

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" wrote:

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?

Adding single cell in multiple worksheet then averaging
 

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" wrote:

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" wrote:

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" wrote:

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?

Adding single cell in multiple worksheet then averaging
 

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?

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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?

Adding single cell in multiple worksheet then averaging
 

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

"Mike H" wrote:

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" wrote:

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" wrote:

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" wrote:

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?