Formula - shorter, neater, faster ?
Hi Trevor:
Perhaps the following array formula will do it for you:
=AVERAGE(LARGE(A15:L15,ROW(4:9)))
array-entered, of course.
Regards,
Vasant.
"Trevor Shuttleworth" wrote in message
...
Thanks Bob, the very thing I was looking for !
I was sure I had seen this or something like it before but couldn't think
how to put it together.
Cheers ... and thanks for the quick response
Trevor
"Bob Phillips" wrote in message
...
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
|