Adding single cell in multiple worksheet then averaging

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maril - 15 Jan 2008 13:41 GMT

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?

Reply to this Message

Mike H - 15 Jan 2008 13:53 GMT

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

> 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?

Reply to this Message

maril - 15 Jan 2008 14:21 GMT

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?

> One way
>
[quoted text clipped - 10 lines]
> > worksheets but then want to average the total out by the number of
> > worksheets. Is there a formula that would address this?

Reply to this Message

Mike H - 15 Jan 2008 14:29 GMT

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

> 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,
[quoted text clipped - 14 lines]
> > > worksheets but then want to average the total out by the number of
> > > worksheets. Is there a formula that would address this?

Reply to this Message

maril - 15 Jan 2008 15:29 GMT

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,
>
[quoted text clipped - 27 lines]
> > > > worksheets but then want to average the total out by the number of
> > > > worksheets. Is there a formula that would address this?

Reply to this Message

maril - 15 Jan 2008 19:40 GMT

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

> Maril,
>
[quoted text clipped - 27 lines]
> > > > worksheets but then want to average the total out by the number of
> > > > worksheets. Is there a formula that would address this?

Reply to this Message