# Calculation from number in a checkbox column

Options
edited 12/09/19

I'm trying to create a column that would allow some groups to report progress as a percentage and some to simply check a box for 100%.

I've written my formula, but as soon as I enter a number within the checkbox column I receive the #Invalid Operation error.

Any help would be appreciated.

=IF(Progress4 = "True", 1 * Duration4, IF(Progress4 = "False", 0, Progress4 * Duration4))

The desired outcome of row 4 would be 8*25%=200%

• ✭✭✭✭✭✭
Options

Hi Jacob,

At a glance, I would recommend creating another column for the percent value and then add that together with the Progress Checkbox.

Would that work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Options

Different work groups would like to use one or the other, not both.  I'm trying to reduce columns (especially visible columns) and reduce confusion.  If it is not possible, I will have to think of something else.

Are you saying this isn't possible from within 1 column?

• ✭✭✭✭✭✭
Options

Because the numbers are being manually entered along with the percent sign in the checkbox column, it is reading as text. You would need to remove the % and convert the numbers from text to actual numbers.

Try something like this...

=IF(Progress4 = "True", 1 * Duration4, IF(Progress4 = "False", 0, VALUE(SUBSTITUTE(Progress4, "%", "")) * Duration4))

• edited 05/28/19
Options

Thanks Paul.  You have to divide by 100, (depending on the data format in your column) but the formula works!

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!