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

The Dan

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.

Jon Mark H

@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.

Jon Mark H

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

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

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

@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

Jon Mark H

@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

Good afternoon,

Thank you so much!!

Those solutions all work.

Absolute life savers!!!