Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Revenue per month Formula extending beyond 1 year

Jill Youngberg
Jill Youngberg ✭✭✭
edited 12/09/19 in Archived 2017 Posts

I have a sheet that I have created to forecast revenue per month based on forecasted schedules and dollars/project. Currently this just goes for 1 year, but with projects now starting late Q4 2017 and going into Q1 of 2018, I am needing to revise this formula to add a year definition. Would love if someone could take a look and give me some advice, I would appreciate it!

 

Attached are 2 screen shots - 1 of the overview of the sheet and the other is the detail of the formula. This formula is in the Jan column for the 3873 Job.

Here is the formula:

=IFERROR(IF(AND(Jan$1 >= MONTH($ConStart6), Jan$1 <= MONTH($ConFin6)), $[Total Amount]6 / (MONTH($ConFin6) - MONTH($ConStart6) + 1)), "--")

 

Any help would be appreciated.

Revenue Formula Detail.JPG

Revenue per month overview.JPG

Tags:
· Share on FacebookShare on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee

    Hello,

    This is a neat challenge! FULL DISCLOSURE, I haven't been able to test this function out on my own. I may have added too many parenthesis. Regardless, this is what I have:

    =IFERROR(IF(AND(DATE(Jan$2, MONTH(Jan$1), 1) >= $ConStart6, DATE(Jan$3, MONTH(Jan$1), 1) <= $ConFin6, $[Total Amount]6/ ((MONTH(DATE(Jan$2, MONTH($ConFin6), DAY($ConFin6))) + MONTH(DATE(Jan$3, MONTH($ConFin6), MONTH($ConFin6), DAY($ConFin6)))) - MONTH($ConStart6)) + 1)), "--")

    If you receive an #UNPARSEABLE error, then fiddle with the parenthesis and double-check my cell references to make sure I typed everything correctly.

    To break down your function sans IFERROR:

    IF(AND(Jan$1 >= MONTH($ConStart6), Jan$1 <= MONTH($ConFin6)), ...

    You might change this to be more centered around creating a date out of your Month and Years rows instead. You may also need to consider a years start and years end row, and ensure that they're all formatted the same way with a four digit year. The following example is based on you having a Year End row as row 3:

    IF(AND(DATE(YEAR(Jan$2), MONTH(Jan$1), 1) >= $ConStart6, AND(DATE(YEAR(Jan$3), MONTH(Jan$1), 1) <= $ConFin6, ...

    Now for the second part of your function that actually does the math:

    ..., $[Total Amount]6/ (MONTH($ConFin6) - MONTH($ConStart6) + 1))

    This is a bit trickier, since MONTH will only return values 1-12. If you, hypothetically, have a project that starts 11/05/17 and ends 2/25/18, that's 4 months total. Here's what I came up with:

    ..., $[Total Amount]6/ ((MONTH(DATE(Jan$2, MONTH($ConFin6), DAY($ConFin6))) + MONTH(DATE(Jan$3, MONTH($ConFin6), DAY($ConFin6)))) - MONTH($ConStart6)) + 1))

    This SHOULD give you the total number of ConFin months and allow you to subtract the ConStart month from that. Again, I haven't been able to test this out, I'm purely going on testing it out in my head.

    If this doesn't work out, let me know why and I might be able to think of something else. For reference, here's our complete functions list if you are inspired from my function: https://help.smartsheet.com/functions

    · Share on FacebookShare on Twitter
  • Shaine Greenwood
    Shaine Greenwood Employee

    Also to reiterate because I posted a wall of text:

    The function I've made will require you to add an extra row with a "Year End" at the top so that you have a Year Start and Year End. If you don't want to do that, you could probably get away with changing my ...DATE(Jan$2, ... to reference $ConStart6 and Jan$3 to reference $ConFin6

    Try it out!

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories