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]

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

wow amazing, i thought this work not possible, but u done it. thanks a million. this is the what i want, thanks again..

 

[markmzz]

wow amazing, i thought this work not possible, but u done it. thanks a million. this is the what i want, thanks again..

I'm glad that your problem was solved and thank you for the feedback.

Markmzz

 

[alvin-chung]

Amazing formula, Markmzz!
Could you share the logic behind INT(($A2-1.6)/0.5)-INT(($A2-1.6)/1.5)*2) so that I can utilize it for similar situation? Thanks

 

[markmzz]

Amazing formula, Markmzz!
Could you share the logic behind INT(($A2-1.6)/0.5)-INT(($A2-1.6)/1.5)*2) so that I can utilize it for similar situation? Thanks

Hi Alvin-chung,

I'll try to explain the first formula:

For 0.1kg to 1.5kg I discovered theses values (only for 1.5kg column):

0;0;0;0;0;0;0;0;0;0;1;1;1;1;1

So I created the first part of the formula:

=IF($A2<=1,0,IF($A2<=1.5,1,

Then I discovered standards from 1.6 kg up to 3.0 kg, 3 kg up to 4.5 kg, 4.0 kg up to 6.0 kg and so on. See the examples below (only for 1.5kg column):

1.6kg to 3.0kg - 0;0;0;0;0;1;1;1;1;1;2;2;2;2;2

3.1kg to 4.5kg - 1;1;1;1;1;2;2;2;2;2;3;3;3;3;3

4.6kg to 6.0kg - 2;2;2;2;2;3;3;3;3;3;4;4;4;4;4

So, I created the formula following those standards. See the second part of the formula below:

INT(($A2-1.6)/0.5)-INT(($A2-1.6)/1.5)*2))

I hope that this helps.

Markmzz

 

[markmzz]

Hi Alvin-chung,

A small modification in my last post:

Then I discovered standards from 1.6 kg up to 3.0 kg, 3.1 kg up to 4.5 kg, 4.6 kg up to 6.0 kg and so on. See the examples below (only for 1.5kg column):

1.6kg to 3.0kg - 0;0;0;0;0;1;1;1;1;1;2;2;2;2;2

3.1kg to 4.5kg - 1;1;1;1;1;2;2;2;2;2;3;3;3;3;3

4.6kg to 6.0kg - 2;2;2;2;2;3;3;3;3;3;4;4;4;4;4


Markmzz

 

[alvin-chung]

Hi Markmzz, appreciate for sharing the process-thought.
It's an eye-opening for me on your formula approach!