Figuring out the Average within a Week

Matt Scroggins

I am currently trying to figure out how to average the turn time for our projects. In Excel all you have to do is select the number of days each project took for that week and it shows you the average. I can't seem to figure out how to do that in Smartsheet. I have a column for Week Number. The total is also based on the Status field. If the status field has a "Z" in it then it will average the projects based off of those rows. I have circled the columns that will be involved in the formula.

Hope this makes sense

Genevieve P.

Hi @Matt Scroggins

You can get an Average based on multiple criteria by using AVG(COLLECT together. The COLLECT function will figure out what rows to look at, listing the range to Average at the beginning.

Try this:

=AVG(COLLECT(Duration:Duration, Status:Status, "Z", [Week Number]:[Week Number], 1))

This would be the format if you were building the formula within the sheet, for example in a column or in a Sheet Summary field. If you're building out this formula to calculate averages in a separate sheet, you would need to use cross-sheet references to find the columns:

=AVG(COLLECT({Duration Column}, {Status Column}, "Z", {Week Number Column}, 1))

If this other sheet had a column listing the week numbers in order, then you could just use a cell reference instead of actually writing out the week number as 1, 2, 3, etc in the formula:

=AVG(COLLECT({Duration Column}, {Status Column}, "Z", {Week Number Column}, [Week Number]@row))

Let me know if this makes sense or if you have any questions!

Cheers,

Genevieve

Genevieve P.

Hi Matt,

I'm glad to hear that worked!

Yes, you can just add another range and another criteria into the COLLECT function, you've got it... ex, something like this:

=AVG(COLLECT({Duration Column}, {Status Column}, "Z", {Week Number Column}, [Week Number]@row, {Revision Column}, >1))

Genevieve P.

Hi @Matt Scroggins

You can get an Average based on multiple criteria by using AVG(COLLECT together. The COLLECT function will figure out what rows to look at, listing the range to Average at the beginning.

Try this:

=AVG(COLLECT(Duration:Duration, Status:Status, "Z", [Week Number]:[Week Number], 1))

This would be the format if you were building the formula within the sheet, for example in a column or in a Sheet Summary field. If you're building out this formula to calculate averages in a separate sheet, you would need to use cross-sheet references to find the columns:

=AVG(COLLECT({Duration Column}, {Status Column}, "Z", {Week Number Column}, 1))

If this other sheet had a column listing the week numbers in order, then you could just use a cell reference instead of actually writing out the week number as 1, 2, 3, etc in the formula:

=AVG(COLLECT({Duration Column}, {Status Column}, "Z", {Week Number Column}, [Week Number]@row))

Let me know if this makes sense or if you have any questions!

Cheers,

Genevieve

Matt Scroggins

That worked! Thank you so much Genevieve!

One thing that I forgot to include is that I need to use the Revision column as well. Could I include that in the COLLECT function along with the other areas that I am searching in order to filter it down even more?

Genevieve P.

Hi Matt,

I'm glad to hear that worked!

Yes, you can just add another range and another criteria into the COLLECT function, you've got it... ex, something like this:

=AVG(COLLECT({Duration Column}, {Status Column}, "Z", {Week Number Column}, [Week Number]@row, {Revision Column}, >1))

Matt Scroggins

Thank you!