Creating a formula with a drop down box of percentages
Hello All,
I am working on using a drop down box to calculate task percentage as well as the complete percentage total per phase. Initially we were using the % Complete and allowing any percentage to be entered however now we are going with a set percentage to make it easier on the client. The problem is that now when I use the same formula it calculates 100 regardless of what the drop down box selection is.
The choices are in 25% increments. I am not sure what I am doing wrong. There are 38 tasks within Phase I and no matter what I change the drop down selection to the answer is always 100%.
25%*2 should be 50% or is my math off? Any feedback would be very helpful.
-Kind Regards
Best Answers
-
Try something like this...
To remove the %:
=SUBSTITUTE([% Complete]@row, "%", "")
To convert it to a number:
=VALUE(SUBSTITUTE([% Complete]@row, "%", ""))
To convert it to a decimal (which is how SS reads actual percentages):
=VALUE(SUBSTITUTE([% Complete]@row, "%", "")) / 100
Then to finish it off with the weight multiplier:
=(VALUE(SUBSTITUTE([% Complete]@row, "%", "")) / 100) * [Weight Complete]@row
Make sure the column you are putting the formula in is formatted for percentages and you should be good to go.
-
You seem to be angry at () really 😁
That's =(VALUE(SUBSTITUTE([% Completes]@row, "%", ""))/100)*[Weight Complete]@row.
The first ( is before VALUE, you added it after, so your Smartsheet is trying to divide by 100 a string. Which doesn't work.
Answers
-
No your maths aren't off. 25%*2 is equal 50%
But you included that in a COUNT function, which counts cells. so your function ends with a 1, and since your column is a %, it displays 100%.
Remove the COUNT function from your formula, it'll be fine.
-
@David Joyeuse Thank you so much for your feedback however if I remove the Count function I get an #invalid operation error
-
Ah Probably because your % completed column is a dropdown list. Try this:
=VALUE([% Completes]@row)*[Weight Complete]@row
-
-
Your VALUE function will include only the % Complete. There is a parenthesis in the wrong place causing the error. Your formula should be: =VALUE([%Completed]@row) * [Weight Completed]@row
-
Its like I need to get the formula to recognize the different values within the drop down box column
-
-
It will recognize the different values with that formula.
What you have typed right now is =VALUE([%Completes]19 * [Weight Complete]19)
What you need is =VALUE([%Completes]19) * [Weight Complete]19
Just move your last parenthesis from the end to after the % Completes19.
-
@Leslie Merlino Awesome Name by the way :) Thank you so much for your feedback on this one. Unfortunately, I have tried both and keep getting the same error.
=VALUE([% Completes]@row * [Weight Complete]19)-Invalid Operation
=VALUE([% Completes]@row )* [Weight Complete]19)-Unparseable
I am not sure what I am doing wrong but I do enjoy the hunt for the perfect formula to reach our desired outcome.
:)
-
Thank you! LOL. I honestly don't meet many Leslie's(sp). Odd cause it is an awesome name!!
On that second one, you still have a parenthesis at the end. Your formula should end with the number 19. No parenthesis.
=VALUE([%Completes]19) * [Weight Complete]19
-
Right the only person I know who spells it like your is my father. It is definitely a unique name. I always get excited when I see it :)
So I tried that and still got an error
=VALUE([% Completes]19) * [Weight 2]19- Invalid Data Type
-
Hmm. Invalid Data Type. In your dropdown list how do you have them listed? Do they look like this? Or are they just the numbers 25, 50, 75, 100 etc?
-
Yes I have them listed as percentages
-
I used this and it works no matter how I have the dropdown set:
-
You think I should remove the drop down option all together; because even when I remove the % symbol I get the same error. I notice in your example it is not in a drop down format like mine you see yours doesn't have the little gray square around it like mine does..but we kind of need the drop down in order to control the client's feedback
Help Article Resources
Categories
Check out the Formula Handbook template!
