How to combine all cells equal or close to a given value

[Gabrielle_erre]

Hey... I'm a newie on Excel and I've seen a lot of smart people around. any of you came across the given exemple below?

I've got a column of different products, and I'd like to find all combination of procucts up to a maximum value of 2,45 meters and count all those combinations to optimise the transport.I tried to sum all the numbers and divide them by 2,45 but I can not split the products. I also have some 0 values on the table that I do not need. Anyone has an idea about what I can use...?

number of pallets used	high
1	0,88
1	1,56
1	2,24
1	3,72
1	1,08
1	1,96
1	3,64
1	2,08
1	2,76
1	0,68
1	2,56
1	1,08
1	1,76
1	3,24
2	3,16
2	3,56
0	0
0	0
0	0
0	0

<colgroup><col width="80" span="2" style="width:60pt"> </colgroup><tbody>
</tbody>

Is there a way through Excel (or anything else if you have in mind) that can list me the best number combinations ?
Thanks in advance!

 

[ravishankar]

Re: How to ombine all cells equal or close to a given value

there are only two products and many are over 2.45. let alone combine, by themselves are too big for carrier. can you make your problem more clear?
Ravishankar

 

[Gabrielle_erre]

Re: How to ombine all cells equal or close to a given value

Thank you Ravishankar! The first example was not complete....and it was not very clear.
I would like to combine all the products from column A by they're hights in colomn D, in order to optimise the space. The maximum hight is 2,45 and I would like to know if there is any logical argument I can put, in otder to group them to a value closer to 2,46. for exceple C3 will have 1x2,45 + 1x0,03.
I would like to keep the 2,45 (1xfull) and 0,03 to combine with another one. I will need a function for all the column D. Is possible something like that in Excel...?

A	B	C	D
1	Product	quantity	No. Of pallet
2	p1	0,54	1
3	p2	4,48	2
4	p3	6,42	3
5	p4	5,42	3
6	p5	2,88	2
7	p6	0,94	1
8	p7	0	0
9	p8	1,06	1

<colgroup><col width="80" span="4" style="width:60pt"> </colgroup><tbody>
</tbody>

 

[sandy666]

Re: How to ombine all cells equal or close to a given value

could you show expected result based on example from post #3 ?

 

[Gabrielle_erre]

Re: How to ombine all cells equal or close to a given value

hey, I was thinking about something like:

complete pal (2,45)	rest to combine	Combined pallets max 2,45	=	Total to transport
	0,54 p1
p2	0,3 p2	p2+p4+p5+p8	0,3 +0,43+0,55+1,06
p3	1,52 p3	p1+p3	0,54+1,52
p3		p6	0,94
p4
p4	0,55 p4
p5	0,43 p5
p6	0,94 p6
p7
p8	1,06 p8

<tbody>
</tbody>

 

[sandy666]

Re: How to ombine all cells equal or close to a given value

is that what you want ?
or I misunderstood...

0.01​

0.00​

0.01​

0.00​

0.00​

0.01​

0.01​

Product

P1​

P2​

P3​

P4​

P5​

P6​

P7​

P8​

TTT​

2.42​

2.43​

2.44​

2.45​

2.46​

2.47​

2.48​

Given value: 2.45 +/- 0.03

Qty

0.54​

0.3​

1.52​

0.55​

0.43​

0.94​

0​

1.06​

2.43​

2.43​

2.45​

2.45​

2.46​

2.46​

2.49​

0​

0​

0​

0​

1​

1​

1​

1​

2.43​

0​

0​

0​

0​

1​

1​

0​

1​

2.43​

1​

1​

0​

1​

0​

0​

0​

1​

2.45​

1​

1​

0​

1​

0​

0​

1​

1​

2.45​

1​

0​

0​

1​

1​

1​

1​

0​

2.46​

1​

0​

0​

1​

1​

1​

1​

0​

2.46​

1​

0​

1​

0​

1​

0​

1​

0​

2.49​

 

[Gabrielle_erre]

Re: How to ombine all cells equal or close to a given value

YES!

Just that I can not insert the error... and I do not need exact hight of 2,45, just to optimise the hight of pallets. for ex. the rest of incomplete pallets : p1+p2+p4=2,40 is ok, but if it would be 2,46, than I mix any 2 products together and make 1 pallet, +1 additional pallet.

 

[sandy666]

Re: How to ombine all cells equal or close to a given value

so you can take 2.43 and 2.45 then the rest optimise again for 2,45 or less
with Solver

as you can see there is no definite result

 

[Gabrielle_erre]

Re: How to ombine all cells equal or close to a given value

And how did you get the results ? what have you used?

 

[sandy666]

Re: How to ombine all cells equal or close to a given value

I did it with Solver, I you don't see it on the Data tab go to Developer tab then Excel Add-ins and mark Solver there

Here is a example excel file

look at A1...G1 (formula) and A3...G3 (another formula)