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

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...


"BillD" wrote:

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