Skip to Content
0

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

May 08, 2017 at 08:20 PM

52

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
May 08, 2017 at 09:02 PM
0

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.

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

capture-dc01.jpg (12.2 kB)
0

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

0