# Thread: Distribute An Amount Over 12 Periods (03)

1. ## Distribute An Amount Over 12 Periods (03)

I recall reading a thread on distributing an amount evenly over a 12 month period. Unfortunately I can not locate it via a search.

Example:
Total amount to distribute = 12,000
Amount to distribute to each of the 12 periods = 1,000

Thanks,
John

2. ## Re: Distribute An Amount Over 12 Periods (03)

Divide by 12?

3. ## Re: Distribute An Amount Over 12 Periods (03)

That's a good one. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

I thought I saw code on how to distribute the amount over 11 months and push the 12 month so that it balances.

I'm working on my version and thought I'd post here to see if someone remembered seeing it in the Lounge.

John

4. ## Re: Distribute An Amount Over 12 Periods (03)

Perhaps you could explain what you want to accomplish. Without knowing that, my admittedly facetious reply is the only answer I can give.

5. ## Re: Distribute An Amount Over 12 Periods (03)

Hans,

This is what I was looking for:

Sub DistributeAmount()
Dim oAccum, oAmount, oAmounDistr As Integer
oAmount = 11000
oAmountDistr = Round(oAmount / 12, 0)
oAccum = 0
For i = 1 To 12
Select Case i

Case 1 To 11
ActiveCell.Offset(0, i - 1) = oAmountDistr
Case 12
ActiveCell.Offset(0, i - 1) = oAmount - oAccum
End Select

oAccum = oAccum + oAmountDistr
Next i
End Sub

You have a great sense of humor, <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
John

6. ## Re: Distribute An Amount Over 12 Periods (03)

If 12,000 is in cell A1, and you want to distribuite it over B1:M1, then put this formula in cell B1 and copy it to C1:L1.

<code>
=ROUNDDOWN(\$A1/12,2)
</code>

Then put this formula in cell M1.

<code>
=ROUND(A1-SUM(B1:L1),2)
</code>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•