formula for point in time averages

DHR Temp Assignment Team

Hi, I have a sheet that has averages for various things such as how long it takes from date a job control closes to when interviews are held. I have to report in 2 week increments - by the individual area -- for example district 1 held interview 01/01/24 thru 01/15/24, then again on 01/16/24 thru 01/31/24 -- I need to show how many interviews were held inbetween these two sets of dates -- of cousre it's for all our districts (12), so I'm first pulling an average of all of the interviews and then I'd like to break out the averages by the weeks -- s =AVERAGEIF(AND(District:District = "3", [Days to Submit FJO Request from FFD]:[Days to Submit FJO Request from FFD], [Round 1: 1st Interview Date]:[Round 1: 1st Interview Date], >=DATE(2024, 1, 1), <=2024, 1, 15) -- and I have 7 metrics (averages) for the hiring process that I have to provide ... any help or advice would be appreciated

Paul Newcome

Try an AVG/COLLECT combo. AVERAGEIF only allows for a single range/criteria set, but the COLLECT function allows for multiple range/criteria sets.

=AVG(COLLECT({Range To Average}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria, ..................))

BraylenStanton

You can use the AVERAGEIFS function to calculate the average between two dates. For example, to calculate the average number of interviews between two sets of dates for a particular district, you can use the following formula: '=AVERAGEIFS(Interviews, District, "District 1", InterviewDates, ">="&DATE(2024,1,1), InterviewDates, "<="&DATE(2024,1,15))

Paul Newcome

@BraylenStanton Your formula looks like it may be for Excel. It is definitely not for Smartsheet. Not only does Smartsheet not have the AVERAGEIFS function, it also uses different syntax.