Formula to calculate an average using 12 most recent form submissions that meet certain criteria

Lauren Dominique

Hello Community! I have a database where 11 different units submit data on a monthly basis related to filled FTEs vs. open FTEs so we can determine the monthly vacancy data. I have built out a form that the managers from each unit use each month to submit their filled FTEs for the previous month. Smartsheet then uses INDEX/MATCH to look across a different database determine how many FTEs that unit is budgeted for the year, and then, based on how many they report having filled, automatically calculates how many FTEs remain open, and therefore what the vacancy rate is for that month.

What I want next is a formula that calculates a 12-month rolling average vacancy rate for each line item submission as well, BUT I am getting stuck on how to tell Smartsheet to base the average off of only the previous 12 entries from that specific department.

I imagine I will need to use AVERAGE(COLLECT, but this is all I've been able to write:

=AVERAGE(COLLECT([Monthly Vacancy Rate]:[Monthly Vacancy Rate], [Helper-Unit]@row, CONTAINS([Unit]@row, @cell)…

Thoughts from the class, please?

Paul Newcome

Do you have an auto-number column in your sheet?

Lauren Dominique

@Paul Newcome I do not but I could add one!