sumif remove high/low values
 

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

My issue with the unary is it doesn't return any error when encountering a
numeric text value *or* an alpha text value.
It just ignores them and it just returns zero.
You can never realize if your data is corrupt (different).

On the other hand, the asterisk form will *actually calculate* your numeric
text, and return an error with alpha text.

When dealing constantly with web imported data, this is virtually an
indispensable feature (property).

I found this out the hard way after revising some inventory formulas.
The inventory was valued by either importing prices from the vendors web
site, or manual entries from faxes and snail mail.
Definitely mixed data types.
Everything was fine with the asterisk formula though, and the mixed data was
never realized to be a point of concern.

Having been propagandized (is that a word?) into believing that the
efficiency would be improved with the unary form, I revised the formulas.

Needless to say, our production costs were reduced that first quarter, and I
was feeling pleased that finally the plant was functioning as we had
planned.

That is, until the accounting department started reconciling the inventory.

There wasn't a hole deep enough for me to crawl into.

Anyway, that's my relationship with the unary.

And I'm just crying in my beer now.<bg
--
Regards,

RD

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

"Bob Phillips" wrote in message
...
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