Average formula using column reference and weeknumber reference

mccaffhm

Hello, I am trying to create a formula for my smart sheet which will tell me the average in transit time for our shipments in week number 1. To do this I have a week number column which automatically updates at the beginning of the week so I can pull the data for my metrics. Currently I have been able to pull the data by refereeing the specific range of cells in each column for the week. This is the equation I have currently.

=AVG(COLLECT([Date/Time Difference]9:[Date/Time Difference]67, weeknumber9 : weeknumber67, "1"))

So how do I automate this and have it reference the entire column for Date/Time Difference and week number.

additionally once this is done, I would like to fund the average from the date/ time difference column and filter by type of shipment.

NicoLHC

@mccaffhm please try this =AVG(COLLECT([Date/Time Difference]:[Date/Time Difference], [Week Number]:[Week Number], "1"))

mccaffhm

Hey NicoLHC,

I just gave it a try and it returned "unparseable" it seems like when I don't include a row reference the formula doesn't recognize the column (its not becoming highlighted)

SSFeatures

Hi mccaffhm,

Can you add a screenshot of your sheet (after removing sensitive information)? I think I have an idea but I want to make sure that I understand the sheet correctly.

Thanks,

SSFeatures