Divide by Zero error when averaging 2 linked cells

Jenn_Sas

I have linked 2 cells from another Smartsheet that are child tasks to my project plan. When trying to average the linked cells in the parent task, I am getting a divide by zero error. The linked cells are not showing 0 in their calculation. I'm at a loss.

Can someone help me with this error or explain why this error is occurring?

My project plan showing the linked cells and avg formula. I'm getting a divide by zero error:

Linked Smartsheet has formula to add checked boxes in the column:

Michelle Choate 2

That is really strange. I have tried it several different ways and it always comes up with 5.5%. My last formula I tried was =AVG(CHILDREN()). That is a really weird error and I am not sure why you are getting it.

Jenn_Sas

The avg children formula is still giving me a divide by zero error

Paul Newcome

The problem is with the formula htat is outputting the percentages. Having the formula add the "%" to the end is turning it into a text string which can't be used in numerical calculations.

But… You can't just change the formulas where they exist now because they are in checkbox columns. You will need to find other cells that are in text/number columns to put the formulas in, don't multiply by 100 and don't add the "%". Instead just run the base calculation and set the column properties to show as a percentage. Then link these two new cells over.

Jenn_Sas

https://community.smartsheet.com/discussion/comment/465826#Comment_465826

Thank you Paul.

Is there any way to get this to work with the checkbox columns? I don't have another way to show whether a project is completed at that step. There are too many people in the Smartsheet, so we found the checkboxes to be the best way to show task complete.

Paul Newcome

ON the sheet with the AVG formula… The one bringing the data over in cell links. Bring the cell links into a helper column. Then in the % Compete column use

=VALUE(SUBSTITUTE([Helper Column]@row, "%", ""))

Then to get the average, we would just use

=AVG(CHILDREN())

You do not need to include anything at all inside of the CHILDREN function.