Pallet consumption calculator

[mypszi]

Hi everyone,

I hope you’ll find the following riddle easy as I’m not a math genius I’d be working on it until the end of the days.

I have started a new role recently and at this place we’re making different mixtures that consist of various components with different bag sizes.
Each mixture has a certain recipe we have to follow.

I would like to work out a calculator in excel that make it possible to work out the consumption of each component by reducing with a constant weight, starting with a known pallet weight through numerous bags finally ending up with a number that is no more sufficient to create a new mixture without starting a new pallet.

I’ll try to write it down by numbers:

starting pallet size: 240 kg
bag size: 20kg
quantity needed for each mixture recipe: 33.2 kg

So, for creating the first mixture I’ll have to take 20kg + 13.2kg of an additional bag. I will have 12.8 kg leftover for the next recipe but for this I will need a full 20kg bag plus 0.4 kg from the 3rd one, and that results of having 19.6 leftover and so on, and so on…

My aim is to reduce the time wastage of my team spending on the calculations that takes ages in some cases as these mixtures are containing 20+ components sometimes.

I’m pretty sure it’s not something that was never done however I’m not that good at functions.

Hope it’ll make someone excited and helpful!

Thanks!

[Beamernsw]

Hi mypszi, I hope this helps. Not exactly sure what you want since you didn't attach a sample sheet to play with, but hopefully this will at least give you some ideas going forward.

So, the first row (first recipe) has different formulas to the rest. I put a starting "20" in C3, then D3 to N3 have the same formula.
All the other cells have the same formula in every cell for every other recipe.

Again, I hope this helps in some way.

Edit: slightly changed formulas, so you can put the remaining part of pallet one into the start of pallet two.
Edit 2: forgot to add column for remainder.

[mypszi]

Thank you, for an idea it’ll do, I will upload a draft of how I imagined at the beginning.

[Beamernsw]

Hi, found an error. Occasionally can be an eigth recipe.



Edit: BTW, I don't know if you can cahnged forums, but if you can, changing this post to "excel-formulas-and-functions" instead of this subgroup will probably get you many more answers.

[mypszi]

Well, I am very impressed with the job you have done mate and I can tell this is a huge help already! Well done, will complete this with my thoughts once I get around excel, all the best until then!