sumif remove high/low values

B

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
 
B

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)
 
R

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 said:
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)
Click to expand...
 
B

Bob Phillips

Hi RD,

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

habit I guess :-(.

Bob
 
R

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>
 
B

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
 
R

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>
 
Top