Calculating equal batches

[Jessica231]

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

[#2]

Hi

Could you attach a sample Excel file to help demonstrate what you want to achieve please,

Thanks

Wyn

[#3]

@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

[#4]

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?

[#5]

e.g if you have 75 units they won't fit in a 100 unit batch so what happens then?

[#6]

So some scenarios that may help me understand

if you have 360 units of orange you want to do 3 batches of 120
If you have 300 units of orange you want to do 2 batches of 150 or do you want 3 batches of 100
if you have 330 units of orange = 3 batches of 110 or 2 batch of 165?
If you have 450 units of orange want 3 batches of 150?

[#7]

@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?

[#8]

@Wyn Hopkins 

Sorry I missed that message. I would like to make the biggest batches possible. 

[#9]

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 ?

[#10]

@Wyn Hopkins Thanks for getting back to me. Yes they would be.

[#11 · Accepted solution]

Hi @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

[#12]

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 !!!

[#13]

@Jessica231 

Or like this for multiple products

[#14]

@Wyn Hopkins Thank you very much.