Trimmean to ignore #div/0!

[Nevada511]

Hi all,

Another question i have data as follows:

Year January
2012 #DIV/0!
2013 #DIV/0!
2014 #DIV/0!
2015 -42%
2016 -16%
2017 -100%

=TRIMMEAN(IF(B11:B17,"<>#DIV/0!"),Variables!$B$4)

Error: #Value!

[FDibbins]

What is causing the #DIV/0? Have you considered modifying that formula to produce "" ?

[Nevada511]

yes.
i tried to produce "" but still get the #VALUE! Error

[FDibbins]

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[Nevada511]

here u go.

[sandy666]

maybe easier will be with =IFERROR(B3/M2-1,0)

[FDibbins]

Try this...
C11=IFERROR(C2/B2-1,"")
then
C19=TRIMMEAN(C11:C17,0.1)

[daddylonglegs]

Probably better to avoid the error in the first place as Ford suggests but, FWIW, you can use this version to ignore errors

=TRIMMEAN(IF(ISNUMBER(B11:B17),B11:B17),Variables!$B$4)