Remove blank or zero percentage in average calculation

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?




Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @[email protected]

    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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!