Formula for Amount spread across months
I have a sheet with a column for every month...Jan/Feb/Mar/Apr, etc.
I have one column labelled Planned Project Savings Start Date
and another column labelled Annualized Project Impact
Is there a formula that would take "Annualized Project Impact" and spread it out across the months based on the starting month?
So if Annualized Project Impact was 5,000 and start date was August 2023  1,000 would show in Aug, Sept, Oct, Nov Dec
Best Answer

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(.................................................)))
Answers

Are you able to provide a screenshot with sample data for reference?

Hi Paul,

If you are strictly staying inside of 2023 and assuming that it goes from the start month through December, then it is definitely much more manageable.
Give this a try: =IF(MONTH([Planned Project Savings Start Date]@row)<= 1, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
The above would go in the January column. You would adjust the 1 to a 2 for Feb, 2 to a 3 for Mar, so on and so forth.

Thanks Paul,
It is working, however it seem the calculation is off a bit and I can't see where it needs to be adjusted.
For example started date Oct, Impact 60,000 should be Oct 20,000 / Nov 20,000 / Dec 20,000 but it is populating 30,000 for each month?

That looks right to me. 90,000 divided by 3 months is 30,000 per month. 20,000 per month for three months would only add up to 60,000.

The start date entered is Oct and project impact is $60,000.

Sorry about that. I was looking in the wrong money column.
Can you show the formula in the Oct 23 column actually in the sheet similar to the screenshot below?

Here's what I have in each months cells now...
=IF(MONTH([Planned Project Savings Start Date]@row) <= 10, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
You are saying to change it to...
=[Planned Project Savings Start Date]@row +CHAR(10) +[Planned Project Savings Start Date]2 + CHAR (10) + [Planned Project Savings Start Date]3

No. I am asking for a screenshot of your formula actually in the sheet the same way I provided a screenshot of my formula. Don't change the formula. I just want to see it in the sheet.

Ah, I see. Here it is.

Actually... Taking a closer look, it looks as if it is doing that on every row and not just the last one. Try this modification:
=IF(MONTH([Planned Project Savings Start Date]@row) <= 10, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row) + 1))

This is what I have in each month
Jan =IF(MONTH([Planned Project Savings Start Date]@row) <= 1, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row) + 1))
Feb =IF(MONTH([Planned Project Savings Start Date]@row) <= 2, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Mar =IF(MONTH([Planned Project Savings Start Date]@row) <= 3, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Apr =IF(MONTH([Planned Project Savings Start Date]@row) <= 4, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
May =IF(MONTH([Planned Project Savings Start Date]@row) <= 5, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Jun =IF(MONTH([Planned Project Savings Start Date]@row) <= 6, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Jul =IF(MONTH([Planned Project Savings Start Date]@row) <= 7, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Aug =IF(MONTH([Planned Project Savings Start Date]@row) <= 8, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Sept =IF(MONTH([Planned Project Savings Start Date]@row) <= 9, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Oct =IF(MONTH([Planned Project Savings Start Date]@row) <= 10, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Nov =IF(MONTH([Planned Project Savings Start Date]@row) <= 11, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))
Dec =IF(MONTH([Planned Project Savings Start Date]@row) <= 12, [Annualized Project Impact ($)]@row / (12  MONTH([Planned Project Savings Start Date]@row)))

Ok. So try putting the +1 in all of the rest.

Good morning Paul!! Brilliant, it works perfectly now. So I continue my learning, what does the "1" represent to Smartsheet? Thank you for your patience and expertise to help me through this. It is significant!

Paul  another question for this formula? Is there a way to limit the entry to ONLY 2023?
Thanks
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!