# Data sorting, Min/Max, selective calculation function help.

##### New Member
Hi. I'm using Excel 2003 with WinXP SP2.

I'm trying to make a spreadsheet to calculate my GPA for my studies. I can get everything to work fine except I want to be able to calculate an adjusted GPA based on a certain value of classes to drop.

The way it would work ideally is that I would be able to search/sort a data set and if it contained "X" number of entries then the "Y" number of lowest marks would be omitted from the AGPA calculation.

I'm having trouble designing a function/formula or scheme to sort the data range, and identify the lowest "Y" number of entries and recalculate the GPA without using them.

Any general help would be appreciated. I will post my spreadsheet when I get home today if it would help to have a look at it.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Jonmo1

##### MrExcel MVP
Try this out for starters
personal.xls
ABCD
1ScoreX#toemit(25%roundedup)Average
2100382.5000
375
475
536
682
773
890
955
1067
Sheet1

B2 formula is

Code:
``=ROUNDUP(COUNTA(A\$2:A\$100)*0.25,0)``

C2 formula is
Code:
``=AVERAGE(IF(A\$2:A\$100>=LARGE(A\$2:A\$100,COUNTA(A\$2:A\$100)-B\$2),A\$2:A\$100))``

C2 formula is an array, confirmed with CTRL + SHIFT + ENTER
after pasting formula, highlight cell and press F2. Then Press
CTRL + SHIFT + ENTER

