Hi! When I looked at this, I thought I could do it quickly but found that's it's more tricky.
I have a list of seven scores recorded in columns B through H against each of say 50 people. In column I, I want to sum the top 4 scores out of the seven scores recorded by each person.
Sorting the fields horizontally is not an option because each score must remain under the event in which it was recorded. Transposing the data for manipulation is also a no go as there are other formulae at work on this worksheet. I think the answer must lie in a formula (or combined formulae). Lastly, I need the solution to be flexible, say I want to take the top 5 scores from 8 events for example.
If you have any ideas, I'd appreciate your comments, thanks!
I have a list of seven scores recorded in columns B through H against each of say 50 people. In column I, I want to sum the top 4 scores out of the seven scores recorded by each person.
Sorting the fields horizontally is not an option because each score must remain under the event in which it was recorded. Transposing the data for manipulation is also a no go as there are other formulae at work on this worksheet. I think the answer must lie in a formula (or combined formulae). Lastly, I need the solution to be flexible, say I want to take the top 5 scores from 8 events for example.
If you have any ideas, I'd appreciate your comments, thanks!