Average function assistance

[Larry L]

I need help with developing an average function for a golf league. We use an
avarage of the last 5 scores posted to develop the person's handicap. Each
week a new score is added to the data and a new average calculated with the
5th oldest score being dropped and newest score being included in the
average. The data is kept on the worksheet in rows. Because a golfer may miss
a week, some rows may have blanks that should not be considered.

For example a person's scores may look like this for the season:
45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and 49 is
the latest score). I want the formulae to consider the 5 non zero scores from
the oldest - 49 (right to left).

I was given this formulae:

=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
+ control/shift & enter

However I could not get it to work.

[Biff]

Hi!

That formula does work.

This one is a little less complicated.....

Assume your scores are in row 1 and you add a new score weekly (or not, if
you miss that week). Your golf season is 25 weeks long. Cell A1 is for the
players name. The weekly scores start in cell B1 and the last cell for the
25th week is cell Z1.

To get the average of the last 5 scores enter this formula using the key
combo of CTRL,SHIFT,ENTER:

=AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))

Biff

"Larry L" <LarryL@discussions.microsoft.com> wrote in message
news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
>I need help with developing an average function for a golf league. We use
>an
> avarage of the last 5 scores posted to develop the person's handicap. Each
> week a new score is added to the data and a new average calculated with
> the
> 5th oldest score being dropped and newest score being included in the
> average. The data is kept on the worksheet in rows. Because a golfer may
> miss
> a week, some rows may have blanks that should not be considered.
>
> For example a person's scores may look like this for the season:
> 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and 49
> is
> the latest score). I want the formulae to consider the 5 non zero scores
> from
> the oldest - 49 (right to left).
>
> I was given this formulae:
>
> =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
> + control/shift & enter
>
> However I could not get it to work.
>
>
>

[Larry L]

The formulae gave me a wrong calculation. The specific data I had is below:

45 42 45 46 40 41 44 44 44 41 38 39 43

The last 5 scores totalled 205 = 41.00 average

The formulae calculated 42.25

The fomulae I used =
{=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535)-1),5)))}

The input data started in cell T535 and ended in cellT535 (18 weeks of
data). What did I do incorrectly?








"Biff" wrote:

> Hi!
>
> That formula does work.
>
> This one is a little less complicated.....
>
> Assume your scores are in row 1 and you add a new score weekly (or not, if
> you miss that week). Your golf season is 25 weeks long. Cell A1 is for the
> players name. The weekly scores start in cell B1 and the last cell for the
> 25th week is cell Z1.
>
> To get the average of the last 5 scores enter this formula using the key
> combo of CTRL,SHIFT,ENTER:
>
> =AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))
>
> Biff
>
> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
> >I need help with developing an average function for a golf league. We use
> >an
> > avarage of the last 5 scores posted to develop the person's handicap. Each
> > week a new score is added to the data and a new average calculated with
> > the
> > 5th oldest score being dropped and newest score being included in the
> > average. The data is kept on the worksheet in rows. Because a golfer may
> > miss
> > a week, some rows may have blanks that should not be considered.
> >
> > For example a person's scores may look like this for the season:
> > 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and 49
> > is
> > the latest score). I want the formulae to consider the 5 non zero scores
> > from
> > the oldest - 49 (right to left).
> >
> > I was given this formulae:
> >
> > =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
> > + control/shift & enter
> >
> > However I could not get it to work.
> >
> >
> >
>
>
>

[Biff]

Hi!

Try this:

Array entered:

=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-3),5)))

Biff

