Average number of days while referencing another sheet

Laura999

Hi All,

I'm hoping someone can assist with this …

I have a master sheet containing many columns including "Request Topic", "Status", "Net Work Days Req. Assign",

I would like to pull KPIs into a separate sheet, calculating: The average number of days the Request Topic "MOU" has been in Status "Needs Assignment" for a specific month. (Can this be pulled to a sheet, or do I need to pull it to a report?)

I already have the number of days the record took to assign with "Net Work Days Req. Assign", I just need to pull the average over the period of 1 month.

What formula would you suggest because anything I've tried I get #UNPARSEABLE as the response.

Thanks!!!

Paul Newcome

Would you need the number to be split across multiple months? For example, if the number of days is 97, would your metrics sheet need to account for only a certain number of days within the first month, a certain number of days within the second month, so on and so forth, or would you just take the full 97 days and attribute it to either the start or end month?

Laura999

It would always be from the first day of the month to the last day of the month. (1-31)

Paul Newcome

You are going to need something along the lines of

=AVG(COLLECT({Number Of Days Column}, {Topic Column}, @cell = "MOU"), {Date Column}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2025))