I'm trying to figure out a way to make an excel spreadsheet that will help me 
out with my players golf scores.  I'd like a spreadsheet that I can enter 
scores in for each player daily, but will only give me an average for the 
lowest 4 out of their most recent 5 scores.  I'd like to have their older 
scores still visible on the spreadsheet, but not used in the calculation of 
their average.  In case I'm as confusing as I figure I am, here's an example:
 
Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 
scores on it, but only give me an average of the 4 lowest scores he's turned 
in out of his latest 5 scores....ignoring his first two scores.  

On Fri, 5 Mar 2010 22:02:01 -0800, houndawg
<houndawg@discussions.microsoft.com> wrote:

>I'm trying to figure out a way to make an excel spreadsheet that will help me 
>out with my players golf scores.  I'd like a spreadsheet that I can enter 
>scores in for each player daily, but will only give me an average for the 
>lowest 4 out of their most recent 5 scores.  I'd like to have their older 
>scores still visible on the spreadsheet, but not used in the calculation of 
>their average.  In case I'm as confusing as I figure I am, here's an example:
> 
>Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 
>scores on it, but only give me an average of the 4 lowest scores he's turned 
>in out of his latest 5 scores....ignoring his first two scores.  

Assuming that the scores are in column A starting in cell A1 and that
all scores are positive numbers.

Try this formula:

=AVERAGE(SMALL(OFFSET(A1,MAX((A1:A100>0)*ROW(A1:A100))-5,,5),{1,2,3,4}))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Change the 100 in both places to reflect the maximum number of scores
to be in column A. 

Hope this helps / Lars-�ke

If A1 contains players name and b1, c2, d1 etc contain scores.
I've made a very simple perhaps ugly solution.  
In F2 input    =SUM(B1:F1)-MAX(B1:F1)
That gives the total less the highest - I'm struggling to average that 
figure.  Logically it should be divided by 4 but it won't work.
To get round this in F3 input F2/4
Hide row 2
I only give this half baked solution as I'd love to know why my formula 
won't work with a /4 tagged on.

-- 
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"houndawg" wrote:

> I'm trying to figure out a way to make an excel spreadsheet that will help me 
> out with my players golf scores.  I'd like a spreadsheet that I can enter 
> scores in for each player daily, but will only give me an average for the 
> lowest 4 out of their most recent 5 scores.  I'd like to have their older 
> scores still visible on the spreadsheet, but not used in the calculation of 
> their average.  In case I'm as confusing as I figure I am, here's an example:
>  
> Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 
> scores on it, but only give me an average of the 4 lowest scores he's turned 
> in out of his latest 5 scores....ignoring his first two scores.  
> 

I meant to say that you can then drag the formulae across which will give you 
a continous rolling average as scores come in.
-- 
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"houndawg" wrote:

> I'm trying to figure out a way to make an excel spreadsheet that will help me 
> out with my players golf scores.  I'd like a spreadsheet that I can enter 
> scores in for each player daily, but will only give me an average for the 
> lowest 4 out of their most recent 5 scores.  I'd like to have their older 
> scores still visible on the spreadsheet, but not used in the calculation of 
> their average.  In case I'm as confusing as I figure I am, here's an example:
>  
> Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 7 
> scores on it, but only give me an average of the 4 lowest scores he's turned 
> in out of his latest 5 scores....ignoring his first two scores.  
> 

Are there/will there be any empty cells within the range? For example:

A2 = 77
A3
A4 = 82
A5 = 83
A6 = 80
A7
A8
A9 = 79

The average would include 77, 79, 80, 82.

What should happen if there aren't at least 5 scores?

-- 
Biff
Microsoft Excel MVP


"houndawg" <houndawg@discussions.microsoft.com> wrote in message 
news:2C6AE611-E51E-4211-94B7-B6C09068F668@microsoft.com...
> I'm trying to figure out a way to make an excel spreadsheet that will help 
> me
> out with my players golf scores.  I'd like a spreadsheet that I can enter
> scores in for each player daily, but will only give me an average for the
> lowest 4 out of their most recent 5 scores.  I'd like to have their older
> scores still visible on the spreadsheet, but not used in the calculation 
> of
> their average.  In case I'm as confusing as I figure I am, here's an 
> example:
>
> Say Billy turns in 6 practice scores, I'd like the spreadsheet to have all 
> 7
> scores on it, but only give me an average of the 4 lowest scores he's 
> turned
> in out of his latest 5 scores....ignoring his first two scores.
> 

Hello,

> ...
> What should happen if there aren't at least 5 scores?
> ...

Does not matter. Array-enter
=AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))

Regards,
Bernd

On 6 Mrz., 19:43, Bernd P <bplumh...@gmail.com> wrote:
> Hello,
>
> > ...
> > What should happen if there aren't at least 5 scores?
> > ...
>
> Does not matter. Array-enter
> =AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))
>
> Regards,
> Bernd

Not most recvent, though.

Regards,
Bernd

>>What should happen if there aren't at least 5 scores?
>Does not matter.

You must have magical powers. You're so good you can read the OP's mind?

>=AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))

That won't do what the OP asked for.

-- 
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff@gmail.com> wrote in message 
news:9d1c2b5d-8623-4398-9d91-3092169f011c@19g2000yqu.googlegroups.com...
> Hello,
>
>> ...
>> What should happen if there aren't at least 5 scores?
>> ...
>
> Does not matter. Array-enter
> =AVERAGE(SMALL(A1:A99,ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))
>
> Regards,
> Bernd 

Hello,

Two steps:

Array-enter into B1:B5:
=INDEX(A1:A99,LARGE(IF(A1:A99<>"",ROW(A1:A99)),ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),
5)))),1)

Then array-enter into C1:
=AVERAGE(SMALL(B1:INDEX(B1:B5,MIN(COUNTA(A1:A99),
5));ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))

But that's quite complex. Maybe better to take a UDF.

Regards,
Bernd

>But that's quite complex.

Not really, but you're making it more complex than need be. Why 2 formulas?

-- 
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff@gmail.com> wrote in message 
news:53799cc9-5258-4980-a305-aacb241a38d6@q21g2000yqm.googlegroups.com...
> Hello,
>
> Two steps:
>
> Array-enter into B1:B5:
> =INDEX(A1:A99,LARGE(IF(A1:A99<>"",ROW(A1:A99)),ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),
> 5)))),1)
>
> Then array-enter into C1:
> =AVERAGE(SMALL(B1:INDEX(B1:B5,MIN(COUNTA(A1:A99),
> 5));ROW(INDIRECT("1:"&MIN(COUNTA(A1:A99),4)))))
>
> But that's quite complex. Maybe better to take a UDF.
>
> Regards,
> Bernd 

Hello Biff,

> ...
> Not really, but you're making it more complex than need be. Why 2 formulas?
> ...

Try it with one only. INDEX is "cell-bound". If you try it, take care
of possible gaps (empty cells) and of the fact that there might be
less than 5 values, please.

With a VBA function you could just start from the last entry, step
back to the fifth-last-filled, calculate the result and stop.

Regards,
Bernd

>Try it with one only. INDEX is "cell-bound".
>If you try it, take care of possible gaps
>(empty cells) and of the fact that there
>might be less than 5 values, please.

Array entered...

=IF(COUNT(A2:A100),AVERAGE(SMALL(INDEX(A:A,LARGE((A2:A100<>"")*ROW(A2:A100),MIN(COUNT(A2:A100),5))):A100,ROW(INDIRECT("1:"&MIN(COUNT(A2:A100),4))))),"")

We can also use a non-volatile version but it would be a bit longer.

If the OP only wants the average if there are at least 5 scores...

Array entered...

=IF(COUNT(A2:A100)<5,"",AVERAGE(SMALL(INDEX(A:A,LARGE((A2:A100<>"")*ROW(A2:A100),5)):A100,{1,2,3,4})))

-- 
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff@gmail.com> wrote in message 
news:7ffab2a3-9184-46e2-912a-22cc463c95e9@q21g2000yqm.googlegroups.com...
> Hello Biff,
>
>> ...
>> Not really, but you're making it more complex than need be. Why 2 
>> formulas?
>> ...
>
> Try it with one only. INDEX is "cell-bound". If you try it, take care
> of possible gaps (empty cells) and of the fact that there might be
> less than 5 values, please.
>
> With a VBA function you could just start from the last entry, step
> back to the fifth-last-filled, calculate the result and stop.
>
> Regards,
> Bernd 

Hello Biff,

Nice one. Also quicker than mine.

I would not call it less complex, though.

The VBA solution which I had in mind:

Function Avg4Last5(r As Range) As Double
Dim i As Long, n As Long
Dim dSum As Double, dMax As Double
i = r.Count
n = 0
dSum = 0#
Do While i > 0 And n < 5
    If Not IsEmpty(r(i)) Then
        If r(i) > dMax Or n = 0 Then
            dMax = r(i)
        End If
        dSum = dSum + r(i)
        n = n + 1
    End If
    i = i - 1
Loop
Select Case n
Case 5
    Avg4Last5 = (dSum - dMax) / 4#
Case 0
    Avg4Last5 = CVErr(xlErrNum)
Case Else
    Avg4Last5 = dSum / n
End Select
End Function

With about 200 rows this VBA is quicker than both worksheet function
approaches. Not that I think golfers play that many rounds - I just
think this VBA function is easier to use and to understand.

Regards,
Bernd

>I just think this VBA function is easier to use and to understand.

And, because I'm not much of a programmer, I think formulas are easier to 
use and understand!

-- 
Biff
Microsoft Excel MVP


"Bernd P" <bplumhoff@gmail.com> wrote in message 
news:efdcf7db-f97b-4e60-aea9-767196dad596@t23g2000yqt.googlegroups.com...
> Hello Biff,
>
> Nice one. Also quicker than mine.
>
> I would not call it less complex, though.
>
> The VBA solution which I had in mind:
>
> Function Avg4Last5(r As Range) As Double
> Dim i As Long, n As Long
> Dim dSum As Double, dMax As Double
> i = r.Count
> n = 0
> dSum = 0#
> Do While i > 0 And n < 5
>    If Not IsEmpty(r(i)) Then
>        If r(i) > dMax Or n = 0 Then
>            dMax = r(i)
>        End If
>        dSum = dSum + r(i)
>        n = n + 1
>    End If
>    i = i - 1
> Loop
> Select Case n
> Case 5
>    Avg4Last5 = (dSum - dMax) / 4#
> Case 0
>    Avg4Last5 = CVErr(xlErrNum)
> Case Else
>    Avg4Last5 = dSum / n
> End Select
> End Function
>
> With about 200 rows this VBA is quicker than both worksheet function
> approaches. Not that I think golfers play that many rounds - I just
> think this VBA function is easier to use and to understand.
>
> Regards,
> Bernd