Summing sets of numbers but with special conditions

  • Thread starter Thread starter Jeff Hooper
  • Start date Start date
J

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
 
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
 
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
 
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
 
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))))
 
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
 
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
 
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 :-)
 
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
 
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>.)
 
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(D1:D6)+MIN(A1:A6)

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

--


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

Regards,

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
 
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.
Click to expand...


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
 
Ouch!!!!!!!!



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

Sandy Mann said:
Jeff,

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

Try:-
Click to expand...
 
(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!).
Click to expand...


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