Holiday Pay

How do I set up a spreadsheet to get an average of the last 12 weeks pay. This is obviously a rolling figure changing from week to week.

I assume you have observations for each week of pay. Use the SUMIF
function. I don't have time to find other links for you. I'm sure someone
else can.

"johnrann" <johnrann.1say2b_1121583928.3138@excelforum-nospam.com> wrote in
message news:johnrann.1say2b_1121583928.3138@excelforum-nospam.com...
>
> How do I set up a spreadsheet to get an average of the last 12 weeks
> pay. This is obviously a rolling figure changing from week to week.
>
>
> --
> johnrann
> ------------------------------------------------------------------------
> johnrann's Profile:
> http://www.excelforum.com/member.php...o&userid=25295
> View this thread: http://www.excelforum.com/showthread...hreadid=387815
>

Assuming the data is in A1:A100, use

=SUMPRODUCT(--(LARGE(A1:A100,ROW(INDIRECT("1:12")))))

--
HTH

Bob Phillips

"johnrann" <johnrann.1say2b_1121583928.3138@excelforum-nospam.com> wrote in
message news:johnrann.1say2b_1121583928.3138@excelforum-nospam.com...
>
> How do I set up a spreadsheet to get an average of the last 12 weeks
> pay. This is obviously a rolling figure changing from week to week.
>
>
> --
> johnrann
> ------------------------------------------------------------------------
> johnrann's Profile:
http://www.excelforum.com/member.php...o&userid=25295
> View this thread: http://www.excelforum.com/showthread...hreadid=387815
>

John,

To get the average - rather than the SUM - then with your data starting in
A1, in row 12, (any column), try:

=SUM(OFFSET(A1,0,0,12,1))/12

and copy down the column

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"johnrann" <johnrann.1say2b_1121583928.3138@excelforum-nospam.com> wrote in
message news:johnrann.1say2b_1121583928.3138@excelforum-nospam.com...
>
> How do I set up a spreadsheet to get an average of the last 12 weeks
> pay. This is obviously a rolling figure changing from week to week.
>
>
> --
> johnrann
> ------------------------------------------------------------------------
> johnrann's Profile:
http://www.excelforum.com/member.php...o&userid=25295
> View this thread: http://www.excelforum.com/showthread...hreadid=387815
>

> =SUM(OFFSET(A1,0,0,12,1))/12

or simply:

=AVERAGE(OFFSET(A1,0,0,12,1))

--
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk
"Sandy Mann" <sandymann2@mailinator.com> wrote in message
news:#Sid4xsiFHA.3164@TK2MSFTNGP15.phx.gbl...
> John,
>
> To get the average - rather than the SUM - then with your data starting in
> A1, in row 12, (any column), try:
>
> =SUM(OFFSET(A1,0,0,12,1))/12
>
> and copy down the column
>
> --
> HTH
>
> Sandy
> sandymann@mailinator.com
> Replace@mailinator with @tiscali.co.uk
>
>
> "johnrann" <johnrann.1say2b_1121583928.3138@excelforum-nospam.com> wrote
in
> message news:johnrann.1say2b_1121583928.3138@excelforum-nospam.com...
> >
> > How do I set up a spreadsheet to get an average of the last 12 weeks
> > pay. This is obviously a rolling figure changing from week to week.
> >
> >
> > --
> > johnrann
> > ------------------------------------------------------------------------
> > johnrann's Profile:
> http://www.excelforum.com/member.php...o&userid=25295
> > View this thread:
http://www.excelforum.com/showthread...hreadid=387815
> >
>
>

Avearge, divide by

MAX(12,COUNTA(A:A))

--
HTH

Bob Phillips

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:unMg6EsiFHA.1948@TK2MSFTNGP12.phx.gbl...
> Assuming the data is in A1:A100, use
>
> =SUMPRODUCT(--(LARGE(A1:A100,ROW(INDIRECT("1:12")))))
>
> --
> HTH
>
> Bob Phillips
>
> "johnrann" <johnrann.1say2b_1121583928.3138@excelforum-nospam.com> wrote
in
> message news:johnrann.1say2b_1121583928.3138@excelforum-nospam.com...
> >
> > How do I set up a spreadsheet to get an average of the last 12 weeks
> > pay. This is obviously a rolling figure changing from week to week.
> >
> >
> > --
> > johnrann
> > ------------------------------------------------------------------------
> > johnrann's Profile:
> http://www.excelforum.com/member.php...o&userid=25295
> > View this thread:
http://www.excelforum.com/showthread...hreadid=387815
> >
>
>