"Larry L" <LarryL@discussions.microsoft.com> wrote in message
news:0785281D-FF1E-4620-AEF2-3C97E80ABBF9@microsoft.com...
> The formulae gave me a wrong calculation. The specific data I had is
> below:
>
> 45 42 45 46 40 41 44 44 44 41 38 39 43
>
> The last 5 scores totalled 205 = 41.00 average
>
> The formulae calculated 42.25
>
> The fomulae I used =
> {=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535)-1),5)))}
>
> The input data started in cell T535 and ended in cellT535 (18 weeks of
> data). What did I do incorrectly?
>
>
>
>
>
>
>
>
> "Biff" wrote:
>
>> Hi!
>>
>> That formula does work.
>>
>> This one is a little less complicated.....
>>
>> Assume your scores are in row 1 and you add a new score weekly (or not,
>> if
>> you miss that week). Your golf season is 25 weeks long. Cell A1 is for
>> the
>> players name. The weekly scores start in cell B1 and the last cell for
>> the
>> 25th week is cell Z1.
>>
>> To get the average of the last 5 scores enter this formula using the key
>> combo of CTRL,SHIFT,ENTER:
>>
>> =AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))
>>
>> Biff
>>
>> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
>> news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
>> >I need help with developing an average function for a golf league. We
>> >use
>> >an
>> > avarage of the last 5 scores posted to develop the person's handicap.
>> > Each
>> > week a new score is added to the data and a new average calculated with
>> > the
>> > 5th oldest score being dropped and newest score being included in the
>> > average. The data is kept on the worksheet in rows. Because a golfer
>> > may
>> > miss
>> > a week, some rows may have blanks that should not be considered.
>> >
>> > For example a person's scores may look like this for the season:
>> > 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and
>> > 49
>> > is
>> > the latest score). I want the formulae to consider the 5 non zero
>> > scores
>> > from
>> > the oldest - 49 (right to left).
>> >
>> > I was given this formulae:
>> >
>> > =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
>> > + control/shift & enter
>> >
>> > However I could not get it to work.
>> >
>> >
>> >
>>
>>
>>

[Biff]

Ooops!

Make that:

=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-2),5)))

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:uR6ptLhqFHA.240@tk2msftngp13.phx.gbl...
> Hi!
>
> Try this:
>
> Array entered:
>
> =AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-3),5)))
>
> Biff
>
> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> news:0785281D-FF1E-4620-AEF2-3C97E80ABBF9@microsoft.com...
>> The formulae gave me a wrong calculation. The specific data I had is
>> below:
>>
>> 45 42 45 46 40 41 44 44 44 41 38 39 43
>>
>> The last 5 scores totalled 205 = 41.00 average
>>
>> The formulae calculated 42.25
>>
>> The fomulae I used =
>> {=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535)-1),5)))}
>>
>> The input data started in cell T535 and ended in cellT535 (18 weeks of
>> data). What did I do incorrectly?
>>
>>
>>
>>
>>
>>
>>
>>
>> "Biff" wrote:
>>
>>> Hi!
>>>
>>> That formula does work.
>>>
>>> This one is a little less complicated.....
>>>
>>> Assume your scores are in row 1 and you add a new score weekly (or not,
>>> if
>>> you miss that week). Your golf season is 25 weeks long. Cell A1 is for
>>> the
>>> players name. The weekly scores start in cell B1 and the last cell for
>>> the
>>> 25th week is cell Z1.
>>>
>>> To get the average of the last 5 scores enter this formula using the key
>>> combo of CTRL,SHIFT,ENTER:
>>>
>>> =AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))
>>>
>>> Biff
>>>
>>> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
>>> news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
>>> >I need help with developing an average function for a golf league. We
>>> >use
>>> >an
>>> > avarage of the last 5 scores posted to develop the person's handicap.
>>> > Each
>>> > week a new score is added to the data and a new average calculated
>>> > with
>>> > the
>>> > 5th oldest score being dropped and newest score being included in the
>>> > average. The data is kept on the worksheet in rows. Because a golfer
>>> > may
>>> > miss
>>> > a week, some rows may have blanks that should not be considered.
>>> >
>>> > For example a person's scores may look like this for the season:
>>> > 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and
>>> > 49
>>> > is
>>> > the latest score). I want the formulae to consider the 5 non zero
>>> > scores
>>> > from
>>> > the oldest - 49 (right to left).
>>> >
>>> > I was given this formulae:
>>> >
>>> > =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
>>> > + control/shift & enter
>>> >
>>> > However I could not get it to work.
>>> >
>>> >
>>> >
>>>
>>>
>>>
>
>

[Larry L]

Still not working correctly yet.

The first formulae resulted in taking away the 1st score and then dividing
the total by the number of times played.

