Display values from an moving array and display

[brayboyce]

hello all,

I have been working on a handicap golf sheet, I am sure like countless other people, based on the Search results from in this forum. I had hit a couple of Snags that I have been Scratching my head on and I need a little help.

The following array is working correctly, it is taking creating an average from the last 7 scores kicking out the largest value from the 7 numbers and providing me an average.
=IFERROR(IF(COUNT(E5:AH5<7,AVERAGE(E5:AH5),AVERAGE(SMALL(IF((E5:AH5>0)*COLUMN(E5:AH5)>=LARGE((E5:AH5>0)*COLUMN(E5:AH5),MIN(10,COUNTIF(E5:AH5,">0"))),E5:AH5,""),{1,2,3,4,5,6,7}))),"")-AI5*(113/AJ5)

What I would like to do is display the numbers that are being used to create the average and have them displayed in Columns labeled 1 thru 7, also I would like to display the number excluded from the average calculation. this will allow everyone to cross reference and validate that their handicap is accurate, 0 (Zero) & blanks should be ignored.

This was the formula I was using when the sheet was static.
=IF(COUNT(INDEX($E5:$AH5,LARGE(IF($E5:$AH5<>"",COLUMN($E5:$AH5)COLUMN($E5)+1),MIN(10,COUNTA($E5:$AH5)))):$AH5)>=AO$4,SMALL(INDEX($E5:$AH5,LARGE(IF($E5:$AH5<>"",COLUMN($E5:$AH5)-COLUMN($E5)+1),MIN(10,COUNTA($E5:$AH5)))):$AH5,AO$4),"")

which worked until I got a 0(zero) for a Score then it would tank the handiCap.

Issues looking to resolve
1. Create Rolling average that start working two scores or more are entered, right now it only works when 5 scores are entered. needs to adjsut when new scores are entered
2. display the numbers being used to create the Average
3. Display the high score excluded from the average.
scoresheetimage.JPG



Thank you for the help

-BB

[Glenn Kennedy]

Hi.

can I suggest an alternative approach, altogether. see sheet.

Firstly, just leave non-playing rounds blank, not with a zero.

Secondly, the problem i had was accommodating the 3rd player, who scored 36 in all games. Hs Max value was equal to all values... that gave me a headache. So I modified the formula showing the maximum value to be non zero, only when the number did not occur in every round played.

So (array formula) t calculate an excluded score:
=IF((SUM(IFERROR(1/COUNTIF(E5:N5,E5:N5),0))=1),"",MAX(E5:N5))

thirdly, a simpler array formula for the rest:
=IFERROR(SMALL(IF($E5:$N5<>$S11,IF($E5:$N5<>"",$E5:$N5)),T$4),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

This works fine if a player has 2 scores equal to the max (I5 and J5) AND when all games returned the same score (Player 3)

[Glenn Kennedy]

OK. Scrap that! It doesn't work. If all values, but 1, are 36 and one is < 36... it falls over.

An alternative:

1. Kill zeros, use blanks.

2. excluded max value (array)
=MAX(E5:N5+COLUMN(E5:N5)/10^10)

3. Low scores (array):
IFERROR(SMALL(IF($E5:$N5<>$S11,IF($E5:$N5<>"",$E5:$N5)),T$4),"")

I think it's OK now!!

[brayboyce]

Glenn, Thank you for looking at this, i am open to change, after seeing your results and looking at my the information that I posted. it was Clear that I failed to properly communicate that information "AKA" the Scores are greater than 10 weeks, Which is when my issues / frustation started. I am using the moving average array only because the team leads will be enter more scores for the next 16 weeks and as the scores get entered the Average should only be based the newest 7 scores entered excluding the largest " one time" Value.

1. my apologies for not providing a clear message
2. I need the 7 most recent scores while excluding the one off high Score
3. Need to display the number being used to calculate the average "visual validation" for the players
4. this needs to be a rolling 16 or more weeks moving average

[JeteMc]

It might help us to understand if you could manually fill in the expected values for cells T5:Z7. We might then be able to write formulas/code that would replicate those values.
Consideration: Do the scores really need to be in ascending order for this to work/be understood by participants?
Let us know if you have any questions.