Formula - shorter, neater, faster ?

  • Thread starter Thread starter Trevor Shuttleworth
  • Start date Start date
T

Trevor Shuttleworth

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
 
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)
 
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
 
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.
 
"OOhfffff" <g>

--
Regards,
Tom Ogilvy

Vasant Nanavati said:
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.



=(SUM(A15:L15)-(SUM(SMALL(A15:L15,{1,2,3}))+SUM(LARGE(A15:L15,{1,2,3}))))/6
=(SUM(A15:L15)-(SMALL(A15:L15,1)+SMALL(A15:L15,2)+SMALL(A15:L15,3)+LARGE(A15
Click to expand...
 
Oh, man ... <sigh> ... well, at least I enjoyed having the shortest formula
for a few minutes <bg>.

Nice one, Dana; a new function for me!

Regards,

Vasant.
 
Dana

now you are just showing off ! That is so good.

I was content with Bob's solution (which is what I had in mind) and
impressed by Vasant's solution but this is superb.

Thanks very much to everyone for the help and suggestions. I think I'll be
able to type this without introducing too many typos.

Regards

Trevor
 
Back
Top