The 2nd formulae worked correctly on the 1st golfer but didn't calculate
correctly on the 2nd (note the 2nd golfer missed the 1st week was the only
difference)

The 3rd formulae resulted in the last score being displayed.

Thanks for your help



"Biff" wrote:

> Ooops!
>
> Make that:
>
> =AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-2),5)))
>
> Biff
>
> "Biff" <biffinpitt@comcast.net> wrote in message
> news:uR6ptLhqFHA.240@tk2msftngp13.phx.gbl...
> > Hi!
> >
> > Try this:
> >
> > Array entered:
> >
> > =AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-3),5)))
> >
> > Biff
> >
> > "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> > news:0785281D-FF1E-4620-AEF2-3C97E80ABBF9@microsoft.com...
> >> The formulae gave me a wrong calculation. The specific data I had is
> >> below:
> >>
> >> 45 42 45 46 40 41 44 44 44 41 38 39 43
> >>
> >> The last 5 scores totalled 205 = 41.00 average
> >>
> >> The formulae calculated 42.25
> >>
> >> The fomulae I used =
> >> {=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535)-1),5)))}
> >>
> >> The input data started in cell T535 and ended in cellT535 (18 weeks of
> >> data). What did I do incorrectly?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Biff" wrote:
> >>
> >>> Hi!
> >>>
> >>> That formula does work.
> >>>
> >>> This one is a little less complicated.....
> >>>
> >>> Assume your scores are in row 1 and you add a new score weekly (or not,
> >>> if
> >>> you miss that week). Your golf season is 25 weeks long. Cell A1 is for
> >>> the
> >>> players name. The weekly scores start in cell B1 and the last cell for
> >>> the
> >>> 25th week is cell Z1.
> >>>
> >>> To get the average of the last 5 scores enter this formula using the key
> >>> combo of CTRL,SHIFT,ENTER:
> >>>
> >>> =AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))
> >>>
> >>> Biff
> >>>
> >>> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> >>> news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
> >>> >I need help with developing an average function for a golf league. We
> >>> >use
> >>> >an
> >>> > avarage of the last 5 scores posted to develop the person's handicap.
> >>> > Each
> >>> > week a new score is added to the data and a new average calculated
> >>> > with
> >>> > the
> >>> > 5th oldest score being dropped and newest score being included in the
> >>> > average. The data is kept on the worksheet in rows. Because a golfer
> >>> > may
> >>> > miss
> >>> > a week, some rows may have blanks that should not be considered.
> >>> >
> >>> > For example a person's scores may look like this for the season:
> >>> > 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and
> >>> > 49
> >>> > is
> >>> > the latest score). I want the formulae to consider the 5 non zero
> >>> > scores
> >>> > from
> >>> > the oldest - 49 (right to left).
> >>> >
> >>> > I was given this formulae:
> >>> >
> >>> > =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
> >>> > + control/shift & enter
> >>> >
> >>> > However I could not get it to work.
> >>> >
> >>> >
> >>> >
> >>>
> >>>
> >>>
> >
> >
>
>
>

[Larry L]

If I would go to the original formulae:

=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))

How would I change this to accommodate my data in cells C535 to T535 for the
1st golfer and C536 to T536 for the 2nd and so on?

"Larry L" wrote:

> Still not working correctly yet.
>
> The first formulae resulted in taking away the 1st score and then dividing
> the total by the number of times played.
>
> The 2nd formulae worked correctly on the 1st golfer but didn't calculate
> correctly on the 2nd (note the 2nd golfer missed the 1st week was the only
> difference)
>
> The 3rd formulae resulted in the last score being displayed.
>
> Thanks for your help
>
>
>
> "Biff" wrote:
>
> > Ooops!
> >
> > Make that:
> >
> > =AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-2),5)))
> >
> > Biff
> >
> > "Biff" <biffinpitt@comcast.net> wrote in message
> > news:uR6ptLhqFHA.240@tk2msftngp13.phx.gbl...
> > > Hi!
> > >
> > > Try this:
> > >
> > > Array entered:
> > >
> > > =AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-3),5)))
> > >
> > > Biff
> > >
> > > "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> > > news:0785281D-FF1E-4620-AEF2-3C97E80ABBF9@microsoft.com...
> > >> The formulae gave me a wrong calculation. The specific data I had is
> > >> below:
> > >>
> > >> 45 42 45 46 40 41 44 44 44 41 38 39 43
> > >>
> > >> The last 5 scores totalled 205 = 41.00 average
> > >>
> > >> The formulae calculated 42.25
> > >>
> > >> The fomulae I used =
> > >> {=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535)-1),5)))}
> > >>
> > >> The input data started in cell T535 and ended in cellT535 (18 weeks of
> > >> data). What did I do incorrectly?
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "Biff" wrote:
> > >>
> > >>> Hi!
> > >>>
> > >>> That formula does work.
> > >>>
> > >>> This one is a little less complicated.....
> > >>>
> > >>> Assume your scores are in row 1 and you add a new score weekly (or not,
> > >>> if
> > >>> you miss that week). Your golf season is 25 weeks long. Cell A1 is for
> > >>> the
> > >>> players name. The weekly scores start in cell B1 and the last cell for
> > >>> the
> > >>> 25th week is cell Z1.
> > >>>
> > >>> To get the average of the last 5 scores enter this formula using the key
> > >>> combo of CTRL,SHIFT,ENTER:
> > >>>
> > >>> =AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))
> > >>>
> > >>> Biff
> > >>>
> > >>> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> > >>> news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
> > >>> >I need help with developing an average function for a golf league. We
> > >>> >use
> > >>> >an
> > >>> > avarage of the last 5 scores posted to develop the person's handicap.
> > >>> > Each
> > >>> > week a new score is added to the data and a new average calculated
> > >>> > with
> > >>> > the
> > >>> > 5th oldest score being dropped and newest score being included in the
> > >>> > average. The data is kept on the worksheet in rows. Because a golfer
> > >>> > may
> > >>> > miss
> > >>> > a week, some rows may have blanks that should not be considered.
> > >>> >
> > >>> > For example a person's scores may look like this for the season:
> > >>> > 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest score and
> > >>> > 49
> > >>> > is
> > >>> > the latest score). I want the formulae to consider the 5 non zero
> > >>> > scores
> > >>> > from
> > >>> > the oldest - 49 (right to left).
> > >>> >
> > >>> > I was given this formulae:
> > >>> >
> > >>> > =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
> > >>> > + control/shift & enter
> > >>> >
> > >>> > However I could not get it to work.
> > >>> >
> > >>> >
> > >>> >
> > >>>
> > >>>
> > >>>
> > >
> > >
> >
> >
> >

[Bob Phillips]

=AVERAGE(TRANSPOSE(OFFSET(535,0,LARGE((535:535>0)*(COLUMN(1:1)),{1,2,3,4,5})
-1)))

and copy down.

