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

Options
edited 12/09/19

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.

Tags:

• Employee
Options

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

• Employee
Options

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!

This discussion has been closed.