I am providing an average of the percents listed in a given column. However, some of the returns (as designed) are returning a #DIV/0 error. With that being the case, as we know with basic division... because we are essentially dividing by 0... this is the cause of the error-
So, I want to know, is it possible for me to write a statement that omits those values in WebI, from the overall Average... I'm thinking an If statement of some form...
I'm currently using:
=Average(Round([v1]/[v2]*100;0))+"%"
AND I'M USING THIS AT THE ROW LEVEL:
=If(IsError([v1]/[v2]))Then ""
ElseIf(IsNull(Round([v1]/[v2]*100;0))) Then ""
Else Round([v1]/[v2]*100;0)+"%"
Thoughts?
Use iserror() function with if-else condition.
=if(iserror(Average(Round([v1]/[v2]*100;0))+"%")) then "" else Average(Round([v1]/[v2]*100;0))+"%"
Recheck your average formula.it seems it's not correct.you have used average() function and manually used v1/v2*100.
I will check now, and try to use what you've suggested.
As it relates to the average, I wanted to take the average of the entire set of data in the column, with the exception of the #DIV/0 - - I will recheck it though
Hmm... that is not what I want to accomplish... I want to be able to take the average of the other rows without the errors... like, in my picture below, I would want to omit the blanks... and take the average of what is left...
Hi,
you can create an extra variable setting X for the values you want to calculate on.
- new variable, check, if iserror set null else set X
- average calculator, average where check = X
Grtz
Koen