Formula help to calculate annualized specific to a specific starting month

I have a formula working correctly, (thanks to Paul Newcome) where a month is entered and an amount (Annualized Project Impact ($)) which then populates to the correct months, based on that start date then carries across the remaining months.

Here is the existing formula for that =IF(MONTH([Planned Project Savings Start Date]@row) <= 1, [Annualized Project Impact ($)]@row / (12 - MONTH([Planned Project Savings Start Date]@row) + 1))

What I need to include is a formula that divides the "Annualized Project Impact ($)" by 12 then starts entry in the "Planned Project Savings Start Date" month.

For the below image the correct monthly amount should be $2,500 NOT $5,000


Answers

  • Julie Fortney
    Julie Fortney Overachievers
    edited 05/02/23

    @Lisa Welch

    Including @Paul Newcome

    I think your original formula was set up to split the Annualized Project Impact ($) across the remaining months of the year.

    If you want it to be split across 12 months, starting in the month of the Planned Project Savings Start Date, you could use this formula:

    Jan '23:

    =IF(MONTH([Planned Project Savings Start Date]@row) <= 1, [Annualized Project Impact ($)]@row / 12)

    Feb '23:

    =IF(MONTH([Planned Project Savings Start Date]@row) <= 2, [Annualized Project Impact ($)]@row / 12)

    And so on, just changing the month number in your formula for each column.

    Paul, I'm curious if you have other ideas too!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Julie Fortney Thanks for the tag.


    @Lisa Welch Julie's solution should work for you based on the way I am reading your request.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!