Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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

    Does anyone recall the link?

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Distribute An Amount Over 12 Periods (03)

    Divide by 12?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    Legare Coleman

Posting Permissions

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