• 461K Members
• 12.5K Online
• 558K Conversations
SOLVED

## Calculating equal batches

Occasional Contributor

# Calculating equal batches

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.

13 Replies

# Re: Calculating equal batches

Hi

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

Thanks

Wyn

# Re: Calculating equal batches

@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

# Re: Calculating equal batches

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?

# Re: Calculating equal batches

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

# Re: Calculating equal batches

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?

# Re: Calculating equal batches

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

# Re: Calculating equal batches

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

# Re: Calculating equal batches

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 ?

# Re: Calculating equal batches

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

Solution

# Re: Calculating equal batches

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

# Re: Calculating equal batches

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

# Re: Calculating equal batches

Or like this for multiple products

# Re: Calculating equal batches

@Wyn Hopkins Thank you very much.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies