Find the 3 largest values in one column which have a sum in another column of 10 or less

[nsnl1]

Hi,

I am trying to identify up to the top three Areas (i.e. highest output) that when combined have a cost of 10 or less:

For example: Mersyeside and Linconshire would give me a combined output of (80 + 24) = 104 for a combined cost of 10. However; Durham, Clevelend and Borders would give me (44 + 57 + 14) = 115 for a for a combined total of 10.


[TABLE="width: 249"]
<tbody>[TR]
[TD]Area[/TD]
[TD]output[/TD]
[TD]cost[/TD]
[/TR]
[TR]
[TD]Northumberland[/TD]
[TD]22[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Tyne and Wear[/TD]
[TD]33[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Durham[/TD]
[TD]44[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Cleveland[/TD]
[TD]57[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]North Yorshire[/TD]
[TD]65[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Borders[/TD]
[TD]14[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]West Yorkshire[/TD]
[TD]23[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Mersyeside[/TD]
[TD]80[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Cumbria[/TD]
[TD]65[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Linconshire[/TD]
[TD]24[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Please could you show me how I can find what the 3 best sites would be for a cost of 10 or less?

I am using Windows 7 and Excell 2010.

Thanks for any guidance,
Steve

 

[XOR LX]

Hi.

Assuming the table as you give it is in A2:C11, and that you use D2 and E2 to house your variables, with 3 in D2 and 10 in E2, then first go to Name Manager (Formulas tab) and define:

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&ROWS($C$2:$C$11)))

Name: Arry2
Refers to: =ROW(INDIRECT("1:"&2^ROWS($C$2:$C$11)))

Name: Arry3
Refers to: =MOD(INT((Arry2-1)/2^(TRANSPOSE(Arry1)-1)),2)

The required array formula** is then:

=IFERROR(INDEX($A:$A,SMALL(IF(INDEX(Arry3,LOOKUP(1,0/FREQUENCY(0,1/(1+MMULT(N(IF(IF(MMULT(Arry3,Arry1^0)<=$D$2,MMULT(Arry3,$C$2:$C$11)<=$E$2),Arry3)),$B$2:$B$11))),Arry2),)=1,TRANSPOSE(ROW($B$2:$B$11))),ROWS($1:1))),"")

Copy down a few more rows until you start to get blanks.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[nsnl1]

WOW! How on earth did you manage to do that so fast? Very impressive.

I assume I'll need to start the array formula in cell D2? If so, when I enter the array formula I get the error:

Microsoft Excell cannot calculate a formula. there is a circular reference in an open workbook, but the references that cause it cannot be listed for you. try editing the last formula you entered or removing it with the Undo command.

I've gone back and double checked the Arry definitions, but they seem OK. Any idea what I'm doing wrong?

Thanks very much for your help I wasn't expecting a response quite so quickly.

Cheers,
Steve

 

[XOR LX]

Remember that I told you to enter one of your variables in D2? So the main formula can't be there as well!

If you look at the formula I gave you, you'll see that it's referencing cell D2, so obviously placing the formula in the same cell which it is attempting to reference will cause a circular error.

Regards

 

[nsnl1]

Sorry - I see my mistake now, I've put values 3 and 10 in cells D1 and E1 not D2 and E3 as you said.

This works brilliantly, thanks ever so much. I've truly no idea how you managed to do this so quickly though.

 

[XOR LX]

You're welcome!

(Years of practice! )

Cheers