Calculation from number in a checkbox column

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%

Invalid Operation checkbox.JPG

Find more posts tagged with

Formulas

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…