I'm in my final year of university and my final grade is calculated by (0.2*second year grade) + (0.8*final year's 90 best credits). I study one course worth 30 credits and 6 courses worth 15 credits each.

My final year's grade can be either made up of two methods; the first is 30-credit course+four best 15 credit courses OR all six 15-credit courses. Say I have an excel spreadsheet where my final grade for all of these courses will be entered.


A1 - 30 credit course grade

A2 - 15 credit course grade

A3 - 15 credit course grade


A7 - 15 credit course grade

Is there a formula that I can use to automatically decide which method should be used and which courses will be included in my final grade depending on the values within the cell?


Edit: added images (apologies for not doing so before, I didn't know it was permissible)

In this link, my spreadsheet is shown. Right now, the yellow cells in column F show my grades for each course I will be doing this year. In the total section, I simply did the average of each course, thinking they all count to my final grade. I now know this isn't true. I am thinking that some form of a MAX function is to be used?

  • Please help us help you by clicking on edit and adding to your original post; Comments are for folks helping to ask, your answers should build up the original question and show the formulas you have tried so far. Taking a screenshot of what you have done so far and uploading it to a website like imgur.com then adding the weblink to the screenshot to your original post helps greatly. – K7AAY Nov 20 '18 at 19:01

If your class scores are entered as numbers (1-100 example A+ = 100) with the large class in A1 and the small classes in A2 through A7 then this formula will tell you which choice to make for your final years best 90 credits.

=IF((A1+LARGE(A2:A7,1)+LARGE(A2:A7,2)+LARGE(A2:A7,3)+LARGE(A2:A7,4))>SUM(A2:A7),"use large class and four smalls","use six small classes")


This solution involves a helper column to list out the course mark with a weight of 15 in ascending order. once you have the list, you take the max of the sum of the first 6 course or the first 4 course plus the values of the 30 course.

in my sample data, in H5 I placed the following formula and copied down


It a regular formula that performs array like operations. Basically it generating a sorted list of values where the corresponding weight is 15 only. Row(A1) acts as a counter. so the first time its 1 and it becomes 2 on the next row and so on. 1,2,3 etc tells aggregate which number in the list to return.

In H12 use the following formula to determine that maximum result of either top 4 15 weight course and a 30 weight course, or 6 15 weight courses:



enter image description here


If my understanding is correct, please try this formula:


enter image description here

  • emmmm...Please check my edited answer, I change the formula. – Lee Nov 21 '18 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.