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

 

[danushka]

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)?

thanks a for the reply, i have dicide to remove that 0.5kg adding part. so now u can use directly cell A1 value. do u have any other idea to get correct answer using just two formulas like my spreadsheet. thanks..

 

[markmzz]

Maybe this (here, in my configuration, . = ,) :

Layout

Weight	1,5 kg	1 kg
13,8	9	1
0,1	0	1
0,2	0	1
0,3	0	1
0,4	0	1
8,6	5	2
3,1	2	1
4,4	2	2
0,8	0	1
0,9	0	1
1	0	1
1,1	0	2
1,2	0	2
1,3	0	2
1,4	0	2
14,3	9	1
14,1	9	1
14,6	9	2
14,5	9	1
********	********	********

<colgroup><col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;" span="3"> <tbody>
</tbody>

Formulas

In B2

=INT($A2/SUBSTITUTE(B$1,"kg",""))

In C2

=ROUNDUP($A2-$B2*SUBSTITUTE(B$1,"kg","")/SUBSTITUTE(C$1,"kg",""),0)

Markmzz

 

[danushka]

Maybe this (here, in my configuration, . = ,) :


Markmzz

thanks for the reply. if weight is 1.1 kg we should use one of 1.5 kg, but your formula using 2 of 1 kg. pls check it.. thanks..

 

[markmzz]

thanks for the reply. if weight is 1.1 kg we should use one of 1.5 kg, but your formula using 2 of 1 kg. pls check it.. thanks..

Try this:

In B2

=IF(A2<=1,0,IF(A2<=1.5,1,INT((A2-1.6)/0.5)-INT((A2-1.6)/1.5)*2))

In C2

=IF(A2<=1,1,IF(A2<=1.5,0,IF(($A2-B2*1.5)>0,ROUNDUP(($A2-B2*1.5),0),0)))

Markmzz

 

[danushka]

Try this:

In B2

=IF(A2<=1,0,IF(A2<=1.5,1,INT((A2-1.6)/0.5)-INT((A2-1.6)/1.5)*2))

In C2

=IF(A2<=1,1,IF(A2<=1.5,0,IF(($A2-B2*1.5)>0,ROUNDUP(($A2-B2*1.5),0),0)))

Markmzz

wow thanks a lot. it's perfect..

 

[markmzz]

wow thanks a lot. it's perfect..

You're welcome and thanks for the feedback.

PS: a small modification in the formulas:

In B2

=IF($A2<=1,0,IF($A2<=1.5,1,INT(($A2-1.6)/0.5)-INT(($A2-1.6)/1.5)*2))

In C2

=IF(($A2-$B2*1.5)>0,ROUNDUP(($A2-$B2*1.5),0),0)

Markmzz

 

[danushka]

You're welcome and thanks for the feedback.

PS: a small modification in the formulas:

In B2

=IF($A2<=1,0,IF($A2<=1.5,1,INT(($A2-1.6)/0.5)-INT(($A2-1.6)/1.5)*2))

In C2

=IF(($A2-$B2*1.5)>0,ROUNDUP(($A2-$B2*1.5),0),0)

Markmzz

thanks a lot. i can't find the different. both are giving same answer. can u explain about your changes. thanks..

 

[markmzz]

thanks a lot. i can't find the different. both are giving same answer. can u explain about your changes. thanks..

In the first formula, I made a modification on the reference type (relative to mixed).

=IF(A2<=1,0,IF(A2<=1.5,1,INT((A2-1.6)/0.5)-INT((A2-1.6)/1.5)*2))
To
=IF([COLOR="#0000FF"]$A2[/COLOR]<=1,0,IF([COLOR="#0000FF"]$A2[/COLOR]<=1.5,1,INT(([COLOR="#0000FF"]$A2[/COLOR]-1.6)/0.5)-INT(([COLOR="#0000FF"]$A2[/COLOR]-1.6)/1.5)*2))

Now the second formula is short. I don't need the first tests to A2. And I made a modification on the reference type (relative to mixed) too.

=IF(A2<=1,1,IF(A2<=1.5,0,IF(($A2-B2*1.5)>0,ROUNDUP(($A2-B2*1.5),0),0)))
To
=IF(($A2-[COLOR="#0000FF"]$B2[/COLOR]*1.5)>0,ROUNDUP(($A2-[COLOR="#0000FF"][/COLOR][COLOR="#0000FF"]$B2[/COLOR]*1.5),0),0)

Markmzz

 

[danushka]

In the first formula, I made a modification on the reference type (relative to mixed).

=IF(A2<=1,0,IF(A2<=1.5,1,INT((A2-1.6)/0.5)-INT((A2-1.6)/1.5)*2))
To
=IF([COLOR=#0000FF]$A2[/COLOR]<=1,0,IF([COLOR=#0000FF]$A2[/COLOR]<=1.5,1,INT(([COLOR=#0000FF]$A2[/COLOR]-1.6)/0.5)-INT(([COLOR=#0000FF]$A2[/COLOR]-1.6)/1.5)*2))

Now the second formula is short. I don't need the first tests to A2. And I made a modification on the reference type (relative to mixed) too.

=IF(A2<=1,1,IF(A2<=1.5,0,IF(($A2-B2*1.5)>0,ROUNDUP(($A2-B2*1.5),0),0)))
To
=IF(($A2-[COLOR=#0000FF]$B2[/COLOR]*1.5)>0,ROUNDUP(($A2-[COLOR=#0000FF]$B2[/COLOR]*1.5),0),0)

Markmzz

thanks for the great formulas. u solved my big issue. i'm going to use these formula with another worksheet.if have time pls help. if not not problem u already done a great job.

in that sheet i have another condition.
formulas i'm going to use in column "C" for 1.5 kg & column "D" for 1.0kg.
column "B" available some text.
my request is, if column "B" available text "SSP" or "PP" then formula answers should display in column "C" & "D"( like before). if column "B" has any other text. no need to update formula answers, that mean column "C" & "D" should be empty.. thanks..

 

[markmzz]

thanks for the great formulas. u solved my big issue. i'm going to use these formula with another worksheet.if have time pls help. if not not problem u already done a great job.

in that sheet i have another condition.
formulas i'm going to use in column "C" for 1.5 kg & column "D" for 1.0kg.
column "B" available some text.
my request is, if column "B" available text "SSP" or "PP" then formula answers should display in column "C" & "D"( like before). if column "B" has any other text. no need to update formula answers, that mean column "C" & "D" should be empty.. thanks..

If I understand correctly what you want, maybe this can helps:

In C2

=IF(OR($B2={"SPP";"PP"}),IF($A2<=1,0,IF($A2<=1.5,1,INT(($A2-1.6)/0.5)-INT(($A2-1.6)/1.5)*2)),"")

In D2

=IF($C2="","",IF(($A2-$C2*1.5)>0,ROUNDUP(($A2-$C2*1.5),0),0))

Markmzz