Calculation from number in a checkbox column

Jacob Slaathaug

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%

 

 

Andrée Starå

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

Jacob Slaathaug

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?

Paul Newcome

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))

Jacob Slaathaug

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

Paul Newcome

Happy to help!