Help with Average Formula

Beth F

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!

Michelle Choate 2

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 2

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.

Beth F

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

Michelle Choate 2

@Beth F Glad I could be of assistance!