formula for point in time averages
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
Answers
-
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, ..................))
-
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))
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!