Forum Discussion
Dividing a number in equal parts, not exceeding 1800 and in multiples of 50 or 100
Hello there,
I am looking for an excel formula to help me resolve the problem statement provided below:
I manufacture steel pipes and have to cut them in such a way that there are minimum number of joints. The max. Length of the pipe can be 1800 mm. I have given a sample spreadsheet below to help you explain certain scenarios. A few things to keep in mind are:
- Max. Length cannot exceed beyond 1800
- The length has to be in multiples of 50 or 100
- The number of pieces have to be minimum. For example, 3000 can be divided into 1800 + 600 + 600 but this will result in 3 pipe pieces. What I need is 1500 + 1500.
| size | Break up | |||||
| 1900 | 950 | 950 | ||||
| 2000 | 1000 | 1000 | ||||
| 2500 | 1250 | 1250 | ||||
| 3000 | 1500 | 1500 | ||||
| 4000 | 1800 | 1100 | 1100 | |||
| 4200 | 1800 | 1200 | 1200 | |||
| 5000 | 1800 | 1600 | 1600 | |||
| 5200 | 1800 | 1700 | 1700 | |||
| 5400 | 1800 | 1800 | 1800 | |||
| 5600 | 1800 | 1800 | 1000 | 1000 | ||
| 5800 | 1800 | 1800 | 1100 | 1100 | ||
| 7000 | 1800 | 1800 | 1800 | 800 | 800 | |
| 8000 | 1800 | 1800 | 1800 | 1300 | 1300 | |
| 8800 | 1800 | 1800 | 1800 | 1700 | 1700 | |
| 9400 | 1800 | 1800 | 1800 | 1800 | 1100 | 1100 |
I did take some help from the discussion mentioned below, but I am still struggling to fulfil my requirement https://techcommunity.microsoft.com/t5/excel/divide-a-quantity-as-whole-number-among-multiple-cells/m-p/154139
It will be amazing if you folks out there could help me 🙏🏼
Willy Lau JMB17 SergeiBaklan It would mean a lot if you can also review the problem statement mentioned above.