Hello,

I have a list of ten values. I would like to sum this list with the
top 2 and bottom 2 values removed in the sum calculation.

Any help would be great,

ben


--
Big Ben
------------------------------------------------------------------------
Big Ben's Profile: http://www.excelforum.com/member.php...fo&userid=6431
View this thread: http://www.excelforum.com/showthread...hreadid=395038

Hi Ben,

Try this

=SUM(D1:D10)-SUMPRODUCT(--(LARGE(D1:D10,{1;2}))+(SMALL(D1:D10,{1;2})))

or if there will be 10 then you could use

=SUMPRODUCT(--(LARGE(D1:D10,ROW(INDIRECT("3:8")))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Ben" wrote in
message ...

Hello,

I have a list of ten values. I would like to sum this list with the
top 2 and bottom 2 values removed in the sum calculation.

Any help would be great,

ben


--
Big Ben
------------------------------------------------------------------------
Big Ben's Profile:
http://www.excelforum.com/member.php...fo&userid=6431
View this thread: http://www.excelforum.com/showthread...hreadid=395038

Bob,
Is there really a need for the unary in your formulas?<g
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bob Phillips" wrote in message
...
Hi Ben,

Try this

=SUM(D1:D10)-SUMPRODUCT(--(LARGE(D1:D10,{1;2}))+(SMALL(D1:D10,{1;2})))

or if there will be 10 then you could use

=SUMPRODUCT(--(LARGE(D1:D10,ROW(INDIRECT("3:8")))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Ben" wrote in
message ...

Hello,

I have a list of ten values. I would like to sum this list with the
top 2 and bottom 2 values removed in the sum calculation.

Any help would be great,

ben


--
Big Ben
------------------------------------------------------------------------
Big Ben's Profile:
http://www.excelforum.com/member.php...fo&userid=6431
View this thread:
http://www.excelforum.com/showthread...hreadid=395038

Hi RD,

No of course you are correct, there is nothing to coerce so no need.

habit I guess :-(.

Bob


"RagDyer" wrote in message
...
Bob,
Is there really a need for the unary in your formulas?<g
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"Bob Phillips" wrote in message
...
Hi Ben,

Try this

=SUM(D1:D10)-SUMPRODUCT(--(LARGE(D1:D10,{1;2}))+(SMALL(D1:D10,{1;2})))

or if there will be 10 then you could use

=SUMPRODUCT(--(LARGE(D1:D10,ROW(INDIRECT("3:8")))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Ben" wrote
in
message ...

Hello,

I have a list of ten values. I would like to sum this list with the
top 2 and bottom 2 values removed in the sum calculation.

Any help would be great,

ben


--
Big Ben

------------------------------------------------------------------------
Big Ben's Profile:
http://www.excelforum.com/member.php...fo&userid=6431
View this thread:
http://www.excelforum.com/showthread...hreadid=395038

I have a personal vendetta against it (unary), and hate it with a passion,
really because *I* screwed up royally in revising an entire project over to
using it, in place of the asterisk.
So, while admiring you're handling of this issue, I just couldn't bare to
let the opportunity pass to eliminate it from any possibility of being used,
no matter in how small the occasion might be.<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bob Phillips" wrote in message
...
Hi RD,

No of course you are correct, there is nothing to coerce so no need.

habit I guess :-(.

Bob


"RagDyer" wrote in message
...
Bob,
Is there really a need for the unary in your formulas?<g
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-

"Bob Phillips" wrote in message
...
Hi Ben,

Try this

=SUM(D1:D10)-SUMPRODUCT(--(LARGE(D1:D10,{1;2}))+(SMALL(D1:D10,{1;2})))

or if there will be 10 then you could use

=SUMPRODUCT(--(LARGE(D1:D10,ROW(INDIRECT("3:8")))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Big Ben" wrote
in
message ...

Hello,

I have a list of ten values. I would like to sum this list with the
top 2 and bottom 2 values removed in the sum calculation.

Any help would be great,

ben


--
Big Ben

------------------------------------------------------------------------
Big Ben's Profile:
http://www.excelforum.com/member.php...fo&userid=6431
View this thread:
http://www.excelforum.com/showthread...hreadid=395038

I am a convert personally, although I accept that some formulas will not
work with -- but will with the *.

I like the fact that you can coerce dates and times as well as True/False.
You can't do that with * (1* yes, but I hate that with a passion).

Bob

"RagDyer" wrote in message
...
I have a personal vendetta against it (unary), and hate it with a passion,
really because *I* screwed up royally in revising an entire project over
to
using it, in place of the asterisk.
So, while admiring you're handling of this issue, I just couldn't bare to
let the opportunity pass to eliminate it from any possibility of being
used,
no matter in how small the occasion might be.<bg
--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"Bob Phillips" wrote in message
...
Hi RD,

No of course you are correct, there is nothing to coerce so no need.

habit I guess :-(.

Bob


"RagDyer" wrote in message
...
Bob,
Is there really a need for the unary in your formulas?<g
--
Regards,

RD