Gfrktyl

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.

eg.

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?

Thanks.

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?

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

=AGGREGATE(14,6,$F$3:$F$23/($C$3:$C$23=15),ROW(A1))

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:

=MAX(SUM(H5:H8)+F3,SUM(H5:H10))

DEMO

If my understanding is correct, please try this formula:

=MAX(F24+SUM(LARGE((F28,F31,F34,F37,F40,F42),ROW($A$1:$A$4))),F28+F31+F34+F37+F40+F42)