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!

Tags:

Best Answer

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭
    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

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭
    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!

  • Beth F
    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
    Michelle Choate 2 ✭✭✭✭✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!