calculating average on bottom 50% of data

S

steele57

Here's the problem. I am trying to find a solution to the following problem.
I need to make a spreadsheet based on a sample of the following info.

Name
Norm 80 70 75 90 95 96 98 92 85 70
Jon 100 102 104 106 98 95 112 95 98 97

With this data, I need a formula to calculate the average of the lowest 5
scores (out of 10 total). I need cell refrences. Thanks in advance.
 
B

Bob Phillips

Try this

=AVERAGE(SMALL(B2:N2,ROW(1:5)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Top