Help with Average Formula
Hi there - I'd like to ask for a hand to calculate an average formula.
This is a cross sheet formula where I want to aggregate an average time duration by Assigned To, by quarter. (I also have instances where my time duration is zero - I think that was causing me a divide by zero error).
Metrics sheet/destination:
Source Sheet:
Thanks in advance for any help you can provide!
Best Answer
-
If you wanted to update every column's formula, you could try this
=SUMIFS({Duration},{Assigned To}, [Real Estate Lead]@row, {Date}, AND(@cell > DATE(2023,01,01), @cell < DATE(2023,03,31)))/COUNTIFS({Assigned To}, [Real Estate Lead]@row, {Date}, AND(@cell > DATE(2023,01,01), @cell < DATE(2023,03,31)))
Otherwise, you could also create a Month column, a Quarter column, a Year Column, and then a Q+Y column and make it easier on yourself to not have to update those dates all the way across.
So the Year column would just be =YEAR([Date: determines quarter]@row) and then a Month column =MONTH([Date: determines quarter]@row).
Then the Quarter column would be =IF(OR(Month@row = 1, Month@row = 2, Month@row = 3), Q1, IF(OR(Month@row = 4, Month@row = 5, Month@row = 6), Q2, IF(OR(Month@row = 7, Month@ row = 8, Month@row = 9), Q3, IF(OR(Month@row = 10, Month@row = 11, Month@row = 12), Q4))))
Then for the Q + Y Column would be =Quarter@row + " " + Year@row
Then you would create a top row in the formula in the Average Duration By Quarter sheet that would match the column names, then the formula for the first column would simply be:
=SUMIFS({Duration},{Assigned To}, [Real Estate Lead]@row, {Q + Y}, [Q1 2023]$1)/COUNTIFS({Assigned To}, [Real Estate Lead]@row, {Q + Y}, [Q1 2023]$1)
And then you could drag it across and have it update itself.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
Answers
-
If you wanted to update every column's formula, you could try this
=SUMIFS({Duration},{Assigned To}, [Real Estate Lead]@row, {Date}, AND(@cell > DATE(2023,01,01), @cell < DATE(2023,03,31)))/COUNTIFS({Assigned To}, [Real Estate Lead]@row, {Date}, AND(@cell > DATE(2023,01,01), @cell < DATE(2023,03,31)))
Otherwise, you could also create a Month column, a Quarter column, a Year Column, and then a Q+Y column and make it easier on yourself to not have to update those dates all the way across.
So the Year column would just be =YEAR([Date: determines quarter]@row) and then a Month column =MONTH([Date: determines quarter]@row).
Then the Quarter column would be =IF(OR(Month@row = 1, Month@row = 2, Month@row = 3), Q1, IF(OR(Month@row = 4, Month@row = 5, Month@row = 6), Q2, IF(OR(Month@row = 7, Month@ row = 8, Month@row = 9), Q3, IF(OR(Month@row = 10, Month@row = 11, Month@row = 12), Q4))))
Then for the Q + Y Column would be =Quarter@row + " " + Year@row
Then you would create a top row in the formula in the Average Duration By Quarter sheet that would match the column names, then the formula for the first column would simply be:
=SUMIFS({Duration},{Assigned To}, [Real Estate Lead]@row, {Q + Y}, [Q1 2023]$1)/COUNTIFS({Assigned To}, [Real Estate Lead]@row, {Q + Y}, [Q1 2023]$1)
And then you could drag it across and have it update itself.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
@Michelle Choate 2 Thank you so much! I'm loving the hack to use a division formula to create an average. I was finding limitations in the AVERAGEIF formula as I couldn't set the parameters I needed. This is working great for me know and I really appreciate your help!
-
@Beth F Glad I could be of assistance!
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!