I would like to know if it is possible to omit #DIV/0 rows when summarizing on a column

[Former Member]

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?

[amitrathi239]

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.

[Former Member]

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

[Former Member]

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

[kohesco]

Hi,

you can create an extra variable setting X for the values you want to calculate on.

1. new variable, check, if iserror set null else set X
2. average calculator, average where check = X

Grtz

Koen