auto weight calculating

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 package to fill that A2 cell weights with less different. always first priority need to give to 1.5 kg, 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..

=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.7 kg. best option is first one.

13.8
1.5 kg -9
1 kg -1
pls check the sample sheet..
so i don't no this is can manage with excel formula or not. so pls help me. thanks a lot...

Hi

First I would change B1 and C1 to contain actual numbers instead of text, if you want to show the text "kg" it can be done with a custom number format.
Then use this formula in b2:
ROUNDDOWN((A2+0,5)/B$1,0)

And use this in c2:
ROUNDUP((A2+0,5)-(B$1*B2),0)

Hi, please disregard my answer. I realized it doesn't work very good for packages less than 1 kg.

Hi,

And does the combined weight need to be greater than the figure in column A+0.5kg? Or can it be less than also?

For example, using your example above of 14.3kg, if I could achieve a combined weight of 14.2kg (which is closer to 14.3kg than your 14.5kg), would that be acceptable? Or is it not valid as it is less than the required amount?

Regards

If I understand the problem, it is similar to this one: http://www.excelforum.com/excel-form...ml#post2957674

As in the previous case, I prefer to solve a problem like this so that I understand the math/algebra, then figure out how to program that solution into Excel.

If I've understood, it looks like we want to solve the equation B*1.5+C*1>=A+0.5 Rearranged B*1.5+C*1-A-0.5>=0 and a minimum.

As in the previous case, Solver might be a good way to solve a problem like this. Set up the spreadsheet to calculate the total weight at some assumed values for B and C, then call Solver and tell it to minimize the difference by changing B and C subject to the appropriate constraints (b and C are integers >=0 and the difference >=0).

If you don't want to use Solver, you will probably have to program in your own trial and error algorithm.

I think that this works. I amended B1 and C1 so that they are numbers that could be used in calculations.

I will take a look at this and see what I can do. I know upon seeing what I gave you that something is missing but right now I can't see what it is.

After trying many different scenarios, I created a helper column (which is hidden) just to keep the formula as simple as possible. I got "stalled" on the possibilities of where having 2 1Kg weights is necessary as opposed to having 1 1.5 Kg and 1 1 Kg. The only situation where 2 1 Kg packages makes sense is where the total weight is up to 2 Kg.

You're welcome and thank you for the feedback.