Formula Not Working - including a "%" in the formula turns off the cell references

Michael Aguilera

I am trying to write a formula and I am having a few issues.

Context: We have a build that has several tasks (which have their own child rows), and it is possible to finish the build entirely without finishing each task entirely. If the build is not finished, we want the tasks to be =AVG(CHILDREN). However, if the bottom line is manually input as 100%, we want that to override the average children function and have it show all tasks as 100%.

Here is what I THINK it should look like, but it is not working… "=IF([Build Complete]65 = "100%", 100%, AVG(CHILDREN))

Natural language being: If Build Complete row 65 is 100%, than make this task 100%, if it is not, than make this task the average of all child rows.

Two issues I am having.

1. In the "value_if_true" section, if I include a percent sign, it automatically makes the [Build Complete]65 cell reference turn off. It goes from blue text to grey text.
2. When adding the AVG(CHILDREN) as the "value_if_false" value, it returns #UNPARSEABLE.

Any help would be greatly appreciated!

Paul Newcome

Smartsheet formulas read percentages as a part of a whole. So 100% = 1, 50% = 0.50, 25% = 0.25, so on and so forth. Try making both of your "100%" pieces just 1 (without quotes) and make sure the column is set with percentage formatting.

Paul Newcome

Smartsheet formulas read percentages as a part of a whole. So 100% = 1, 50% = 0.50, 25% = 0.25, so on and so forth. Try making both of your "100%" pieces just 1 (without quotes) and make sure the column is set with percentage formatting.

Michael Aguilera

@Paul Newcome This worked perfectly, thanks for the assist! The formula is now =IF([Build Complete]65 = 1, 1, AVG(CHILDREN())). Thanks again!

Paul Newcome

Happy to help. 👍️