justmyname009

Q: Automatic calculation at the end of every month

Numbers 3.6.2  I need to have the value of one cell added to the value of another cell at either the end or the beginning of every month. It seems like it should be fairly simple to do but I can't seem to figure out how to make the date part work.

iMac, Mac OS X (10.5.8)

Posted on Sep 18, 2016 7:52 PM

Close

Q: Automatic calculation at the end of every month

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Sep 19, 2016 5:55 AM in response to justmyname009
    Level 5 (5,053 points)
    Mac OS X
    Sep 19, 2016 5:55 AM in response to justmyname009

    Hi name,

     

    I will be easier to provide a relevant answer if you can provide more specifics as to how your table is set up

  • by justmyname009,

    justmyname009 justmyname009 Sep 19, 2016 7:45 AM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 19, 2016 7:45 AM in response to t quinn

    I'm still designing the table. It is a simple budget where I want to add a fixed amount to a total every month so I know how much is available.

  • by t quinn,

    t quinn t quinn Sep 19, 2016 7:49 PM in response to justmyname009
    Level 5 (5,053 points)
    Mac OS X
    Sep 19, 2016 7:49 PM in response to justmyname009

    Hi name,

     

    Something like this?

    Screen Shot 2016-09-19 at 8.49.05 PM.png

    C3= IF(MONTH(B2)<MONTH(B3),C2+D$2,C2)

     

    quinn

  • by justmyname009,

    justmyname009 justmyname009 Sep 19, 2016 11:26 PM in response to t quinn
    Level 1 (4 points)
    iWork
    Sep 19, 2016 11:26 PM in response to t quinn

    Well, sort of. Maybe this will help. Some budget items are paid every month and some are (for example) every 6 months. I want to accumulate those items every month. So If my insurance is due in 6 months, I want to add $50 a month. I want column C to increment by the amount in column B every month.

     

    Thanks for the help,

    Paul

     

    Screen Shot Budget.png

  • by Barry,

    Barry Barry Sep 20, 2016 12:11 AM in response to justmyname009
    Level 7 (32,502 points)
    iWork
    Sep 20, 2016 12:11 AM in response to justmyname009

    Hi jmn,

     

    Do you mean that the amount in C should change on the first of each month?

     

    If so, Quinn ia on the right track.

     

    Screen Shot 2016-09-20 at 12.03.10 AM.png

    Column A contains labels (omitte as not included in the calculations).

    Column B contains the monthly amount budgeted for each category (copied from your example).

    Column C contains the amount budgeted to the end of the current month (calculated by formula below).

     

    The calculations assume the budget is for a calendar year (Jan 1 - Dec 31), and that the year starts January 1.

     

    C2: MONTH(TODAY())×B

    Fill down to the rest of Column C.

    9i filled only to the last row containing data. If filled to the last non-Foote row, the twi blank cells in column C will display 0.00.

     

    B10: =SUM(B)

    Fill right to column C.

     

    Row 10 is a Footer row.

     

    Regards,

    Barry

  • by justmyname009,

    justmyname009 justmyname009 Sep 20, 2016 12:44 AM in response to Barry
    Level 1 (4 points)
    iWork
    Sep 20, 2016 12:44 AM in response to Barry

    Barry,

     

    It's getting better. Your formula multiplies the current month ( 9 ) by & 50. I suppose this will work if I start the budget in January. But I really need it to calculate "next month", not by the number of the month. Could EOMONTH be used in this case somehow?

    Thanks,

    Paul

  • by Barry,

    Barry Barry Sep 20, 2016 2:00 AM in response to justmyname009
    Level 7 (32,502 points)
    iWork
    Sep 20, 2016 2:00 AM in response to justmyname009

    That requires the start date to be declared, that the declared date be the first of the stated month, and that the current date to be adjusted to the first day of the following month (for purposes of calculations).

     

    Screen Shot 2016-09-20 at 1.57.49 AM.png

    C2, and filled down to C9: =DATEDIF(B$1,EOMONTH(C$1,0)+1,"M")×B

     

    Regards,

    Barry