##### New Member
Hi jonmo1. Thanks for the reply. I've tried to adapt that code to help me out but I'm still working on it. Below is my spreadsheet presently.

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=11><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - AGPA Chart.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=11><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb755237><INPUT onclick='window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);' type=button value="Copy Formula" name=btCb873980></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=11><TABLE border=0><TBODY><TR><FORM name=formFb078704><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name=sltNb935705><OPTION value='=IF(C2="A+",4.5,IF(C2="A",4,IF(C2="B+",3.5,IF(C2="B",3,IF(C2="C+",2.5,IF(C2="C",2,IF(C2="D",1,0)))))))' selected>D2<OPTION value="=IF(B2=3, D2*1, IF(B2=6,D2*2, 0))">E2<OPTION value==G2/3>F2<OPTION value==SUM(B2:B50)>G2<OPTION value="=IF(G2<90,0,IF(AND(G2>=90,G2<=95),15,IF(AND(G2>=96,G2<=101),18,IF(AND(G2>=102,G2<=107),121,IF(AND(G2>=108,G2<=113),24,IF(AND(G2>=114,G2<=119),27,IF(G2>=120,30,0)))))))">H2<OPTION value==(SUM(E2:E50))/F2>I2<OPTION value='=IF(C3="A+",4.5,IF(C3="A",4,IF(C3="B+",3.5,IF(C3="B",3,IF(C3="C+",2.5,IF(C3="C",2,IF(C3="D",1,0)))))))'>D3<OPTION value="=IF(B3=3, D3*1, IF(B3=6,D3*2, 0))">E3<OPTION value='=IF(C4="A+",4.5,IF(C4="A",4,IF(C4="B+",3.5,IF(C4="B",3,IF(C4="C+",2.5,IF(C4="C",2,IF(C4="D",1,0)))))))'>D4<OPTION value="=IF(B4=3, D4*1, IF(B4=6,D4*2, 0))">E4<OPTION value='=IF(C5="A+",4.5,IF(C5="A",4,IF(C5="B+",3.5,IF(C5="B",3,IF(C5="C+",2.5,IF(C5="C",2,IF(C5="D",1,0)))))))'>D5<OPTION value="=IF(B5=3, D5*1, IF(B5=6,D5*2, 0))">E5<OPTION value='=IF(C6="A+",4.5,IF(C6="A",4,IF(C6="B+",3.5,IF(C6="B",3,IF(C6="C+",2.5,IF(C6="C",2,IF(C6="D",1,0)))))))'>D6<OPTION value="=IF(B6=3, D6*1, IF(B6=6,D6*2, 0))">E6<OPTION value='=IF(C7="A+",4.5,IF(C7="A",4,IF(C7="B+",3.5,IF(C7="B",3,IF(C7="C+",2.5,IF(C7="C",2,IF(C7="D",1,0)))))))'>D7<OPTION value="=IF(B7=3, D7*1, IF(B7=6,D7*2, 0))">E7<OPTION value='=IF(C8="A+",4.5,IF(C8="A",4,IF(C8="B+",3.5,IF(C8="B",3,IF(C8="C+",2.5,IF(C8="C",2,IF(C8="D",1,0)))))))'>D8<OPTION value="=IF(B8=3, D8*1, IF(B8=6,D8*2, 0))">E8<OPTION value='=IF(C9="A+",4.5,IF(C9="A",4,IF(C9="B+",3.5,IF(C9="B",3,IF(C9="C+",2.5,IF(C9="C",2,IF(C9="D",1,0)))))))'>D9<OPTION value="=IF(B9=3, D9*1, IF(B9=6,D9*2, 0))">E9<OPTION value='=IF(C10="A+",4.5,IF(C10="A",4,IF(C10="B+",3.5,IF(C10="B",3,IF(C10="C+",2.5,IF(C10="C",2,IF(C10="D",1,0)))))))'>D10<OPTION value="=IF(B10=3, D10*1, IF(B10=6,D10*2, 0))">E10<OPTION value='=IF(C11="A+",4.5,IF(C11="A",4,IF(C11="B+",3.5,IF(C11="B",3,IF(C11="C+",2.5,IF(C11="C",2,IF(C11="D",1,0)))))))'>D11<OPTION value="=IF(B11=3, D11*1, IF(B11=6,D11*2, 0))">E11<OPTION value='=IF(C12="A+",4.5,IF(C12="A",4,IF(C12="B+",3.5,IF(C12="B",3,IF(C12="C+",2.5,IF(C12="C",2,IF(C12="D",1,0)))))))'>D12<OPTION value="=IF(B12=3, D12*1, IF(B12=6,D12*2, 0))">E12<OPTION value='=IF(C13="A+",4.5,IF(C13="A",4,IF(C13="B+",3.5,IF(C13="B",3,IF(C13="C+",2.5,IF(C13="C",2,IF(C13="D",1,0)))))))'>D13<OPTION value="=IF(B13=3, D13*1, IF(B13=6,D13*2, 0))">E13<OPTION value='=IF(C14="A+",4.5,IF(C14="A",4,IF(C14="B+",3.5,IF(C14="B",3,IF(C14="C+",2.5,IF(C14="C",2,IF(C14="D",1,0)))))))'>D14<OPTION value="=IF(B14=3, D14*1, IF(B14=6,D14*2, 0))">E14<OPTION value='=IF(C15="A+",4.5,IF(C15="A",4,IF(C15="B+",3.5,IF(C15="B",3,IF(C15="C+",2.5,IF(C15="C",2,IF(C15="D",1,0)))))))'>D15<OPTION value="=IF(B15=3, D15*1, IF(B15=6,D15*2, 0))">E15<OPTION value='=IF(C16="A+",4.5,IF(C16="A",4,IF(C16="B+",3.5,IF(C16="B",3,IF(C16="C+",2.5,IF(C16="C",2,IF(C16="D",1,0)))))))'>D16<OPTION value="=IF(B16=3, D16*1, IF(B16=6,D16*2, 0))">E16<OPTION value='=IF(C17="A+",4.5,IF(C17="A",4,IF(C17="B+",3.5,IF(C17="B",3,IF(C17="C+",2.5,IF(C17="C",2,IF(C17="D",1,0)))))))'>D17<OPTION value="=IF(B17=3, D17*1, IF(B17=6,D17*2, 0))">E17<OPTION value='=IF(C18="A+",4.5,IF(C18="A",4,IF(C18="B+",3.5,IF(C18="B",3,IF(C18="C+",2.5,IF(C18="C",2,IF(C18="D",1,0)))))))'>D18<OPTION value="=IF(B18=3, D18*1, IF(B18=6,D18*2, 0))">E18<OPTION value='=IF(C19="A+",4.5,IF(C19="A",4,IF(C19="B+",3.5,IF(C19="B",3,IF(C19="C+",2.5,IF(C19="C",2,IF(C19="D",1,0)))))))'>D19<OPTION value="=IF(B19=3, D19*1, IF(B19=6,D19*2, 0))">E19<OPTION value='=IF(C20="A+",4.5,IF(C20="A",4,IF(C20="B+",3.5,IF(C20="B",3,IF(C20="C+",2.5,IF(C20="C",2,IF(C20="D",1,0)))))))'>D20<OPTION value="=IF(B20=3, D20*1, IF(B20=6,D20*2, 0))">E20<OPTION value='=IF(C21="A+",4.5,IF(C21="A",4,IF(C21="B+",3.5,IF(C21="B",3,IF(C21="C+",2.5,IF(C21="C",2,IF(C21="D",1,0)))))))'>D21<OPTION value="=IF(B21=3, D21*1, IF(B21=6,D21*2, 0))">E21</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value='=IF(C2="A+",4.5,IF(C2="A",4,IF(C2="B+",3.5,IF(C2="B",3,IF(C2="C+",2.5,IF(C2="C",2,IF(C2="D",1,0)))))))' name=txbFb426622></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%">

