Excel fromula: Adding numbers from list to achieve a target

[BillD]

I'm not sure if this is possible in Excel but I'd sure love if I could get
this to work.

I have a column of weights (different sizes). Each weight is labeled in the
column next to the weight amount column. And a Target weight in another
column. For example:

ColumnA ----- ColumnB ---------- ColumnC
Wt Lbl ----- Weight Amt -------- Target Wt

A1: a ----- B1: 0.125 ---------- C1: 4.256
A2: b ----- B2: 0.250
A3: c ----- B3: 0.500
A4: d ----- B4: 0.750
A1: e ----- B1: 1.000
A2: f ----- B2: 2.000
A3: g ----- B3: 2.000
A4: h ----- B4: 5.000


Now I have a target weight I am trying to achieve, lets say it's 4.256 lbs
I need to know which weights (identified by its label) will be required to
equal something just less than the target weight (with the remainder
identified in another cell).

In the example above the answer would be f,g,b (which adds up to 4.250 lbs)
with a remainder (or rather shortage) of .006 lbs.

Is this possible?? I'd bow to the Excel genuis that could figure out this
one .
Thanks,
Bill

 

[BillD]

ck13,
Wow....that is impressive....your answer starts to work but, unfortunately,
it doesn’t.

There are about 30 weights.
I need to know the remainder.
Some target weights (in C1) dont work in your formula (6.00 for one)

I gotta tell you though, I'm pretty impressed with your knowledge of Excel.
....any other sugestions?

 

[ck13]

Trying my luck.. I assume the following otherwise, it will be back to the
drawing board:

1. The weight amount that you give is all there is, meaning after 5 lbs,
there are no more weights.

the weight label a start at A1, weight amount 0.125 at B1 and weight target
4.256 at C1

I created a helper column at column D for f and g as both have the same
weights. The formula in E6 is =IF(B6="","",B6+COUNTIF(B6:$B$8,B6)/10000) This
is drag down to E7.

My solution start from G1 to J1
G1
=INDEX(A1:A8,MATCH(IF(INDEX(B1:B8,MATCH(C1,B1:B8))=2,LARGE(E1:E8,1)),E1:E8))

H1 =INDEX(A1:A8,MATCH(C1-INDEX(B1:B8,(MATCH(C1,B1:B8))),B1:B8))

I1
=INDEX(A1:A8,MATCH((C1-INDEX(B1:B8,(MATCH(C1,B1:B8))))-INDEX(B1:B8,MATCH(C1-INDEX(B1:B8,(MATCH(C1,B1:B8))),B1:B8)),B1:B8))

J1
=INDEX(A1:A8,MATCH(B1:B8,(C1-INDEX(B1:B8,(MATCH(C1,B1:B8))))-INDEX(B1:B8,MATCH(C1-INDEX(B1:B8,(MATCH(C1,B1:B8))),B1:B8))-INDEX(B1:B8,MATCH((C1-INDEX(B1:B8,(MATCH(C1,B1:B8))))-INDEX(B1:B8,MATCH(C1-INDEX(B1:B8,(MATCH(C1,B1:B8))),B1:B8)),B1:B8))))

J1 will show #NA meaning no more weights label.

Hope luck is on my side for it to work...

 

[ck13]

Hi,

I also relook into the formula and realise some problems with it. it works
for a certain range. I will need to go through it again. Will try and see
what i can come up with.. If not, i will also let you know through here.

 

[BillD]

GREAT!
Thanks for all the help ck13. I sure appreciate it.

Bill