Still array formulae

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Larry L" <LarryL@discussions.microsoft.com> wrote in message
news:2D953FA9-7C4D-4B43-B6CB-D3C753EFB5D9@microsoft.com...
>
> If I would go to the original formulae:
>
>
=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
>
> How would I change this to accommodate my data in cells C535 to T535 for
the
> 1st golfer and C536 to T536 for the 2nd and so on?
>
> "Larry L" wrote:
>
> > Still not working correctly yet.
> >
> > The first formulae resulted in taking away the 1st score and then
dividing
> > the total by the number of times played.
> >
> > The 2nd formulae worked correctly on the 1st golfer but didn't calculate
> > correctly on the 2nd (note the 2nd golfer missed the 1st week was the
only
> > difference)
> >
> > The 3rd formulae resulted in the last score being displayed.
> >
> > Thanks for your help
> >
> >
> >
> > "Biff" wrote:
> >
> > > Ooops!
> > >
> > > Make that:
> > >
> > >
=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-2),5)
))
> > >
> > > Biff
> > >
> > > "Biff" <biffinpitt@comcast.net> wrote in message
> > > news:uR6ptLhqFHA.240@tk2msftngp13.phx.gbl...
> > > > Hi!
> > > >
> > > > Try this:
> > > >
> > > > Array entered:
> > > >
> > > >
=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-3),5)
))
> > > >
> > > > Biff
> > > >
> > > > "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> > > > news:0785281D-FF1E-4620-AEF2-3C97E80ABBF9@microsoft.com...
> > > >> The formulae gave me a wrong calculation. The specific data I had
is
> > > >> below:
> > > >>
> > > >> 45 42 45 46 40 41 44 44 44 41 38 39 43
> > > >>
> > > >> The last 5 scores totalled 205 = 41.00 average
> > > >>
> > > >> The formulae calculated 42.25
> > > >>
> > > >> The fomulae I used =
> > > >>
{=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535)-1),5)))}
> > > >>
> > > >> The input data started in cell T535 and ended in cellT535 (18 weeks
of
> > > >> data). What did I do incorrectly?
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >> "Biff" wrote:
> > > >>
> > > >>> Hi!
> > > >>>
> > > >>> That formula does work.
> > > >>>
> > > >>> This one is a little less complicated.....
> > > >>>
> > > >>> Assume your scores are in row 1 and you add a new score weekly (or
not,
> > > >>> if
> > > >>> you miss that week). Your golf season is 25 weeks long. Cell A1 is
for
> > > >>> the
> > > >>> players name. The weekly scores start in cell B1 and the last cell
for
> > > >>> the
> > > >>> 25th week is cell Z1.
> > > >>>
> > > >>> To get the average of the last 5 scores enter this formula using
the key
> > > >>> combo of CTRL,SHIFT,ENTER:
> > > >>>
> > > >>> =AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))
> > > >>>
> > > >>> Biff
> > > >>>
> > > >>> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
> > > >>> news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
> > > >>> >I need help with developing an average function for a golf
league. We
> > > >>> >use
> > > >>> >an
> > > >>> > avarage of the last 5 scores posted to develop the person's
handicap.
> > > >>> > Each
> > > >>> > week a new score is added to the data and a new average
calculated
> > > >>> > with
> > > >>> > the
> > > >>> > 5th oldest score being dropped and newest score being included
in the
> > > >>> > average. The data is kept on the worksheet in rows. Because a
golfer
> > > >>> > may
> > > >>> > miss
> > > >>> > a week, some rows may have blanks that should not be considered.
> > > >>> >
> > > >>> > For example a person's scores may look like this for the season:
> > > >>> > 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest
score and
> > > >>> > 49
> > > >>> > is
> > > >>> > the latest score). I want the formulae to consider the 5 non
zero
> > > >>> > scores
> > > >>> > from
> > > >>> > the oldest - 49 (right to left).
> > > >>> >
> > > >>> > I was given this formulae:
> > > >>> >
> > > >>> >
=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
> > > >>> > + control/shift & enter
> > > >>> >
> > > >>> > However I could not get it to work.
> > > >>> >
> > > >>> >
> > > >>> >
> > > >>>
> > > >>>
> > > >>>
> > > >
> > > >
> > >
> > >
> > >

[Biff]

Hi!

If the first week score is in column C and the last weeks score will be in
column T:

=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(A:R)),5)))

Biff

