Formula - shorter, neater, faster ?
Trevor,
How about
=(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1, 2,3}))))/6
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Trevor Shuttleworth" wrote in message
...
I'm trying to calculate some targets for next year based on this year's
achievements.
So, assume I have twelve numbers in a row for January to December. I want
to discard the three lowest scores and the three highest scores and then
sum
the remaining six values and divide by 6 to get an average. I have come
up
with the following formula but I'm sure there must be a shorter, neater
and
possibly faster way to write it (well, certainly faster to write with less
possibility of getting it wrong !)
=(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3 )+LARGE(A15
:L15,1)+LARGE(A15:L15,2)+LARGE(A15:L15,3)))/6
Any thoughts ?
Of course I could just discard the TWO smallest and TWO largest ... which
might be an option.
Thanks for your help
Trevor
|