Please help!!!!!

[Tom Wells]

I am an Instructor and I have all my marks on excel 2002. They are divided
into Lab quiz marks, Lecture quiz marks, midterm, final, etc. I then attach
various weights to the marks to calculate the final mark. My difficulty is
that I have told the students that they can discard their lowest mark in the
lab quizes or lecture quizes (NOT BOTH) before I calculate their final mark.
If I combine all their marks it is easy I can use the min function and
divide by count- 1. But I need to keep lab marks separate from lecture
marks. How do I subtract the lowest mark from the totals. The lowest mark
could obviously be in either the lab or the lecture. Please help, since I
need to submit the marks very VERY soon. I could do it manually, but, I
know there must be a way I can do it by calculations. Thanks. Tom

 

[Peo Sjoblom]

One possible way

=SUM(A2:G7,-MIN(A2:B7))/(COUNT(A2:G7)-1)

where A2:G7 is the whole table, A2:B7 would be the 2 ranges with the Lab and
Lecture
marks

I added the divided by count if you need an average, if that's already taken
care of or
if you don't need it remove the last part and use

=SUM(A2:G7,-MIN(A2:B7))

 

[Tom Wells]

Thanks for your prompt reply. I am not certain that your formula would
calculate exactly what I want. I need to show a separate average for the
lab and lecture marks (lowest mark removed from whichever) as well as a
final mark.

Just so we are clear I have all the students in separate rows and there
marks are in columns. So, for example, student 1 would be in row 4. Quiz
results would be in columns D-R.

Thanks again. Tom

 

[J.E. McGimpsey]

one way:

Assume Lecture/Quiz marks in columns B:R, Lab Marks in S:Z, with Lab
quizes in V:Z:

Lecture/Quiz:

=SUM(B4:R4, -MIN(D4:R4) * (MIN(D4:R4)<MIN(S4:Z4))) /
(COUNT(B4:R4) - (MIN(D4:R4)<MIN(S4:Z4)))


Lab/Quiz:

=SUM(S4:Z4, -MIN(V4:Z4) * (MIN(V4:Z4)<MIN(D4:R4))) /
(COUNT(S4:Z4) - (MIN(V4:Z4)<MIN(D4:R4)))

OTOH, this begs one question: What if the lowest quiz grade was for
a lab quiz, but throwing out the lecture quiz would help the lecture
grade more than throwing out the lab quiz would help the lab grade
(i.e., High marks on all labs/lab quizzes but one, with marginal
marks in Lecture/quizzes)? Do the students get to choose which grade
to throw out?

 

[Peo Sjoblom]

If you only want to remove the lowest value in D4:R4

=AVERAGE(LARGE(D4:R4,ROW(INDIRECT("1:"&COUNT(D4:R4)-1))))

entered with ctrl + shift & enter

if the lab and lectures are in column D and E and marks in other columns can
be lower
than the lab and the lecture marks

=SUM(D4:R4,-MIN(D4:E4))/(COUNT(D4:R4)-1)

 

[Tom Wells]

Thanks for the help. I really appreciate it. Tom