Calculating an Average based upon a value in other column

Kandee Evans
Kandee Evans ✭✭
edited 12/09/19 in Formulas and Functions

I have a calculation to calculate the number of days between two columns.

=IFERROR(NETWORKDAYS([Date Submitted]41, [Go Live Date]41), " ")  It works great to return a blank if the customer has not gone live.

Then I have formula to calculate the average of the cells.

=AVG([Number of Days to Implement]12:[Number of Days to Implement]41)  Also works great !

I now want to calculate the average based upon a region (EAST, WEST, CENTRAL, MOUNTAIN)

I tried this formula but it is not working.

=AVG([Number of Days to Implement]12:[Number of Days to Implement]41, [US Region]:[US Region], "Central")

Any suggestions?

 

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    smartsheet does not currently have an avgif formula. I recommend submitting a product enhancement request in the bar to the right.

    That said, you can achieve the same results by using a sumif()/Count()

     

    =sumif([US Region]12:[US Region]41, "Central",Number of Days to Implement]12:[Number of Days to Implement]41)/Count([US Region]12:[US Region]41)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could also use an AVG(COLLECT( formula which works exactly how an AVGIFS would work.

     

    =AVG(COLLECT([Number of Days to Implement]12:[Number of Days to Implement]41, [US Region]:[US Region], "Central"))

  • Thank you to both for your assistance.  The average days were not calculating correctly with the formula but I was able to make a slight adjustment based upon another calculation I have in my sheet.

    I already had it calculating the total number of closed implementations.

    =COUNTIFS([US Region]:[US Region], "East", Closed:Closed, "Yes")

    So I used that cell to do the division and it worked perfectly.

    =SUMIF([US Region]15:[US Region]44, "East", [Number of Days to Implement]15:[Number of Days to Implement]44) / COUNT([After Hours]2)

    After Hours is the column name I have for the COUNTIF formula

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad you were able to find a working solution! yes

  • Hi Kandee,

    This may help in the future:

    =AVERAGEIF(range, criteria, [average_range])    

    Returns the average of cells that meet a given criteria.

    Example: =AVERAGEIF([Value 1]200:[Value 2]280, >100)

    Best,

    Kara

  • L_123
    L_123 ✭✭✭✭✭✭

    Kara, when was this added? i double checked the formula list and even checked it in a blank sheet to make sure that averageif wasn't a function yet when i posted. I also checked the release notes and there is no mention of it

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    It was added yesterday, or at least I noticed it yesterday.

    There are a lot of new functions.

    Heres more info about AVERAGEIF: https://help.smartsheet.com/function/averageif

    Hope that helps!

    Have a fantastic day!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!