I am trying to find the average of a colum of excluding errors

Options

Hi everybody,

First time poster long time code borrower and student :)

I am having issues with a formula returning #invalid data type.

I am trying to find the average of the column called "Time to Action," excluding any cells that do not have numbers or contain an error.

I have tried using

=AVERAGEIF([Time to Action]:[Time to Action], ISNUMBER([Time to Action]:[Time to Action]))

I have had no success and can not figure out where I am going wrong.

Any assistance would be appreciated.


Best Answer

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭
    Answer ✓
    Options

    @The Dan see the replies from @Hollie Green & @parulmishra - they nailed it.

    My personal favorite with formulas to check if a cell blank (as you would want to do with your formula is "Time to Action" is:

    =IF([Ticked as Completed]@row<>"", insertYourTimetoActionFormulaHere, "")

    So it runs your formula if "Ticked as Completed" is NOT blank, but if it IS blank, it displays nothing - but that's just how I like to do it personally.

Answers

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭
    Options

    Hey @The Dan !

    It looks like you may be able to do this with:

    =AVERAGEIF([Time to Action]:[Time to Action], ISNUMBER(@cell)

    As it already knows the range & now must just check if each cell is a number.

    If that doesn't work - could you change the formula in "Time to Action" so that it doesn't show anything if there's an error?

    Let me know if that works or if you need any further help!

    -Jon Mark H

  • The Dan
    The Dan ✭✭
    Options

    Hey @Jon Mark H

    Thank you for your help!


    I tried your formula and it returns the #INVALID DATA TYPE Error.


    I think I agree with your second statement regarding changing the average formula to return nothing if there is an error.

    Any idea of what that formula would look like?


    -The Dan

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Options

    For you time to action column put in the formula

    =IFERROR(your current formula goes here,"") this will return a blank instead of an error message in your time to action column. then you can use Jon's formula to reference the time to action column.

  • parulmishra
    parulmishra ✭✭✭✭✭
    edited 06/19/23
    Options

    @The Dan You can try using IFERROR to achieve that

    For example if you want the cell to have 0 then

    IFERROR( TimeToActionFormula , 0)

    if you want the cell to have blank then

    IFERROR( TimeToActionFormula , "")

    More details can be seen here https://help.smartsheet.com/function/iferror

    Parul Mishra

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭
    Answer ✓
    Options

    @The Dan see the replies from @Hollie Green & @parulmishra - they nailed it.

    My personal favorite with formulas to check if a cell blank (as you would want to do with your formula is "Time to Action" is:

    =IF([Ticked as Completed]@row<>"", insertYourTimetoActionFormulaHere, "")

    So it runs your formula if "Ticked as Completed" is NOT blank, but if it IS blank, it displays nothing - but that's just how I like to do it personally.

  • The Dan
    The Dan ✭✭
    Options

    Good afternoon,

    Thank you so much!!

    Those solutions all work.


    Absolute life savers!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!