Jun 12 2019 04:52 AM
Hi guys,
I have a question regarding the formula to use in order to calculate equal batches.
To be clearer, my order being different every day and my batches needing to be equal and containing between 100 and 200 units, an automated formula would save me a lot of time in calculating the numbers every day.
Hope some of you will be able to help.
Thank you in advance
Jun 12 2019 05:08 AM
Jun 12 2019 05:16 AM
@Wyn Hopkins Thanks ! I hope this helps.
Basically my batches of red and blue always consist of 200 or 190 units so it quite easy to calculate.
On the other hand orange and purple batch size varies depending on my order. The important thing is for my batches to consist of between 100 and 200 units and be equal. I hope that makes sense.
As you probably guessed I m very new to this and not exactly well versed in excel terminology. I hope I provided the necessary info. Thanks again
Jun 12 2019 05:35 AM
So there's quite a few calculations going on here that I'll need some help to understand
Why does the Quantity represent. Quantity of what?
The yellow cell is taking the total Quantity and splitting it by 200 items to get the number of batches of Red items
The Green cell is splitting it by 210 items (you mention 190 in your post) to get the number of batches of Blues
I'm not clear on what you want the formula to do for Orange or Purple?
Jun 12 2019 05:38 AM
Jun 12 2019 05:50 AM
Jun 12 2019 05:54 AM
@Wyn Hopkins So the quantity represents the amount of tubs ordered. I calculate the quantities in 142g tubs and 170g tubs as this is the unit of measurement for my batches.
My apologies you are right for the blue it is batches of 210 not 190.
The formula for the orange and purple are meant to calculate the minimum amounts of equal sized batches (each batch being between 100 and 200 tubs of 170g) depending on the order. If the order is lower than 100 than a batch of 100 wi still have to be made. Does that make sense?
Basically I want the system in the case of me having an order of 300 to make 2 batches of 150. Does that make sense?
Jun 12 2019 05:57 AM
Sorry I missed that message. I would like to make the biggest batches possible.
Jun 13 2019 03:43 AM - edited Jun 13 2019 03:44 AM
sorry one more question, in your attached example you had 8.5 and 5.3 batches. So do your orange and purple batches need to be whole numbers ?
Jun 13 2019 03:44 AM
@Wyn Hopkins Thanks for getting back to me. Yes they would be.
Jun 13 2019 04:15 AM
SolutionHi @Jessica231
So there's probably a more elegant formula way of doing this but I've approached it using a table
Let me know if it works for you
Jun 13 2019 04:20 AM
OMG this is perfect thank you sooooo much! I wish I could send you cookies ! ahahhaah
I have no idea about what the very intricate formula is but it is exactly what I wanted. Eternally grateful !!!
Jun 13 2019 05:23 AM
Jun 13 2019 04:15 AM
SolutionHi @Jessica231
So there's probably a more elegant formula way of doing this but I've approached it using a table
Let me know if it works for you