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
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.
Comments
-
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
-
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!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives