Average by section within a column

ShaneRiddle

Average with 100%.png

Average without 100%.png

I am working on a sheet where we are tracking the percent complete within a column. The top percent complete formula is =AVG([Percent Complete]2, [Percent Complete]17). The formula in [Percent Complete]2 is =IF(Completed@row = "Completed", "100%", COUNTIF(Completed3:Completed16, "Completed") / 14)

The purpose of the formula in green, [Percent Complete]2 is so that if [Completed]3 is marked as completed, the [Percent Complete]2 shows 100% so users don't have to check the 14 boxes below as all complete and can essentially mark that section as complete with 1 box when done. The sheet will have 12 of these sections with each averaging the % complete and that rolls up to the [Percent Complete]1 cell.

I tried using checkboxes and that didn't work since you have a formula in the cell and you mark the box it doesn't roll up. We tried a few other options as well. Currently with the formula in [Percent Complete]2, when you mark [Completed]2 as complete, the average in [Percent Complete]1 ignores the number shown in [Percent Complete]2 almost like it doesn't think it is a number.

I appreciate your ideas on how to make this work.

Thank you,

Shane

Melissa Yamada

In [Percent Complete]2 you’re outputting "100%" with quotes. In Smartsheet, anything in quotes is treated as text, not a numeric percentage. So when your top formula does AVG([Percent Complete]2, [Percent Complete]17), it skips that value because AVG only works with numbers.

Change the formula to:

=IF(Completed@row = "Completed", 1, COUNTIF(Completed3:Completed16, "Completed") / 14)

Then just format the [Percent Complete] column as Percent. Smartsheet will display 1 as 100%, but under the hood it stays a number so your AVG will include it correctly.