Dividing a number over a given number of cells with one or no remainder

[SD40T-2]

I work as a professional pyrotechnician.

For each display event I have a given number of cues, and a time period in minutes.

In the past, I've usually divided the number of cues, but the number of minutes and the result is the number of cues that are fired in a given minute. For example:
150 cues / 25 minutes = 6 cues a minute.

Sometimes, the division doesn't come out nice and even like the example before.

In a recent display event, it was 150 cues during a 22 minutes.  That comes out to 6.8181....

I'm looking for a method using formulas that would display a whole integer number (can't cut the pyrotechnics in half) of cues for each minute, such that it would balance the remainder over the course of the minutes, rather than dump a large remainder at the end.

For instance...

If I use the second example: 150 cues / 22 minutes = 6.8181.... Most minutes are going to be 7 cues, but there will be a few 6 cue minutes - four in this case. A formula that would fill 22 cells, with the spread such that the cues/minute intervals are more or less balanced over the entire display duration.  If four, six cue minute segments are thrown in, then things will balance out evenly.

Can excel do this and balance it out evenly?

Thanks,

DK Speirs

[#2]

@SD40T-2 

This is quite fascinating. I'm pretty sure that you are speaking of fireworks displays, right? Where, may I ask, are you doing this? I have a summer home along the New Jersey (US) shore, so may have seen some of your handiworks (if that's where you operate).

Anyway, although I find your question fascinating, and I love elegant stand-alone (let Excel do it all) solutions, my practical mind wonders if you're not maybe making things more complicated here than you need to.

To stay with your example, what I'm really wondering is whether in your professional judgment, you mightn't want to either front-load the display OR (more likely, based on pyrotechnics displays I've witnessed), back-load it, i.e., have all of those 7 cues per minute sequences loaded into the last four minutes of a 22 minute show. OR some other combination, not quite subject to arbitrary computer heuristic, but rather influenced by your professional judgment plus, perhaps, conversations with the client community???

Bottom line being, why not do just simple math, get the base number of cues for each minute (6 per min, to stay with your example) and manually distribute the remainder for maximum customer satisfaction? That could even mean loading 2 into each of the last two minutes, or .....

[#3]

@SD40T-2 

Let's say the number of cues is in B1 and the number of minutes in B2.

In a cell in row 1, for example D1, enter the formula

=IF(ROW()>$B$2,"",QUOTIENT($B$1,$B$2)+(ROW()<=MOD($B$1,$B$2)))

And fill down to the row whose number is the largest number of minutes you expect, for example to D60.

This version will place the shorter events at the end.

To place them at the beginning:

=IF(ROW()>$B$2,"",QUOTIENT($B$1,$B$2)+($B$2-ROW()<MOD($B$1,$B$2)))

[#4]

I believe this will give you that even distribution you want. Again assume cues in B1 and minutes in B2:
=SCAN(0,SEQUENCE(B2),LAMBDA(carry,p,ROUND(B1/B2*p,0)-ROUND(B1/B2*(p-1),0)))

[#5]

@SD40T-2 

Now that my Excel-Forum-Friends @Hans Vogelaar and @mtarler have given you some great formulas that will do what you asked for, I'm still curious how you might respond to the questions I asked....whether, for professional pyrotechnic reasons, it might actually make more sense to give yourself the freedom to manually adjust the numbers, to front-load, back-load or otherwise modify the "smooth" algorithmic set of numbers.

Without taking away at all from their brilliance.