I am trying to find the average of a colum of excluding errors
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

@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

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

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

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.

@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

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

Good afternoon,
Thank you so much!!
Those solutions all work.
Absolute life savers!!!
Help Article Resources
Categories
Check out the Formula Handbook template!