#UNPARCEABLE
Hi all,
I am trying to create a formula that does the following:
I would like Smartsheet to pick all the contracts that are "MSA" and "CDA/NDA" from the "Contract Type" column, and that were all signed between July 1st 2022 and June 30th 2023.
Once the conditions above are met, I would like Smartsheet to take the days that it took to draft these contracts based on the criteria above and spit out the average of days.
Essentially I want to know the average of days it took to draft MSAs and CDAs/NDAs at a given year. Can anybody help me create this formula? it's for a sheet summary.
I think this is the closer I got but I keep getting the #UNPARCEABLE error. Can anybody help me?
=IFERROR(CEILING((SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], "CDA/NDA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30)) + SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], "MSA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30))) / (COUNTIFS([Length of Drafting Contract]:[Length of Drafting Contract], "<>", [Contract Type]:[Contract Type], "CDA/NDA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30)) + COUNTIFS([Length of Drafting Contract]:[Length of Drafting Contract], "<>", [Contract Type]:[Contract Type], "MSA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30)))),1),"")
Answers
-
Hi @ben.avraham,
You can trim down your formula a bit to this:
=(SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= DATE(2022, 7, 1), @cell <= DATE(2023, 6, 30)))) / (COUNTIFS([Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= DATE(2022, 7, 1), @cell <= DATE(2023, 6, 30))))
If you're going to want to change the start/end period and you're using this in a sheet summary (for example, to show current financial/company year) then you can add a couple of extra summary fields so it's quicker to update the date range than needing to meddle with the formula to do so.
For example, here I've added 2 date summary fields, Start and End:
With the average being calculated:
=(SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= Start#, @cell <= End#))) / (COUNTIFS([Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= Start#, @cell <= End#)))
This would also enable you to easily do a rolling average if you needed other metrics as you can make the Start/End also be formulas based off today's date if necessary.
Hope this helps, but just post if you have any problems/questions!
-
Wow thank you so much Nick! and for being so quick!
I need to show a few time periods simultaneously on a dashboard so I am not sure the calendar summary fields would be appropriate but this is a cool way that I didn't even realize!
One more question, how can I round up the result to a full number? I kept trying to integrate it into your formula but it didn't work. for example, if I got 30.1 I'd like it to say 31 for the first formula you wrote. If possible.
Again thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!