Summing sets of numbers but with special conditions

[Jeff Hooper]

Hi - I need a formula to do the following. I have 2 columns of six
numbers - for example:

A B
1 75 70
2 83 69
3 80 72
4 84 67
5 95 83
6 93 80

The formula needs to do the following:

1. Find the lowest number in column A (75 in row 1 in the above example)
2. Discard the number in corresponding cell in column B (70 in cell B1)
3. From the remaining numbers in column B, discard the highest (83 in cell
B5)
4. Add together the remaining 4 numbers in column B (69, 72, 67, 80) and add
this result to the number found in step 1 (75) to give a total (363)

Any ideas/pointers most welcome.

Thanks

 

[Sandy Mann]

Jeff,

=SUM(B1:B6)-INDEX(B1:B6,MATCH(MIN(A1:A6),A1:A6))-INDEX(B1:B6,MATCH(MAX(A1:A6
),A1:A6))+MIN(A1:A6)

will give you 363 but I can't help feeling that there must be a more elegant
way of doing it.

Regards,

Sandy

 

[Sandy Mann]

I told you that there was a better way:

=SUMPRODUCT((A1:A6<>MIN(A1:A6))*(A1:A6<>MAX(A1:A6))*(B1:B6))+MIN(A1:A6)

HTH

Sandy

 

[Jeff Hooper]

Sandy - many thanks for quick replies. Seems OK except when I try it on
these numbers:

70 67

74 70

87 69

81 63

79 61

88 68

Gives 333 instead of 331 (70+69+63+61+68)

Jeff

 

[Ken Wright]

Probably a way of netting this down, but it seems to work for your example
data:-

=MIN($A$1:$A$6)+SUMPRODUCT((($A$1:$A$6<>MIN($A$1:$A$6))*($B$1:$B$6)))-SUMPRODUCT
(MAX((($A$1:$A$6<>MIN($A$1:$A$6))*($B$1:$B$6))))

 

[Sandy Mann]

Jeff,

Sorry, I misread your criteria. I discarded the value in column B adjacent
to the highest value in column A.

Try:-
=SUM((A1:A6<>MIN(A1:A6))*(B1:B6))-MAX(((A1:A6<>MIN(A1:A6))*(B1:B6)))+MIN(A1:
A6)

which is an array formula. Hold down Ctrl and Shift while you press Enter.
If you do it right then XL will add curly braces around the formula like:-

{=SUM((A1:A6<>MIN(A1:A6))*(B1:B6))-MAX(((A1:A6<>MIN(A1:A6))*(B1:B6)))+MIN(A1
:A6)}

(but don't add them yourself)

HTH and once again my apologies for not reading your question right.

Sandy

 

[Sandy Mann]

Ken,

I like the use of MAXand MIN in the same SUMPRODUCT, that was the bit I
could not work out even when I read the question properly.

Regards

Sandy

 

[Ken Wright]

LOL - and I liked yours because it looked shorter. Either way, I have no idea
what happens if there is a tie for Max or Min, but I'm guessing both will return
incorrect values as they are likely to discard one too many values. Up to the
OP to decide if it needs to be catered for, but it's pretty much bedtime here in
the UK now

 

[Peo Sjoblom]

If the values in A are all unique one can just use

=SUMPRODUCT(($A$1:$A$6<>MIN($A$1:$A$6))*($B$1:$B$6))-MAX($B$1:$B$6)+MIN($A$1
:$A$6)

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Dave Peterson]

I'm not sure that works.

If my data looks like:

70 1000
74 75
87 69
81 63
79 61
88 68

So I'm dumping the 1000 and the 75.
70+69+63+61+68 = 331

(I'd user helper cells <vbg>.)

 

[Dave Peterson]

I'd use a few helper cells.

In C1:
=IF(A1=MIN($A$1:$A$6),0,B1)
(copied down to C6)

In D1:
=IF(C1=MAX($C$1:$C$6),0,C1)
(copied down to D6)

In E1:
=SUM(D16)+MIN(A1:A6)

(Those long formulas hurt my head. So I give up and use a couple of those 256
columns. <vbg>)

 

[Peo Sjoblom]

Thanks for the heads up LOL

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Ken Wright]

(Those long formulas hurt my head. So I give up and use a couple of those 256

columns. <vbg>)

LOL

 

[Peo Sjoblom]

Here's another adaptation, it would have helped if I have
had read the OP first post

=SUMPRODUCT(LARGE(($A$1:$A$6<>MIN($A$1:$A$6))*($B$1:$B$6),ROW(INDIRECT("2:"&
COUNT(B1:B6)))))+MIN($A$1:$A$6)

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Ron Rosenfeld]

Hi - I need a formula to do the following. I have 2 columns of six
numbers - for example:

A B
1 75 70
2 83 69
3 80 72
4 84 67
5 95 83
6 93 80

The formula needs to do the following:

1. Find the lowest number in column A (75 in row 1 in the above example)
2. Discard the number in corresponding cell in column B (70 in cell B1)
3. From the remaining numbers in column B, discard the highest (83 in cell
B5)
4. Add together the remaining 4 numbers in column B (69, 72, 67, 80) and add
this result to the number found in step 1 (75) to give a total (363)

Any ideas/pointers most welcome.

The *array-entered* formula:

=SUM(LARGE(((A1:A6)<>MIN(A1:A3))*B1:B6,{2,3,4,5}))+MIN(A1:A6)

will do what you want.

To *array-enter* a formula, after typing or pasting it in, hold down
<ctrl><shift> while hitting <enter>. XL will place braces {...} around the
formula.

A more general format would be the array formula:


=SUM(LARGE(((ColA)<>MIN(ColA))*ColB,ROW(INDIRECT("2:"&COUNT(ColB)-1))))+MIN(ColA)


--ron

 

[Dave Peterson]

Ouch!!!!!!!!

Here's another adaptation, it would have helped if I have
had read the OP first post

=SUMPRODUCT(LARGE(($A$1:$A$6<>MIN($A$1:$A$6))*($B$1:$B$6),ROW(INDIRECT("2:"&
COUNT(B1:B6)))))+MIN($A$1:$A$6)

--

For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Jeff Hooper]

Thanks Sandy - this works fine except for when there is a tie for the lowest
number in column A. I failed to mention that if there is, then the one to
be used is the one with the higher number in column B. For example, in
these numbers:

86 80
99 85
90 74
102 84
92 73
86 67


there are 2 lowest numbers in Column A (86) but the one to be used is the
one in A1 since it has an 80 in column B whereas the other has 67. The
total should thus equal 86+74+84+73+67= 384 whereas your formula gives 317
(because I think it doesn't include the 67 in the addition).

(If you are wondering what this is all about, it's a scoring system for a
teams of 6 golfer. Col A is their gross scores, column B their nett scores.
The team score is the best gross score plus the sum of the 4 best nett
scores from the remaining 5!).

Jeff H

Jeff,

Sorry, I misread your criteria. I discarded the value in column B adjacent
to the highest value in column A.

Try:-

 

[Ron Rosenfeld]

(If you are wondering what this is all about, it's a scoring system for a
teams of 6 golfer. Col A is their gross scores, column B their nett scores.
The team score is the best gross score plus the sum of the 4 best nett
scores from the remaining 5!).

Try this *array-entered* formula:

=SUM(Net)-MAX(IF(MIN(Gross)=Gross,Net))-MAX(Net)+MIN(Gross)

At least, it seems to give the desired answer for the examples you have posted.


--ron