I need to exclude a value from a list based on a corresponding value.

[SSLeBlanc]

Sorry, I may have tried to be too descriptive in the subject line.

I am trying to set up a rollbook that will calculate grades with les
effort than last semester. Do you all remember the "drop the lowes
test score" in grading?

I found an old post using SMALL, but it was eliminating the lowes
value from the list. What I need to do is a little different.

For each exam I have 2 columns. The first is a numerical value (poin
value), and the second is a percent (score - test point value divide
by available points for the test, stored in another cell - and easil
calculated). Example below:

A1 - test 1 availabe points (ie: 78)
A2 - test 1 points (ie: 70)
B2 - test 1 score (ie: 90%)
C1 - test 2 available points (ie: 96)
C2 - test 2 points (ie: 68)
D2 - test 2 score (ie 71%)
E1 - test 3 available points (ie: 70)
E2 - test 3 points (ie: 67)
F2 - test 3 score (ie: 96%)

I need to total the points for the top two test scores - in the exampl
above I need to drop the "test 2" because it is the lowest score/grade
though not the lowest point value.

I need two values from this - the total available points for the tw
tests not dropped, and the total points. Using the example above
need:

a) (total available points) A1 + E1
b) (total points) A2 + E2

I hope I've explained this well enough. I have not done a lot o
involved formulas in spreadsheets and I've just been struggling wit
the best way to do this for a while.

(I am flexible with the number of cells needed/changes to th
spreadsheet at this point - the data is not input yet).

Thank you for any help!! (I thank you and my husband thanks you! - n
more staying up until 3 am to get grades in at the end of a semester!

 

[Frank Kabel]

Hi
try the following:
1. change your spreadsheet layou like the following (this makes the
formulas much easiere and you also may use pivot tables, filtering,
sorting, etc)
A B C D
1 Testname avail.p. points score
2 test1 78 70 90%
3 test2 96 68 71%
.....

2. Now to the claculation:
a) top 2 points (from column C)
=MAX(C1:C20)+LARGE(C1:C20,2)

b) the corresponding total points available (from column B)
=INDEX(B1:B20,MATCH(MAX(C1:C20),C1:C20,0))+INDEX(B1:B20,MATCH(LARGE(C1:
C20,2),C1:C20,0))

 

[SSLeBlanc]

THANK YOU!! I'll give it a try