How can I get Excel to choose the highest grade credits on my course and display my final grade?
up vote
0
down vote
favorite
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?
microsoft-excel worksheet-function microsoft-excel-2010
add a comment |
up vote
0
down vote
favorite
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?
microsoft-excel worksheet-function microsoft-excel-2010
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 at 19:01
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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?
microsoft-excel worksheet-function microsoft-excel-2010
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?
microsoft-excel worksheet-function microsoft-excel-2010
microsoft-excel worksheet-function microsoft-excel-2010
edited Nov 20 at 19:13
asked Nov 20 at 18:52
Ruwaz
11
11
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 at 19:01
add a comment |
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 at 19:01
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 at 19:01
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 at 19:01
add a comment |
3 Answers
3
active
oldest
votes
up vote
0
down vote
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")
add a comment |
up vote
0
down vote
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
add a comment |
up vote
0
down vote
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)
emmmm...Please check my edited answer, I change the formula.
– Lee
Nov 21 at 6:31
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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")
add a comment |
up vote
0
down vote
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")
add a comment |
up vote
0
down vote
up vote
0
down vote
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")
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")
edited Nov 20 at 19:14
Albin
2,2951129
2,2951129
answered Nov 20 at 19:09
Brian
212
212
add a comment |
add a comment |
up vote
0
down vote
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
add a comment |
up vote
0
down vote
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
add a comment |
up vote
0
down vote
up vote
0
down vote
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
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
answered Nov 21 at 4:21
Forward Ed
437213
437213
add a comment |
add a comment |
up vote
0
down vote
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)
emmmm...Please check my edited answer, I change the formula.
– Lee
Nov 21 at 6:31
add a comment |
up vote
0
down vote
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)
emmmm...Please check my edited answer, I change the formula.
– Lee
Nov 21 at 6:31
add a comment |
up vote
0
down vote
up vote
0
down vote
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)
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)
edited Nov 21 at 6:30
answered Nov 21 at 2:05
Lee
79917
79917
emmmm...Please check my edited answer, I change the formula.
– Lee
Nov 21 at 6:31
add a comment |
emmmm...Please check my edited answer, I change the formula.
– Lee
Nov 21 at 6:31
emmmm...Please check my edited answer, I change the formula.
– Lee
Nov 21 at 6:31
emmmm...Please check my edited answer, I change the formula.
– Lee
Nov 21 at 6:31
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1377065%2fhow-can-i-get-excel-to-choose-the-highest-grade-credits-on-my-course-and-display%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 at 19:01