How to fix my #Boolean Expected Error

Ted Iovino · 04/06/20

Good morning, I'm trying to solve this error in my formula. All I want to do is display a % complete every time I click a check mark under my done column. Here is my formula:

=COUNTIF(Done:Done, 1) / (COUNTIFS(Done:Done, 0, Tasks:Tasks, NOT(ISBLANK(@cell))) + (COUNTIF(Done:Done, 1)))

Any help will be greatly appreciated!

Debbie Sawyer · 04/07/20

The original error was due to the formula being placed in the checkbox column. The "Boolean Expected" error means that Smartsheet is expecting a Tick in a box within that column, not a number returned from a calculation - hence my original question of where is the cell that is performing the calculation.

Paul pointed out that you can overcome the Boolean Expected error by converting your answer to an accepted string (sort of forcing Smartsheet to accept a value which isn't the value type it is expecting i.e. it was expecting a tick in a box, but you made it accept a text/number value instead) which worked. However the +"" was forcing a formatting that wasn't compatible with the % formatting button.

By moving the formula to a Text/Number data type column, you were able to get the formula result returned in an Expected Format (as the column was expecting Text/Number data) hence overcoming the original Boolean Expected error; however in moving the formula you didn't need to Force Smartsheet to accept a Text/Numeric value as it was already expecting one! In removing the +"" you rendered the cell open to formatting again! ;)

(This is my laymans way of understanding what happens - I am not particularly technical, but hopefully I can explain things in a way that people can understand!)

Fingers crossed hey! 😄

Hope I've helped you out!

Kind regards

Debbie

Paul Newcome · 04/07/20

@Debbie Sawyer That was a great explanation as to why the different portions were working as they were.

@Ted Iovino If you wanted to continue displaying it in the checkbox column, you would need to simply round the numerical portion of the formula to two decimals then multiply by 100. After that instead of + "", you would use + "%" to add the percent symbol.

=ROUND(COUNTIF(Done:Done, 1) / (COUNTIFS(Done:Done, 0, Tasks:Tasks, NOT(ISBLANK(@cell))) + (COUNTIF(Done:Done, 1))), 2) * 100 + "%"

Debbie Sawyer · 04/07/20

Where is the cell that you are trying to put the answer to this formula? Is that destination cell a Text/Number data type or are you trying to put the answer at the end of your Done column?

Paul Newcome · 04/07/20

You should be able to just convert it into a text string by adding

+ ""

to the end.

=COUNTIF(Done:Done, 1) / (COUNTIFS(Done:Done, 0, Tasks:Tasks, NOT(ISBLANK(@cell))) + (COUNTIF(Done:Done, 1))) + ""

Ted Iovino · 04/07/20

Thank you Debbie and Paul for your responses. I'm trying to locate a total percentage every time I complete a project in the Done row 1 slot right above the checkbox column.

Paul, thank you! It displays it as a decimal point now! I do however want it to display as 20% instead of 0.20. When I click percentage format, it doesn't change it. Do I have to alter my formula?

Debbie Sawyer · 04/07/20

Hi - If you move your formula into a new column (a Text/Number) column then the % button will work :)

Hope this helps.

Debbie

Ted Iovino · 04/07/20

Hi Debbie, I moved it to my tasks column which is a Text/Number type, but the % button didn't change anything. Did I do something wrong?

Debbie Sawyer · 04/07/20

Did you remove the +"" from the end as you won't need it now...

Ted Iovino · 04/07/20

Wahh thank you!! I didn't really understand the +"" addition to the formula, didn't realize I had to take it out.

Debbie Sawyer · 04/07/20

The original error was due to the formula being placed in the checkbox column. The "Boolean Expected" error means that Smartsheet is expecting a Tick in a box within that column, not a number returned from a calculation - hence my original question of where is the cell that is performing the calculation.

Paul pointed out that you can overcome the Boolean Expected error by converting your answer to an accepted string (sort of forcing Smartsheet to accept a value which isn't the value type it is expecting i.e. it was expecting a tick in a box, but you made it accept a text/number value instead) which worked. However the +"" was forcing a formatting that wasn't compatible with the % formatting button.

By moving the formula to a Text/Number data type column, you were able to get the formula result returned in an Expected Format (as the column was expecting Text/Number data) hence overcoming the original Boolean Expected error; however in moving the formula you didn't need to Force Smartsheet to accept a Text/Numeric value as it was already expecting one! In removing the +"" you rendered the cell open to formatting again! ;)

(This is my laymans way of understanding what happens - I am not particularly technical, but hopefully I can explain things in a way that people can understand!)

Fingers crossed hey! 😄

Hope I've helped you out!

Kind regards

Debbie

Paul Newcome · 04/07/20

@Debbie Sawyer That was a great explanation as to why the different portions were working as they were.

@Ted Iovino If you wanted to continue displaying it in the checkbox column, you would need to simply round the numerical portion of the formula to two decimals then multiply by 100. After that instead of + "", you would use + "%" to add the percent symbol.

=ROUND(COUNTIF(Done:Done, 1) / (COUNTIFS(Done:Done, 0, Tasks:Tasks, NOT(ISBLANK(@cell))) + (COUNTIF(Done:Done, 1))), 2) * 100 + "%"

Ted Iovino · 04/07/20

You guys are amazing and both explanations were perfect. Thank you so much again, I will remember this for future projects!

Paul Newcome · 04/07/20

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful". That way others searching for a similar solution can know that one may be found here.

Debbie Sawyer · 04/08/20

Glad it is now working for you :)

Formulas and Functions

Formulas and Functions

How to fix my #Boolean Expected Error

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days