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



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

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

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)
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)

b) the corresponding total points available (from column B)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question