# formula for point in time averages

Options

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

• ✭✭✭✭✭✭
Options

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, ..................))

• Options

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))

• ✭✭✭✭✭✭
Options

@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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!