Calculating average on linked cells - divide by zero error

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.

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:

Georgie

Hi @Jenn_Sas,

Thanks for providing the screenshots! I can see that your ROUND formula in the cells that are linked is using + “%” at the end, meaning that the resulting value in those linked cells is a mixed text and number value. This is why your AVG formula cannot calculate - it can only work on values that are numerical only. 

To resolve this, we can replace the cell links in your % Complete column with formulas that give us the numerical values, and then the AVG formula will work as expected.

1. First, remove the cell links in row 3 and 4 in your % Complete column (right-click the cell and select Remove Link).  
2. We’ll use the same base formula for both cells, but we need to create different cross-sheet references for each one. For the cell in row 2 of your % Complete column, we want to reference the cell in row 1 in the “Sept/Oct Bulk Draw” column on your other sheet. The formula we need is below - I recommend typing this in so that you can create the cross-sheet reference for your sheet:
   1. =IF(LEN({Sept/Oct Bulk Total}) = 2, VALUE(LEFT({Sept/Oct Bulk Total}, 1)) / 100, VALUE(LEFT({Sept/Oct Bulk Total}, 2)) / 100)
   2. To create the cross sheet reference, after you type LEN(, click Reference Another Sheet.
      
   3. Locate your source sheet and select the cell in the totals row of the Sept/Oct Bulk Draws column. I recommend giving the sheet reference a name (eg “Sept/Oct Bulk Total”), as this is what you’ll see in the formula.
      
   4. Once you’ve created the reference, you can type or copy and paste it into the formula (so you could then copy and paste the formula above and replace each cross-sheet reference with your own).
3. Do the same for the cell in row 3 of your % Complete column - this time, make sure you reference the cell with the total for the Sept/Oct Baby Draws:
   1. =IF(LEN({Sept/Oct Baby Total}) = 2, VALUE(LEFT({Sept/Oct Baby Total}, 1)) / 100, VALUE(LEFT({Sept/Oct Baby Total}, 2)) / 100)
4. You’ll see that the values in your % Complete column are decimal values. To show these as percentages, click the column header to highlight the whole column, then click the percentage format button on the top toolbar (you’ll need to click the three-dot menu if you can’t see it):
   

You should then see that your =AVG formula is calculating correctly.

Does that work for you?

Georgie

Georgie

Hi @Jenn_Sas,

Thanks for providing the screenshots! I can see that your ROUND formula in the cells that are linked is using + “%” at the end, meaning that the resulting value in those linked cells is a mixed text and number value. This is why your AVG formula cannot calculate - it can only work on values that are numerical only. 

To resolve this, we can replace the cell links in your % Complete column with formulas that give us the numerical values, and then the AVG formula will work as expected.

1. First, remove the cell links in row 3 and 4 in your % Complete column (right-click the cell and select Remove Link).  
2. We’ll use the same base formula for both cells, but we need to create different cross-sheet references for each one. For the cell in row 2 of your % Complete column, we want to reference the cell in row 1 in the “Sept/Oct Bulk Draw” column on your other sheet. The formula we need is below - I recommend typing this in so that you can create the cross-sheet reference for your sheet:
   1. =IF(LEN({Sept/Oct Bulk Total}) = 2, VALUE(LEFT({Sept/Oct Bulk Total}, 1)) / 100, VALUE(LEFT({Sept/Oct Bulk Total}, 2)) / 100)
   2. To create the cross sheet reference, after you type LEN(, click Reference Another Sheet.
      
   3. Locate your source sheet and select the cell in the totals row of the Sept/Oct Bulk Draws column. I recommend giving the sheet reference a name (eg “Sept/Oct Bulk Total”), as this is what you’ll see in the formula.
      
   4. Once you’ve created the reference, you can type or copy and paste it into the formula (so you could then copy and paste the formula above and replace each cross-sheet reference with your own).
3. Do the same for the cell in row 3 of your % Complete column - this time, make sure you reference the cell with the total for the Sept/Oct Baby Draws:
   1. =IF(LEN({Sept/Oct Baby Total}) = 2, VALUE(LEFT({Sept/Oct Baby Total}, 1)) / 100, VALUE(LEFT({Sept/Oct Baby Total}, 2)) / 100)
4. You’ll see that the values in your % Complete column are decimal values. To show these as percentages, click the column header to highlight the whole column, then click the percentage format button on the top toolbar (you’ll need to click the three-dot menu if you can’t see it):
   

You should then see that your =AVG formula is calculating correctly.

Does that work for you?

Georgie

Jenn_Sas

@Georgie you are a saint. The formula you provided is working. Although a manual process, I'm able to get the cells linked. Thank you, sincerely.

Georgie

Hi @Jenn_Sas,

Glad to hear that's working for you! Happy to help 🙂

Georgie