Formula - shorter, neater, faster ?
 

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

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

Formula - shorter, neater, faster ?
 

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

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

Formula - shorter, neater, faster ?
 

"OOhfffff" <g

--
Regards,
Tom Ogilvy

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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

Formula - shorter, neater, faster ?
 

Just another option:

=TRIMMEAN(A15:L15,6/12)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"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

Formula - shorter, neater, faster ?
 

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 DeLouis" wrote in message
...
Just another option:

=TRIMMEAN(A15:L15,6/12)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"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

Formula - shorter, neater, faster ?
 

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


"Dana DeLouis" wrote in message
...
Just another option:

=TRIMMEAN(A15:L15,6/12)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"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