Cross Sheet Formula For Averaging Total Turn Around Time

Marlana K.

I’m looking for a cross-sheet formula in Smartsheet to calculate the average Turn Around Time (TAT) by specific Site Numbers.

Source sheet details:

• Site Number (text/number)Turn Around Time (number; measured in days)

Target sheet details:

I have a row per site and want a formula in the Avg TAT column that returns the average TAT for the Site Number on that row (e.g., from [Site Number]@row ).

What I’ve tried / need help with:

I believe I should use AVERAGE(COLLECT(...)) with cross-sheet references, but I’m unsure of the exact syntax, especially for filtering by Site Number and excluding blanks/zeros.

• I’d like the formula to pull TAT values from the source sheet for the matching Site NumberExclude blanks (and preferably exclude 0 if there are placeholder zeros)Return blank if there are no matching rows

Constraints/Notes:

Turn Around Time is already a numeric field (no date math needed) calculated using a formula :

IF([PA Status/Status Options]@row = "Approved", [Date Site Notified of Approval]@row - [Date PA Initiated]@row , "")

Site Number matches exactly (no partial text).

I have admin permissions to create cross-sheet references.

Could you provide the correct cross-sheet formula and any best practices for handling blanks/zeros?

Thank you!

MARLANA KALINOWSKI

Sr. Business Analysts / Smartsheet Solutions

Strategy & Business Solutions | Genoa Healthcare

Paul Newcome

Give this a try:

=IFERROR(AVG(COLLECT({TAT}, {TAT}, AND(@cell <> "", @cell <> 0), {Site Number}, @cell = [Site Number]@row)), "")