AVERAGEIF function help?

Options

I'm having trouble with the AVERAGEIF function. I have a project intake sheet, and I'd liek to take the average of a number among all projects in the sheet.

I'm attempting to use =AVERAGEIF([Duration of Hold]6:[Duration of Hold]100, >0)), but I can't figure out how to protray the critera correctly to make the formula work.

I want it to only take the average of cells in Duration of Hold, but only the ones that have a number. The column Duration of Hold is calculated based on Start and End date columns. So some rows have a number, but in others the data is still incomplete so some cells say "#INVALID DATA TYPE" because the project hasn't progressed yet. My AVERAGEIF formula is coming back as "UNPARSABLE" currently.

Is there a way to exclude the INVALID DATA TYPE rows in my AVERAGEIF formula? See screenshots below for reference. Thank you!

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/12/24 Answer ✓
    Options

    @Kate Allison

    Ok lets try adding an IFERROR to your formula in the Duration of hold to return a blank instead of an error.

    =IFERROR(your formula),"")

    THEN you can do

    =AVG(COLLECT([Duration of Hold]:[Duration of Hold],[Hold Start Date]:[Hold Start Date], @cell <> "",[Hold End Date]:[Hold End Date],@cell <> ""))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    edited 06/11/24
    Options

    Hey @Kate Allison,

    If you wrap your formula in an IFERROR function, that might remove the invalid data errors:

    =IFERROR(AVERAGEIF([Duration of Hold]6:[Duration of Hold]100, >0), "")

    See if that works!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    what if you change the > 0 to <>””

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Kate Allison
    Kate Allison ✭✭✭✭
    edited 06/12/24
    Options

    @bisaacs It did not work :( It no longer has an error, but now it's just returning a blank cell. It should return a 5 based on the data currently in the sheet.

    @Mark.poole changing to <>"" also did not work and still returned #UNPARSABLE.

    Maybe there's a way to incorporate ISNUMBER? I haven't figured it out so far this morning.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/12/24 Answer ✓
    Options

    @Kate Allison

    Ok lets try adding an IFERROR to your formula in the Duration of hold to return a blank instead of an error.

    =IFERROR(your formula),"")

    THEN you can do

    =AVG(COLLECT([Duration of Hold]:[Duration of Hold],[Hold Start Date]:[Hold Start Date], @cell <> "",[Hold End Date]:[Hold End Date],@cell <> ""))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Kate Allison
    Kate Allison ✭✭✭✭
    Options

    @Mark.poole adding in IFERROR worked - thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!