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

nsnl1

New Member
Joined
Jun 12, 2015
Messages
3
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.


Areaoutputcost
Northumberland222
Tyne and Wear333
Durham444
Cleveland575
North Yorshire656
Borders141
West Yorkshire232
Mersyeside808
Cumbria656
Linconshire242

<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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Jun 12, 2015
Messages
3
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Jun 12, 2015
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,599
Messages
5,597,105
Members
414,125
Latest member
iQQ

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top