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

Find more posts tagged with

Accepted answers

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

All comments

Paul Newcome

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

Lisa Welch

Hi Paul,

Paul Newcome

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.

Lisa Welch

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?

Paul Newcome

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.

Lisa Welch

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

Paul Newcome

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?

Lisa Welch

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

Paul Newcome

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.

Lisa Welch

Ah, I see. Here it is.

Paul Newcome

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))

Lisa Welch

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)))

Paul Newcome

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

Lisa Welch

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!

Lisa Welch

Paul - another question for this formula? Is there a way to limit the entry to ONLY 2023?

Thanks

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.