Formula help

[danushka]

hi, i have some weights(kg) in cell A2. so i have two type of packages to fill that weights.one package is 1.5kg & other one is 1 kg. i want use that two pckage to fill that A2 cell weights with less different. but there are two conditions.
01. always 0.5 kg need to add to A2 cell weights.
02. for weight adjusting u can use only maximum 2 of 1 kg packages.so as a example to fill 13.8 kg result should like bellow..(my excel sheet also data appearing like bellow table)

=13.8+0.5 = 14.3

so i have to use 9,1.5 pks & 1, 1 kg pks. result will be - 14.5 ( that mean only different 0.2kg with actual requirement) if i use 10 of 1.5 kg, then result is =15 ,that mean i have used more than 0.7kg. best option is first one.

13.8
1.5 kg	9
1 kg	1

<tbody>
</tbody>

so i don't no this is can manage with excel formula or not. so pls help me. thanks a lot...

 

[alvin-chung]

Since you only have 3 options (e.g. 0/1/2 of 1kg pks, you may want to try the following to select the best option.

	A	B	C	D
1	Original Weight	13.8 kg
2	New Weight	14.3 kg
3
4	Type	Option 1	Option 2	Option 3
5	1.0 kg	0	1	2
6	1.5 kg	10	9	9
7	Total	15.0 kg	14.5 kg	15.5 kg
8	Difference	0.7	0.2	1.2
9
10	Best Option	Option 2

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 105px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =B1+0.5 B6 =ROUNDUP(($B$2-($A$5*B5))/$A$6,0) C6 =ROUNDUP(($B$2-($A$5*C5))/$A$6,0) D6 =ROUNDUP(($B$2-($A$5*D5))/$A$6,0) B7 =($A$5*B5)+($A$6*B6) C7 =($A$5*C5)+($A$6*C6) D7 =($A$5*D5)+($A$6*D6) B8 =B7-$B$2 C8 =C7-$B$2 D8 =D7-$B$2 B10 {=INDEX(B4:D4,0,MATCH(MIN((B8:D8>=0)*(B8:D8)),B8:D8,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

 

[danushka]

thanks lot fot your help. i have two cells below the weight. so one for 1.5kg and other one for 1 kg. i want to auto fill that two cells with best options. if this possible?. thanks.

 

[alvin-chung]

I'm afraid I couldn't understand your question, could you try illustrate with a table?

 

[danushka]

it's like this. a2 cell weight.
a3 cell 1.5kg (text)
a4 cell 1.0kg(text)

so i want answer to cell b3 (how many 1.5 pkgs) & cell b4( how many 1kg pkgs). is this clear.? i'm sending this from my phone. so can't make table.

 

[alvin-chung]

You mean if you key in 13.8 in A1, you want B3 to show 9 and B4 to show 1?

 

[danushka]

yes. is this possible?. thanks.

 

[alvin-chung]

That's possible by shifting the index position, you can hide column D:G thereafter if need so.

	A	B	C	D	E	F	G
1	13.8			Original Weight	13.8 kg
2	Weight			New Weight	14.3 kg
3	1.5 kg	9
4	1.0 kg	1		Type	Option 1	Option 2	Option 3
5				1.0 kg	0	1	2
6				1.5 kg	10	9	9
7				Total	15.0 kg	14.5 kg	15.5 kg
8				Difference	0.7	0.2	1.2
9
10				Best Option	Option 2

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 105px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 =A1 E2 =E1+0.5 B3 {=INDEX(E6:G6,0,MATCH(MIN(E8:G8),E8:G8,0))} B4 {=INDEX(E5:G5,0,MATCH(MIN(E8:G8),E8:G8,0))} E6 =ROUNDUP(($E$2-($D$5*E5))/$D$6,0) F6 =ROUNDUP(($E$2-($D$5*F5))/$D$6,0) G6 =ROUNDUP(($E$2-($D$5*G5))/$D$6,0) E7 =($D$5*E5)+($D$6*E6) F7 =($D$5*F5)+($D$6*F6) G7 =($D$5*G5)+($D$6*G6) E8 =E7-$E$2 F8 =F7-$E$2 G8 =G7-$E$2 E10 {=INDEX(E4:G4,0,MATCH(MIN(E8:G8),E8:G8,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

 

[danushka]

That's possible by shifting the index position, you can hide column D:G thereafter if need so.

	A	B	C	D	E	F	G
1	13.8			Original Weight	13.8 kg
2	Weight			New Weight	14.3 kg
3	1.5 kg	9
4	1.0 kg	1		Type	Option 1	Option 2	Option 3
5				1.0 kg	0	1	2
6				1.5 kg	10	9	9
7				Total	15.0 kg	14.5 kg	15.5 kg
8				Difference	0.7	0.2	1.2
9
10				Best Option	Option 2

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E1 =A1 E2 =E1+0.5 B3 {=INDEX(E6:G6,0,MATCH(MIN(E8:G8),E8:G8,0))} B4 {=INDEX(E5:G5,0,MATCH(MIN(E8:G8),E8:G8,0))} E6 =ROUNDUP(($E$2-($D$5*E5))/$D$6,0) F6 =ROUNDUP(($E$2-($D$5*F5))/$D$6,0) G6 =ROUNDUP(($E$2-($D$5*G5))/$D$6,0) E7 =($D$5*E5)+($D$6*E6) F7 =($D$5*F5)+($D$6*F6) G7 =($D$5*G5)+($D$6*G6) E8 =E7-$E$2 F8 =F7-$E$2 G8 =G7-$E$2 E10 {=INDEX(E4:G4,0,MATCH(MIN(E8:G8),E8:G8,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

hi, thanks a lot for your formula. i have found another two formulas. but need support column. (column "B"). can u pls check this, i want get this answers without supporting column (column "B"). thanks..
Download Weights v2.xlsx from Sendspace.com - send big files the easy way

 

[alvin-chung]

In your spreadsheet try replace Bn with An+0.5
E.g. C2=IF((A2+0.5)<2,"",IF(MOD(A2+0.5,$C$1)<=1,INT((A2+0.5)/$C$1),INT((A2+0.5)/$C$1)+1))
E.g. D2=IF((A2+0.5)<=1,1,IF((A2+0.5)<=2,2,IF(A2+0.5-(C2*$C$1)=0,"",IF(A2+0.5-$C$1*C2<0,"",1))))

ps: The formula looks inaccurate to me, say in your spreadsheet row 7-11 wouldn't it be better if you choose 1 1.5kg instead of 2 1kg (which is stated in your criteria to select the best option by having least difference)?