As a golf league we are trying something new. I need a formula that will drop the lowest and highest scores from a row of 10 scores then take the lowest 5 of the remaining 8 scores to get an average. I don't know if this can also happen but I would like to highlight the lowest five scores that I am using. I have included a sample workbook. Thank you for any help.
John
Array formula:committed with Ctrl-Shift-Enter, not just EnterFormula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
For the average of the 2nd through 6th lowest scores, try this regular formula in your workbook
Is that something you can work with?
Please Login or Register to view this content.
Just playing ...
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Both array functions
Regards, TMS
Seeing Ron's example made me realise that the first suggestion does NOT need to be array entered.
Formula:Please Login or Register to view this content.
Regards, TMS
=SUM(SMALL(C6:L6,{2,3,4,5,6}))/5 This formula seems to work great! Thank you for your help. Is there a way to highlight the 5 scores that the formula uses?
Can't test it now but you can probably use Conditional Formatting
Formula:Please Login or Register to view this content.
Regards, TMS
You're welcome.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Actually, I think Ron's AVERAGE formula is a better solution than my SUM.
The formula for conditional formatting only returns 4 scores. I need all 5. Is there something I might have done wrong?
No, the problem is that you have so many tied scores. The seventh lowest score happens to be the same as the sixth.
Can anything be done?
Maybe just use OR with the specific values ... but that will probably highlight all the 78s
Formula:Please Login or Register to view this content.
Regards, TMS
It did highlight all the 78's.
If you use this formula in conditional formatting.....
=COUNTIF($C6:C6,C6)<=SUM(IF(SMALL($C6:$L6,ROW(INDIRECT("2:6")))=C6,1))
You only get 5 numbers highlighted - if there are duplicates it takes the numbers from the left first
Audere est facere
Using your posted workbook...
• Select C6:L6
• Home.Conditional_formatting.Manage_Rules...New Rule
...Use a formula
...=(RANK(C6,$C6:$L6,TRUE)+COUNTIF($C6:C6,C6)-1)<6
...Click the format button and set the display
...Click: OK
Does that help?
Thank you everyone for your help. Spreadsheet is working exactly how I would like it to. Could not have did it with out your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks