sumif remove high/low values

[Big Ben]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[RagDyer]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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 Phillips]

Hi RD,

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

habit I guess :-(.

Bob


"RagDyer" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote
in
> > message news:[email protected]...
> > >
> > > 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
> > >
> >
> >
>

[RagDyer]

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" <[email protected]> wrote in message
news:eC%[email protected]...
> Hi RD,
>
> No of course you are correct, there is nothing to coerce so no need.
>
> habit I guess :-(.
>
> Bob
>
>
> "RagDyer" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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" <[email protected]> wrote
> in
> > > message news:[email protected]...
> > > >
> > > > 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 Phillips]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:eC%[email protected]...
> > Hi RD,
> >
> > No of course you are correct, there is nothing to coerce so no need.
> >
> > habit I guess :-(.
> >
> > Bob
> >
> >
> > "RagDyer" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Bob,
> > > Is there really a need for the unary in your formulas?<g>
> > > --
> > > Regards,
> > >
> > > RD

[RagDyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:eC%[email protected]...
> > > Hi RD,
> > >
> > > No of course you are correct, there is nothing to coerce so no need.
> > >
> > > habit I guess :-(.
> > >
> > > Bob
> > >
> > >
> > > "RagDyer" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > Bob,
> > > > Is there really a need for the unary in your formulas?<g>
> > > > --
> > > > Regards,
> > > >
> > > > RD
>
>