Please help me with an Excel formula.
July 4, 2011 4:10 AM Subscribe
How can I use Excel to automatically calculate the average of the best two of three results?
Please help me with an Excel formula.
I'm trying to calculate my students' final grades. This semester they had three progress checks, each worth 30 marks. I have these marks in three columns (A, B, C) in an Excel worksheet. In the fourth column, D, I need to calculate the average of their best two progress checks as a percentage. For example if a student had 28, 26 and 23, we would drop the 23 and take the average of 28 and 26 which gives a percentage of 90%. Until now I have always done this manually.
Is there a way to get Excel to automatically select the best two progress checks and skip the worst result? What if a student got, for example, 27 and 24 and 24. Will the fact that the two worst results are equal confuse this formula (i.e. Excel doesn't know which one to drop)?
Many thanks for your help!
P.S. Happy treason day to all the Mefites over in the rebel colonies. :o)
Please help me with an Excel formula.
I'm trying to calculate my students' final grades. This semester they had three progress checks, each worth 30 marks. I have these marks in three columns (A, B, C) in an Excel worksheet. In the fourth column, D, I need to calculate the average of their best two progress checks as a percentage. For example if a student had 28, 26 and 23, we would drop the 23 and take the average of 28 and 26 which gives a percentage of 90%. Until now I have always done this manually.
Is there a way to get Excel to automatically select the best two progress checks and skip the worst result? What if a student got, for example, 27 and 24 and 24. Will the fact that the two worst results are equal confuse this formula (i.e. Excel doesn't know which one to drop)?
Many thanks for your help!
P.S. Happy treason day to all the Mefites over in the rebel colonies. :o)
Response by poster: Thanks for your answer. How do I get it to automatically choose the minimum?
posted by MighstAllCruckingFighty at 4:17 AM on July 4, 2011
posted by MighstAllCruckingFighty at 4:17 AM on July 4, 2011
Best answer: =MIN(range)
so an example would be =(SUM(A1:D1)-MIN(A1:D1))/2
posted by grahamwell at 4:19 AM on July 4, 2011
so an example would be =(SUM(A1:D1)-MIN(A1:D1))/2
posted by grahamwell at 4:19 AM on July 4, 2011
Response by poster: Thanks! If there are two equal worst results, will this confuse the formula? If a student got 27, 24 and 24, would the formula delete both of the 24s or just one?
posted by MighstAllCruckingFighty at 4:23 AM on July 4, 2011
posted by MighstAllCruckingFighty at 4:23 AM on July 4, 2011
Best answer: It'll subtract the minimum. Not the sum of the two worst equal marks. You can of course test this yourself on a dummy worksheet.
posted by singingfish at 4:29 AM on July 4, 2011
posted by singingfish at 4:29 AM on July 4, 2011
Best answer: No, it will be fine, it will only pick one.
posted by grahamwell at 4:29 AM on July 4, 2011
posted by grahamwell at 4:29 AM on July 4, 2011
Response by poster: Just tested it, works a treat! Thanks a lot everyone! Something that is no doubt simple to a lot of people is about to save me a lot of time.
posted by MighstAllCruckingFighty at 4:30 AM on July 4, 2011
posted by MighstAllCruckingFighty at 4:30 AM on July 4, 2011
Response by poster: Thanks for the tip! Nothing is obvious to me when it comes to Excel - I only really use the functions a couple of times a year for grades. The only thing I use it for every day is logging some personal finance data.
Personally I think Excel is witchcraft.
posted by MighstAllCruckingFighty at 5:19 PM on July 4, 2011
Personally I think Excel is witchcraft.
posted by MighstAllCruckingFighty at 5:19 PM on July 4, 2011
This thread is closed to new comments.
posted by about_time at 4:16 AM on July 4, 2011