Multiple 'Median' Values

[lincolnshep]

I've never asked a question on here before but have benefitted from many answers posted to other users questions - so thank you all for that. I can't however, find an answer to this one.

I have a list of 30 numbers (all +ve integers or zero) that changes every day. I want to ignore the lowest 10 and the highest 10 and then find the average of the 'middle' 10. At the minute I copy the latest 30 numbers every day and then re-order them to identify the middle 10 but there must be a neater way.

Can anyone think of a way of doing this in a formula or with VBA?

Thanks in advance.

(BTW, I've got Excel 2007)

 

[Richard Schollar]

Hi

Assuming your values are in A1:A30 then perhaps:

=MEDIAN(LARGE(A1:A30,{11,12,13,14,15,16,17,18,19,20}))

 

[mikerickson]

This should sum the middle 10 numbers
=SUMPRODUCT(A1:A30,--(10 < RANK(A1:A30,A1:A30)),--(RANK(A1:A30,A1:A30)<=20))

 

[lincolnshep]

Thank you both. Will have a play with those two ideas and see what happens.

Regards,
Shep

 

[barry houdini]

Richard, won't the median of the middle 10 always be the same as the median of the entire sample?

Of course if you want an average then I think that using AVERAGE instead of MEDIAN in Richard's suggestion would be valid....

 

[Richard Schollar]

Assuming the upper and lower ranges discarded are the same size then yes - if they are different sizes then no (determined by the array constant passed in to Large).

Think that's right, anyhow

 

[lincolnshep]

Barry / Richard, Thank you both, I've not used LARGE before but combining it with AVERAGE has given me just what I wanted. I've checked the results against my old 'manual' system and they match every time. Perfect!

Mike, Thank you too for your quick answer, I'm sure it works just as well but I found the other one a little easier to follow.

Really appreciate everyone's help with this.

Regards,
Shep