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

khankoff

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)

Andrée Starå

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

 

Paul Newcome

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)

khankoff

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.

khankoff

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.

Paul Newcome

Happy to help! 

Andrée Starå

No worries!

Glad that you solved it!