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

#### nsnl1

##### New Member
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.

 Area output cost Northumberland 22 2 Tyne and Wear 33 3 Durham 44 4 Cleveland 57 5 North Yorshire 65 6 Borders 14 1 West Yorkshire 23 2 Mersyeside 80 8 Cumbria 65 6 Linconshire 24 2

<tbody>
</tbody>

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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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).

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

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

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.

You're welcome!

(Years of practice! )

Cheers

Replies
10
Views
663
Replies
0
Views
305
Replies
0
Views
497
Replies
2
Views
486
Replies
7
Views
308

1,221,521
Messages
6,160,302
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back