How to fix my #Boolean Expected Error
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!
Best Answers
-
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
-
@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 + "%"
Answers
-
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?
-
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))) + ""
-
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?
-
Hi - If you move your formula into a new column (a Text/Number) column then the % button will work :)
Hope this helps.
Debbie
-
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?
-
Did you remove the +"" from the end as you won't need it now...
-
Wahh thank you!! I didn't really understand the +"" addition to the formula, didn't realize I had to take it out.
-
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
-
@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 + "%"
-
You guys are amazing and both explanations were perfect. Thank you so much again, I will remember this for future projects!
-
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.
-
Glad it is now working for you :)
Help Article Resources
Categories
Check out the Formula Handbook template!
