Divide a value in excel by a set of preset values to find out how many of each are needed-CodePudding

I am curious if there is a way to make my life easier. In excel I am producing a total value, say 750 and need to find out how many orders of pipe I need from values of 50,100,200,250,500. Is there anyway to have excel take a value and then return how many of each of these numbers I would need, so for the 750 case 1 500 and 1 250?

Currently the solution is just worked out in my head

CodePudding user response：

Assuming you want to try to fit pipes in decreasing order of size,and that you have access to the required functions, you can use Reduce as demonstrated

As pointed out by @Jos Woolley, this may not give you the answer you want if the total is something like 749. It will fit as many values in as possible and give a result 700 (remainder 49). You could fix it perhaps by rounding up to the next multiple of 50.

CodePudding user response：

Not sure I understand the question but lets try.

if you have 1 450 to divide, have a formula that divides 1 450 with you highest lenght (750) and then round it down.

so the formula would be something of the line: = rounddown(1 450 / 750; 0)

you will then get the answer that you need 1 of the length 750.

then keep the info about how much length you have remaining. So a formula like: =1 450 - 750 * [the answer from previous formula = 1]. this would sum to 700.

then start over with the same thing, but divide 700 with 500 (second largest size).

CodePudding user response：

Your question is extremely difficult: one might think for this easy solution, starting with value_begin:

amount_of_500 = value_begin DIV 500; // integer division
temp = value_begin - 500 * amount_of_500;
amount_of_250 = temp DIV 250; // again integer division
temp = temp - 250 * amount_of_250;
amount_of_200 = temp DIV 200; // again integer division
temp = temp - 200 * amount_of_200;
...

However, this will not work because of the value 200, which is far too close to 250: just start with value_begin equal to 400 (algorithm solution : 250 100 50, while best solution : 200 200).

Are you sure you need both 200 and 250 as possible numbers to divide by? If yes, you might have a serious problem getting this implemented.