Excel formula to pick top 3 numbers of 3-6 possible 3

[Scott02]

Hello all,

I have rows of data consisting of between 3 to 6 columns of numbers. The goal is to get the 3 highest numbers of those in each row. For better clarification, the data is a series of test scores, where the top 3 are kept and averaged and the rest (if any-there can be up to 6 total) are discarded. I've tried using nested IF and MAX statements, but can't seem to get the right combination. Can anyone help with a formula for this? I prefer not to use VBA, as I am not experienced with it and cannot provide future support if needed for the end user. Thanks in advance for any suggestions.

Scott

 

[onedtent]

=large(cell_range,1)
=large(cell_range,2)
=large(cell_range,3)

 

[Scott02]

Thanks for the tip. Do you know of another trick that would work if 2 of the top 3 were the same number? In several cases, people scored 90% more than once.

Thank you!

 

[DaleWatson123321]

Scott,

onedtent's suggestion of the LARGE function is ABSOLUTELY correct. Using the LARGE function also takes care of situations where there are duplicate scores amongst the top three scores.

If you're having difficulty in setting up your model, I can email you an example file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Scott02]

Thanks for the help from both of you! I appreciate your quick respones. Once I was pointed in the right direction (and anchored my ranges) it works perfectly! Thanks again!