Formula for Amount spread across months

Paul Newcome

Happy to help. 👍️

Here is a breakdown of that portion of the formula:

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

We take the $ amount and divide it by (12 minus the month in the date plus 1). So in the case of October we get

12 - 10 + 1

which gives us 3 which is the same number of cells that will have an output which means the $$ amount is being divided by the same number of months that will have an output in them.

Without the +1, we were dividing by 2 which is why we were getting the wrong number (increased by exactly one entry amount on every row).

I'm not sure I understand what you're asking with restricting to only 2023. Do you mean it will only output if the date is within 2023, if so, you would start each formula off with:

=IF(YEAR([Planned Project Savings Start Date]@row) = 2023, IF(MONTH(.................................................)))

Lisa Welch

Paul - Just circling back to say thanks again as this solution is working perfectly!! Appreciate the help you provided on this one.

Lisa Welch

Hi Paul - looking for an adjustment to the original formula above. The 2023 Calendar Savings amount is calculating correctly and the Annualized Savings is calculating correctly.

The monthly however is calculating based on the start month but I need it to calculate based on ALWAYS 12 months. So the Annualized Project Impact is over 12 months.

In this example the 2023 Calendar Savings of $66,667 is correct and the Annualized savings of $100,000 is correct however the monthly amount May to Dec each month should be $8333.33.

Current formula is

Thanks,

Lisa Welch

Is it a matter of combining these two formulas?

=[Annualized Project Impact ($)]@row / 12

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