Budget Calculation

I am attempting to calculate the expected spend year to date with a formula that references data in a month column where MONTH(TODAY()) - 1. This works, but when the new year started, it reset all of the expected spend year to date back to 0. Is there away to set a formula that won't

=IF(Month6 = "12", SUM([Budget (January)]6:[Budget (December)]6), IF(Month6 = 11, SUM([Budget (January)]6:[Budget (November)]6), IF(Month6 = 10, SUM([Budget (January)]6:[Budget (October)]6), IF(Month6 = 9, SUM([Budget (January)]6:[Budget (September)]6), IF(Month6 = "8", SUM([Budget (January)]6:[Budget (August)]6), IF(Month6 = "7", SUM([Budget (January)]6:[Budget (July)]6), IF(Month6 = "6", SUM([Budget (January)]6:[Budget (June)]6), IF(Month6 = "5", SUM([Budget (January)]6:[Budget (May)]6), IF(Month6 = "4", SUM([Budget (January)]6:[Budget (April)]6), IF(Month6 = "3", SUM([Budget (January)]6:[Budget (March)]6), IF(Month6 = "2", SUM([Budget (January)]6:[Budget (February)]6), IF(Month6 = "1", [Budget (January)]6, 0))))))))))))

Any thoughts on an alternative formula?

Thanks!

Find more posts tagged with

Formulas

Accepted answers

All comments

Mark Cronk

Hi Nathan,

You need to add a year check if you want last year's spend to remain. Insert a column YEAR unless you have a date field you can use to determine spend year. Then try:

=IF([Year]@row<year(today()), SUM([Budget (January)]6:[Budget (December)]6), IF(Month6 = "12", SUM([Budget (January)]6:[Budget (December)]6), IF(Month6 = 11, SUM([Budget (January)]6:[Budget (November)]6), IF(Month6 = 10, SUM([Budget (January)]6:[Budget (October)]6), IF(Month6 = 9, SUM([Budget (January)]6:[Budget (September)]6), IF(Month6 = "8", SUM([Budget (January)]6:[Budget (August)]6), IF(Month6 = "7", SUM([Budget (January)]6:[Budget (July)]6), IF(Month6 = "6", SUM([Budget (January)]6:[Budget (June)]6), IF(Month6 = "5", SUM([Budget (January)]6:[Budget (May)]6), IF(Month6 = "4", SUM([Budget (January)]6:[Budget (April)]6), IF(Month6 = "3", SUM([Budget (January)]6:[Budget (March)]6), IF(Month6 = "2", SUM([Budget (January)]6:[Budget (February)]6), IF(Month6 = "1", [Budget (January)]6, 0)))))))))))))

Work?

Mark

Nathan Umbriac

Thanks @Mark Cronk. What do I need to add in the Year fields?

Mark Cronk

Hi Nathan,

To use the formula I provides, add text/number column titled [Year]. In the rows add the 4 digit year of the transaction. Your 2020 months should recalculate to last year and not restart with 0 in Jan.

Work?

Mark

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.