i am making a new scoring speadsheet for my sport. we meet each month and a score is posted. each person can either turn up or not. we take the 6 best scores (lowest) and average the for trophy scores for the year. using all sorts of vlookup tables and so on to bring totals from the monthly meets to the yearly totals page i keep a score master list on one page. i have it set that if someone does not participate one month they get a '0' score.
i need a way to average the 6 lowest scores from a selection of 10 scores (but discount any '0' scores)
on my other worksheet i use for another event i run i use the follwing formula
=((max(xy:xy;1))+(max(xy:xy;2))+(max(xy:xy;3))+(max(xy:xy;4))+(max(xy:xy;5))+(max(xy:xy;6)))/6
but if i sum max for min it returns the '0' scores.
if i cant find a easy way to do it with a funky formula i will just make it return a 999 instead of 0 for no score.
Edit: Tag topic as solved -- MrProgrammer 2019-06-26 18:08 |