How to get average # Workdays by Contract Type

Joan156

I'm stumped and hoping the community can help.

Within Sheet Summary of my master grid, I am trying to create an average formula that calculates the average # of workdays by contract type within a specific month--in this case, June 2023. This data will be used in a Monthly Dashboard.

For example:

"NDA/CDA (Non-Disclosure Agreement)" should equal 8.25

"SOW (Statement of Work)" should equal 3.333

Carson Penticuff

=AVG(COLLECT([# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed], [Contract Type]:[Contract Type], "NDA/CDA (Non-Disclosure Agreement)", [Today's Date]:[Today's Date], MONTH(@cell) = 6, [Today's Date]:[Today's Date], YEAR(@cell) = 2023))

You will just need to substitute in Month/Year/Contract Type as needed.

Carson Penticuff

Awesome! I'm glad it's working for you. 👍️

Carson Penticuff

=AVG(COLLECT([# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed], [Contract Type]:[Contract Type], "NDA/CDA (Non-Disclosure Agreement)", [Today's Date]:[Today's Date], MONTH(@cell) = 6, [Today's Date]:[Today's Date], YEAR(@cell) = 2023))

You will just need to substitute in Month/Year/Contract Type as needed.

Joan156

Sadly I'm receiving Invalid Data Type message.

Carson Penticuff

Is the [Today's Date] column formatted as a date?

Joan156

Yes, it is formatted as a date. Do I need to change it?

Carson Penticuff

No, for the YEAR() and MONTH() functions to work, the format must be a date, so this is correct. The other option is that the entries in the [# Workdays from Receipt to Fully Executed] are text instead of integers.

Here is an experiment to try. Use this formula and it should return the number of entries in the [# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed] column that are formatted as text. The expected result would be 0.

=COUNTIF([# Workdays from Receipt to Fully Executed]:[# Workdays from Receipt to Fully Executed], ISTEXT(@cell))

Joan156

Still Invalid Data Type.

Is it possible that since the # Workdays from Receipt to Fully Executed column includes a formula it is impacting the Sheet Summary?

I appreciate your help. I may have to rethink my project.

Joan156

Carson:

You were right all along. I had other extraneous data that impacted the original formula.

It works perfect.

Thank you!

Carson Penticuff

Awesome! I'm glad it's working for you. 👍️