Excel: Need some math help

Factual Questions
1

Hope I can explain this well enough:

I have a starting amount, say 17,300. I want to divide it as evenly as possible among a number of slots. Thing is the number of slots may be anywhere from 1-6 independent slots and there is no way to tell ahead of time how many I can use (as other things may have them in use and thus be unavailable).

Here’s the trick though. The slots have to be filled in amounts of 100 units (no way around that). Rounding to nearest 100 has already occurred to get the 17,300 figure and I do not have stock to just add more for an even multiple of 1-6 (as the case may be).

It is ok if most are even and the last few are 100 or 200 short of the first ones as long as at the end they all total to 17,300. Just want them to be as near as possible so they finish roughly together.

This one has been giving me fits. Hoping solution is one of those easy ones I am just overlooking.

Any ideas?

2

will this work?

=ROUNDDOWN(17300/n,-2) where n = number of slots.

You can put the extra “widgets” (in increments of 100) into any of the slots.

3

Nevermind

4

I think having units of 100 complicates things, so let’s just divide your numbers by 100. There probably are clever ways of doing it, but there are also some simple iterative methods. Here’s one I knocked together in a Google spreadsheet (you’ll need to View/Show formula bar to see the formulas):

5

And here’s the same thing except you can specify unit size:

6

Do you care about which slots you get to use, or just how many? Do you have a preference for which items go in which slots, or are they completely interchangeable? This is a pretty straightforward assignment problem either way (although I don’t know that I’d want to try to solve it in Excel).

7

Instead of rounding the whole number by the total, have 6 cells. The first cell is the total divided by the number of slots, rounded to 100. The second one has an “if” statement, i.e. if the slots are not greater than one, it’s blank. Otherwise it’s equal to the total minus the amount in the first cell, divided by the number of slots minus one, rounded to 100. And so on for the remaining cells.

8

Do not care which slots (all are identical). Just care how many I use which may be from 1-6 depending on other (unrelated) factors.

No preference for what goes where. They are identical items.

I agree not sure I want to tackle making that formula work in Excel. :slight_smile:

9

Hmm…might work.

Will give it a go when I get home.

10

If you can’t use extra cells for intermediate values, like Xim’s solution, this set of formulas might help you get started.

[B1]=FLOOR($A1/6,100)+IF((MOD($A1,600)>0),100)
[C1]=FLOOR($A1/6,100)+IF((MOD($A1-100,600)>0)*AND($A1-FLOOR($A1/6,100)*6-100>0),100,0)
[D1]=FLOOR($A1/6,100)+IF((MOD($A1-200,600)>0)*AND($A1-FLOOR($A1/6,100)*6-200>0),100,0)
[E1]=FLOOR($A1/6,100)+IF((MOD($A1-300,600)>0)*AND($A1-FLOOR($A1/6,100)*6-300>0),100,0)
[F1]=FLOOR($A1/6,100)+IF((MOD($A1-400,600)>0)*AND($A1-FLOOR($A1/6,100)*6-400>0),100,0)
[G1]=FLOOR($A1/6,100)+IF((MOD($A1-500,600)>0)*AND($A1-FLOOR($A1/6,100)*6-500>0),100,0)

where your starting value is stored in A1 and you have 6 target cells available (with the target cells assumed to be in the same row). This should be a general solution for the 6-cell case. If you had 5 cells, you would divide the starting value by 5 in the FLOOR functions, 500 in the MOD functions, and only cycle the subtraction from 0 to 500.

The trick will probably be finding a consistent way to determine how many slots are available. If the unavailable slots are going to be filled with something consistent, you can just use a series of nested IF functions to determine which set of formulas to use:
[B1]=IF(<C1 contains whatever>, $A1,(IF<D1 contains whatever>,FLOOR($A1/2,100)+IF((MOD($A1,200)>0),100),(IF<E1 contains whatever>,FLOOR($A1/3,100)+IF((MOD($A1,300)>0),100),…

I hope that helps. Without knowing more about the sheet in question, it’s about the best I can think of.