Excel fromula: Adding numbers from list to achieve a target
 

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

Excel fromula: Adding numbers from list to achieve a target
 

ck13,
Wow....that is impressive....your answer starts to work but, unfortunately,
it doesnt.

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" wrote:

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

Excel fromula: Adding numbers from list to achieve a target
 

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

Excel fromula: Adding numbers from list to achieve a target
 

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" wrote:

ck13,
Wow....that is impressive....your answer starts to work but, unfortunately,
it doesnt.

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" wrote:

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

Excel fromula: Adding numbers from list to achieve a target
 

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

Bill

"ck13" wrote:

Not sure if it will fully works. Note that my assumption is that a similar
weight amount will not occur more than twice, otherwise, the formula
definitely will not work. I have some name being defined here;

Wt_Lbl =Sheet1!$A$2:$A$31
Wt_Amt =Sheet1!$B$2:$B$31
Working =Sheet1!$E$2:$E$31
Target_Wt =Sheet1!$C$2

Cell A1 is Wt Lbl
Cell B1 is Wt Amt
C1 is Target Wt
E1 is Working

Data start at A2, B2, C2 and E1
The formula at E2 is =IF(B2="","",B2-COUNTIF(B2:$B$31,B2)/100000000)
Drag this formula down

G1 to N1 are workings
G1 =INDEX(Wt_Amt,MATCH(Target_Wt,Working))
H1 =INDEX(Wt_Amt,MATCH(Target_Wt-SUM($G$1:G1),Working))
Drag H1 till desire

At G2, =INDEX(Wt_Lbl,MATCH(Target_Wt,Working))
At H2,
=IF(INDEX(Wt_Lbl,MATCH(Target_Wt-SUM($G$1:G1),Working))=G2,INDEX(Wt_Lbl,MATCH(Targe t_Wt-SUM($G$1:G1),Working)-1),INDEX(Wt_Lbl,MATCH(Target_Wt-SUM($G$1:G1),Working)))

drag the formula from H2 till desire.

I hope it works this time...


"BillD" wrote:

ck13,
Wow....that is impressive....your answer starts to work but, unfortunately,
it doesnt.

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" wrote:

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