Running average

[Robert B]

Hi all,
In Excel '97 I am trying to calculate a running average
using up to 52 cell references over 52 worksheets in a
workbook ie: =average(wk1!G3,wk2!g3.........etc). The
formula worked for up to 30 arguments, however now I
require more than 30 arguments as I am now working in week
31 of the financial year. I've tried concatenation but,
whilst it arrives at the running average answer, Excel
gives me an answer in text with up to 14 or so decimal
points. I require it in number form. Are you able to
offer a solution?
Regards,
Robert B

 

[Tom Ogilvy]

Use 3D references

=Sum('wk1:wk31'!G3)

this will sum G3 in any sheet between a sheet named wk1 and a sheet named
wk31 in the tab order.

you could put in two blank sheets named Start and End,

then move you weekly sheets between these two sheets (add the new weekly
sheet each week.

your formula would be

=Sum(Start:end!G3)

and you would not need to adjust it.

 

[Roger Govier]

Hi Robert

Try
=AVERAGE(Wk1:Wk52!G3)

or create a Sheet called First and another called Last and use
=AVERAGE(First:Last!G53)

You can move the position of First and Last if required, and you will get
the avergae of the range of sheets contained between First and Last

 

[Robert B]

Thanks Roger, will try.

-----Original Message-----
Hi Robert

Try
=AVERAGE(Wk1:Wk52!G3)

or create a Sheet called First and another called Last and use
=AVERAGE(First:Last!G53)

You can move the position of First and Last if required, and you will get
the avergae of the range of sheets contained between First and Last

--
Regards
Roger Govier



.

 

[Robert B]

Thanks Tom, will try.
Robert.

-----Original Message-----
Use 3D references

=Sum('wk1:wk31'!G3)

this will sum G3 in any sheet between a sheet named wk1 and a sheet named
wk31 in the tab order.

you could put in two blank sheets named Start and End,

then move you weekly sheets between these two sheets (add the new weekly
sheet each week.

your formula would be

=Sum(Start:end!G3)

and you would not need to adjust it.

--
Regards,
Tom Ogilvy




.