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

[AdamMK]

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.

AdamMK

 

[AdamMK]

Thanks. It doesn't make sense because I'm trying to drop credit hours and not letter grades. I'll try to work with what you've given me to adapt the code to be accurate.

The problem is that courses can be worth 3credits or 6credits. If I got an A in a 6credit course I would effectively have 2 A's to calculate with. If I wanted to drop 30 credit hours according to my formula, then I would be dropping the 30/3=10 lowest letter grades in my calculation.

I think I need to add an extra field or go about calculating things a bit differently for it to work properly.

 

[Jonmo1]

If I wanted to drop 30 credit hours according to my formula, then I would be dropping the 30/3=10 lowest letter grades in my calculation.

In that case, just take the reference to H2 in the formula, and devide it by 3..
change

=AVERAGE(INDIRECT("I2:I" & COUNTA($C:$C)-$H2))

to

=AVERAGE(INDIRECT("I2:I" & COUNTA($C:$C)-($H2/3)))

 

[Jonmo1]

However, deviding H2 by 3 may result in a fraction #. Not a whole #, and that would mess thigs up.

You would need to ROUND the result of H2/3..

If you want it rounded up

=AVERAGE(INDIRECT("I2:I" & COUNTA($C:$C)-ROUNDUP($H2/3),0)))

If you want it rounded down

=AVERAGE(INDIRECT("I2:I" & COUNTA($C:$C)-INT($H2/3))))