Remove blank or zero percentage in average calculation

kim.dang@dang-consulting.com

I have been trying to get an average of child rows and continue to get Divide by zerro error. What do i need to do to remove this error?

Kelly Moore

Hey @kim.dang@dang-consulting.com

What formula are you using that is giving you the Divide by zero? The one in your screenshot is unparseable. This formula below may give you the answer you need but perhaps we could come up with a formula that didn't require you to hard code row numbers into the formula.

First, try this without the IFERROR. The IFERROR may mask formula errors - it should be the last thing you add to a formula to be certain the formula is working as desired.

=AVG(CHILDREN([Original Duration]9), CHILDREN([Original Duration]13))

=IFERROR(AVG(CHILDREN([Original Duration]9), CHILDREN([Original Duration]13)),"")

Does this work for you?

Kelly

kim.dang@dang-consulting.com

Hi again -

so i used the latter formula. the first one gave me a number of 2,243% and an error and the second one gave me 483% but no errors! still missing something?

Kelly Moore

Hey Kim

Sorry you're still having problems with the formula. Help me understand exactly what values you are trying to average, and where exactly are you placing the formula I provided? If you are averaging values that are already percents (decimals up to 1.0) then you should not have a result greater than 1 (or 100%).

We would expect the second formula to not show errors - that is it's purpose. It is interesting to me though that the results are different between the two formulas.

It's also difficult to tell from your screenshot what rows are parent rows. Besides row 9 and 13 (wherever they are) do you have more parents?

The 'Divide by zero' error shown in the blue row in your screenshot above - is this the same formula I provided or does that cell have a different formula? If different, what is that formula?

Kelly