Spread whole number across specific months in a year

[rkobeyer]

Hi


Is it possible to spread whole numbers (not decimals) across a year?

For example if I have a total of 33 and I want to spread it from January to August the formula should give the below result.

J F M A M J J A S O N D
4 4 4 4 4 4 4 5

If 33 is spread evenly from January to December it would look like this:

J F M A M J J A S O N D
2 2 2 3 3 3 3 3 3 3 3 3

With larger numbers skewed toward the end of the year.

I have attached a file with the layout in excel and the expected totals the formula should give for each month.

I have been working on this all day but haven't gotten anywhere! You can see in Sheet 2 my previous attempts if it helps.

Any help I get I will be eternally grateful for!

MonthSpread.xlsm

Cheers,
Russell

[rkobeyer]

Can I pay someone to solve this for me? I tried contacting a few freelance excel workers, but they haven't replied.

[macropod]

I'm assuming you have some other code that loops through the rows and all you're after is the basic logic for working out the #s to populate the 'spread' range. The following code shows how to do that, taking a value entered in an InputBox as the # to spread and the selected range as the cells over which the spreading is to be done:

Sub Demo()
Dim i As Long, j As Long, k As Long, l As Long, x As Long
i = CLng(InputBox("Number to spread?", , 0))
j = Selection.Cells.Count
k = -Int(-i / j)
l = k * j - i
For x = 1 To l
  Selection.Cells(x).Value = k - 1
Next
For x = l + 1 To j
  Selection.Cells(x).Value = k
Next
End Sub