#Boolean Expected? #Invalid Operation? I can't win for losing

Options
✭✭✭✭✭
edited 12/09/19

I thought I was quite brilliant when I figured out that the reason a formula I created in a checkbox only column returned a #BOOLEAN EXPECTED error.  I solved the problem by appending +"" to the end of my formula, and celebrated with a fun sized Snickers.  Imagine my disappointment, when I tried to create a new formula that calculated a percent complete between two such numbers.

In other words, the columns with the (corrected) formula return values of 89 and 20 respectively.  In Sheet Summary, I decided to build a formula that divides the field with 20 by the field with 89.  Lo and behold, I get a new error:  #INVALID OPERATION.  Now this is a simple division formula, so the only think I can believe is causing this is the fix I made for the BOOLEAN EXPECTED error.

Anyone have suggestions?  Here are my three formulas:

=COUNTIF([Has Desk Phone]2:[Has Desk Phone]99, <>0) + "" (works now, but returns Boolean without the +"")

=COUNTIF([Wants Desk Phone]2:[Wants Desk Phone]99, <>0) + "" (works now, but returns Boolean without the +"")

=[Wants XLNX Desk Phone]1 / [Has Desk Phone]1 (returns #INVALID OPERATOR)

Tags:

• ✭✭✭✭✭✭
Options

Hi Ken,

It's because the numbers are converted to text when you're using +"" so you'll have to convert it back to a number with the VALUE function or take a look at the formula again.

https://help.smartsheet.com/function/value

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

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

Andree is correct. You will need the value function to convert the text string back to numerical values.

=VALUE([Wants XLNX Desk Phone]1) / VALUE([Has Desk Phone]1)

• ✭✭✭✭✭
Options

Sorry, Andree.  The company for which I'm contracting at the moment doesn't allow external sharing.  However, the VALUE suggestion you made ended up working.

• ✭✭✭✭✭
Options

Thanks very much, Paul.  Andree offered the same suggestion, but you get 2.0948 extra points, because you actually showed me how to adapt my formula with it.  Saved me the trouble of having to learn it, but also taught me something.

Cheers.

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭✭
Options

No worries!

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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!