#UNPARCEABLE

ben.avraham

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),"")

Nick Korna

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!

ben.avraham

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!