problem with combining functions

[marcas91]

I'm using regular Excel formula in the cells (not scripting, mainly
because I don't know how). I have a problem with trying to combine
offset() and small()

my actual code is:

=OFFSET(small(E4:E18,1),0,-2)

and it obviously doesn't work.

Is there any way to use those two features together?

If it helps I'm trying to make a gradebook capable of weighting
categories (e.g. 50% of avg for quiz, etc.) and dropping lowest
quizzes. I have all the code working except this one combination of
finding the lowest grade using small() and returning its corresponding
number using offset().

Thanks in advance for any help.

Marcas Burnett
marcas91[at]gmail[dot]com

 

[Guest]

marcas91,
I tried this and had success.
E4:E18 grades
Line 1 E20 "=(MIN(E4:E18))"
Line 2 E19"=SUM((E4:E18),-E20)/Count(E4:E18)-1)"
How this works:
Line 1 "=(MIN(E4:E18)) finds the lowest value
Line 2 "=SUM((E4:E18) this part totals column,-lowest value
divided by the number of values -1)"
HTH

 

[Roger Govier]

Hi

An alternative way
=INDEX(C4:C18,MATCH(SMALL(E4:E18,1),E4:E18,0))