##### New Member
Courses are arbitrary names.
Weight is either 3 or 6, with 3 being a half year class and 6 being a full year class.
GPA Score and GPA Score Worth depend on the letter grade obtained and whether it was a half year class or a full year class.

My # of credits to drop cell indicates how many of the bottom credits I want to get rid of in my adjusted GPA (AGPA) calculation. I need to incorporate that cell into my AGPA formula since that number will vary with how many credits have been completed.

Example: If I have 120+ credits completed I am eligible to drop the 30 worst credit hour marks recieved in my AGPA calculation.

Is it feasible to use my existing excel sheet or do I need to make some major adjustments?

#### Jonmo1

##### MrExcel MVP
You're wanting to average Column E, and drop X (based on H2) # of the Worst Scores ?? And G2 is the Total # of Scores?

Formula for J2 should be
Code:
``=AVERAGE(IF(E\$2:E\$1000>=LARGE(E\$2:E\$1000,G\$2-H\$2),E\$2:E\$1000))``
IMPORTANT
after pasting formula, highlight cell and press F2. Then Press
CTRL + SHIFT + ENTER

##### New Member
Hi jonmo1. I did what you said but I'm getting a #num error. I'm not sure if I need to adjust the code or not. I appreciate the help but I'm only a basic user of excel so I may be overlooking something simple.
AGPA Chart.xls
ABCDEFGHIJ
26a4836108242.7222#NUM!
36b36
46c24
56d12
66a48
76b36
86c24
96a48
106c24
116c24
126b36
136c24
146a48
156c24
166a48
176c24
186b36
196c24
Sheet1

##### New Member
J2 Code is:

=AVERAGE(IF(E\$2:E\$1000>=LARGE(E\$2:E\$1000,G\$2-H\$2),E\$2:E\$1000))

#### Jonmo1

##### MrExcel MVP
ok, I don't understand how you are determining how many (low) scores to drop from the average...

##### New Member
How it works is that my classes are either 3 credit hours or 6 credit hours. Calculations are made using 3 credit hours (1 half course worths). If I get an A in a 3 credit hour course, and a B in a 6 credit hour course, for calculation purposes I will have 1 A + 2 B's.

Now once I hit a certain amount of credit hours, I am able to drop the LOWEST 'X' number of letter grades in my AGPA.

So if I had: A, A, B, B, C C, D, A, C, C, B

Assuming I could drop my 3 lowest letter grades I would drop 1 D + 2 C's.

So my problem is sorting my letter grade list, and then telling excel how many of the LOWEST letter grades to drop to recalculate my AGPA, effectively only counting the remaining letter grades and averaging accordingly.

#### Jonmo1

##### MrExcel MVP
This still doesn't make sense. I went ahead and filled down columns B and C (just copied rows 2-20). According to your formulas, you have 50 grades, and it's going to drop 30 of them??

But this should do it. going off of H2 as the # to drop. And Averaging column D, Not E.

I inserted a column (I) and put this formula in I2 - filled down
Code:
``=LARGE(D\$2:D\$50,ROW()-1)``

And this formula is now in K2
Code:
``=AVERAGE(INDIRECT("I2:I" & COUNTA(\$C:\$C)-\$H2))``
personal.xls
ABCDEFGHIJK
26a48982943042.8367353.947368
36b364
46c244
56a484
66b364
76c244
86d124
96a484
106b364
Sheet1

Replies
1
Views
246
Replies
1
Views
392
Replies
0
Views
268
Replies
7
Views
1K
Replies
0
Views
246

1,190,693
Messages
5,982,327
Members
439,773
Latest member
tyruschen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back