Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Combine FREQUENCY and SUM of Associated Values | Excel Worksheet Functions | |||
Return Consecutive Values - Pairs | Excel Worksheet Functions | |||
SUMIF positive values only from database | Excel Worksheet Functions | |||
Finding Values With more than 2 decimal Places | Excel Discussion (Misc queries) |