Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

=AVGIFS Function Workaround

Todd Tanner
edited 12/09/19 in Archived 2016 Posts

So there isn't an AVGIFS function currently in SmartSheet, but I still need to find a workaround. I came up with:

 

=IF(AND([Project Manager]="Name",[Owner]="Name",[Project Status]="Complete"),AVG([Days to Complete]),"")

 

However I am coming up with a "MISSING OR INVALID PARAMETERS error. I have pulled the averaging portion out and it works fine on its own. 

 

Any ideas?

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Todd,

     

    You are missing references to the row. The error message can be misleading (at least to me) when this is the case.

    I'm not sure that is going to work.

    Let us know if it did (or didn't).

    I have some ideas but it is time for sleep.


    Craig

  • Dave D
    Dave D
    edited 04/18/16

    Quickly looking at your formula I see a couple errors.

     

    First, [Project Manager], [Owner], are not cell references - they are just the name of a column with brackets

     

    This is a cell reference:

     

    [Project Manager]2

     

    or 

     

    Owner2

     

    If you want reference a range of cells...

     

    [Project Manager]2:[Project Manager]10

     

    or

     

    Owner2:Owner10

     

    If you want to reference an entire column:

     

    [Project Manager]:[Project Manager]

     

    or

     

    Owner:Owner

     

    You are correct in that there is not an AVGIFS but you can just use COUNTIFS and SUMIFS to get the same results

     

    SUMIFS / COUNTIFS = AVGIFS

This discussion has been closed.