"Larry L" <LarryL@discussions.microsoft.com> wrote in message
news:2D953FA9-7C4D-4B43-B6CB-D3C753EFB5D9@microsoft.com...
>
> If I would go to the original formulae:
>
> =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
>
> How would I change this to accommodate my data in cells C535 to T535 for
> the
> 1st golfer and C536 to T536 for the 2nd and so on?
>
> "Larry L" wrote:
>
>> Still not working correctly yet.
>>
>> The first formulae resulted in taking away the 1st score and then
>> dividing
>> the total by the number of times played.
>>
>> The 2nd formulae worked correctly on the 1st golfer but didn't calculate
>> correctly on the 2nd (note the 2nd golfer missed the 1st week was the
>> only
>> difference)
>>
>> The 3rd formulae resulted in the last score being displayed.
>>
>> Thanks for your help
>>
>>
>>
>> "Biff" wrote:
>>
>> > Ooops!
>> >
>> > Make that:
>> >
>> > =AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-2),5)))
>> >
>> > Biff
>> >
>> > "Biff" <biffinpitt@comcast.net> wrote in message
>> > news:uR6ptLhqFHA.240@tk2msftngp13.phx.gbl...
>> > > Hi!
>> > >
>> > > Try this:
>> > >
>> > > Array entered:
>> > >
>> > > =AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535:T535)-3),5)))
>> > >
>> > > Biff
>> > >
>> > > "Larry L" <LarryL@discussions.microsoft.com> wrote in message
>> > > news:0785281D-FF1E-4620-AEF2-3C97E80ABBF9@microsoft.com...
>> > >> The formulae gave me a wrong calculation. The specific data I had is
>> > >> below:
>> > >>
>> > >> 45 42 45 46 40 41 44 44 44 41 38 39 43
>> > >>
>> > >> The last 5 scores totalled 205 = 41.00 average
>> > >>
>> > >> The formulae calculated 42.25
>> > >>
>> > >> The fomulae I used =
>> > >> {=AVERAGE(T535:INDEX(C535:T535,LARGE(IF(C535:T535<>"",COLUMN(C535)-1),5)))}
>> > >>
>> > >> The input data started in cell T535 and ended in cellT535 (18 weeks
>> > >> of
>> > >> data). What did I do incorrectly?
>> > >>
>> > >>
>> > >>
>> > >>
>> > >>
>> > >>
>> > >>
>> > >>
>> > >> "Biff" wrote:
>> > >>
>> > >>> Hi!
>> > >>>
>> > >>> That formula does work.
>> > >>>
>> > >>> This one is a little less complicated.....
>> > >>>
>> > >>> Assume your scores are in row 1 and you add a new score weekly (or
>> > >>> not,
>> > >>> if
>> > >>> you miss that week). Your golf season is 25 weeks long. Cell A1 is
>> > >>> for
>> > >>> the
>> > >>> players name. The weekly scores start in cell B1 and the last cell
>> > >>> for
>> > >>> the
>> > >>> 25th week is cell Z1.
>> > >>>
>> > >>> To get the average of the last 5 scores enter this formula using
>> > >>> the key
>> > >>> combo of CTRL,SHIFT,ENTER:
>> > >>>
>> > >>> =AVERAGE(Z1:INDEX(B1:Z1,LARGE(IF(B1:Z1<>"",COLUMN(B1:Z1)-1),5)))
>> > >>>
>> > >>> Biff
>> > >>>
>> > >>> "Larry L" <LarryL@discussions.microsoft.com> wrote in message
>> > >>> news:DA01C57F-57B9-4824-99A1-C2B333B0FE9B@microsoft.com...
>> > >>> >I need help with developing an average function for a golf league.
>> > >>> >We
>> > >>> >use
>> > >>> >an
>> > >>> > avarage of the last 5 scores posted to develop the person's
>> > >>> > handicap.
>> > >>> > Each
>> > >>> > week a new score is added to the data and a new average
>> > >>> > calculated
>> > >>> > with
>> > >>> > the
>> > >>> > 5th oldest score being dropped and newest score being included in
>> > >>> > the
>> > >>> > average. The data is kept on the worksheet in rows. Because a
>> > >>> > golfer
>> > >>> > may
>> > >>> > miss
>> > >>> > a week, some rows may have blanks that should not be considered.
>> > >>> >
>> > >>> > For example a person's scores may look like this for the season:
>> > >>> > 45, 47, blank, 44, 48, 46, blank, 49 (the 45 is the earliest
>> > >>> > score and
>> > >>> > 49
>> > >>> > is
>> > >>> > the latest score). I want the formulae to consider the 5 non zero
>> > >>> > scores
>> > >>> > from
>> > >>> > the oldest - 49 (right to left).
>> > >>> >
>> > >>> > I was given this formulae:
>> > >>> >
>> > >>> > =AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE((1:1>0)*(COLUMN(1:1)),{1,2,3,4,5})-1)))
>> > >>> > + control/shift & enter
>> > >>> >
>> > >>> > However I could not get it to work.
>> > >>> >
>> > >>> >
>> > >>> >
>> > >>>
>> > >>>
>> > >>>
>> > >
>> > >
>> >
>> >
>> >