Revenue per month Formula extending beyond 1 year

Jill Youngberg

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.

Shaine Greenwood

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

Shaine Greenwood

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!