Prev: Entering Values and Updating Next Empty Cell in a Range
Next: Conditional Formatting Query
From: houndawg on 6 Mar 2010 01:02
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.

From: Lars-�ke Aspelin on 6 Mar 2010 02:17
On Fri, 5 Mar 2010 22:02:01 -0800, houndawg
<houndawg(a)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
From: Russell Dawson on 6 Mar 2010 03:12
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.
>
From: Russell Dawson on 6 Mar 2010 03:14
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.
>
From: T. Valko on 6 Mar 2010 13:04
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(a)discussions.microsoft.com> wrote in message
news:2C6AE611-E51E-4211-94B7-B6C09068F668(a)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.
>


 |  Next  |  Last
Pages: 1 2 3
Prev: Entering Values and Updating Next Empty Cell in a Range
Next: Conditional Formatting Query