Creating a formula with a drop down box of percentages
Answers
-
Yes see it works here but without the drop down selections...is there a way to ensure they only put in the percentages that we want and still have the formula work out?
THANK you a huge ton just for responding :)
-
I believe you can make it work with your drop down list with this formula:
=VALUE(LEFT([% Completes]@row, FIND("%", [% Completes]@row)-1)) * [Weight Completed]@row.
The LEFT/FIND function will make sure we get rid of the "%" prior to returning the value of it.
-
@David Joyeuse Thank you so much for your help on this unfortunately I received an error of Invalid Value. I tried to play around with the parenthesis and ended up with a unparsable error.
-
Yeah text function seems to have weird results when working with dropdown cells. That's interesting at they seemed to not be treated like text.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Thank you so much for your help !! I am beginning to think I may be asking for the impossible....if so what other way could we control the clients selection choice as relates to Percentage complete of each task. I did get an unparsable error. I tried to add parenthesis around Weight 2 but it still errors out. When I look at the format for the column percentage is selected.
-
Well, you get UNPARSEABLE because you have a closed parenthesis after /100. Add one prior to VALUE to make sure the * will act on the VALUE function and not 100.
Also, in the substitute function you put " ", it should be "" to remove %.
-
@David Joyeuse Thanks so much!! How did I do? I added a parenthesis in front of Value and removed the space between the quotations
-
You need to remove the 19 after [Weight Complete]. You should have either a row number OR @row, not both.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ah nice one Paul, I missed that :)
-
-
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.
-
@David Joyeuse ..I am just trying to figure out the pieces to this puzzle. Are you suggesting that I re-write it like you have here? After @Paul Newcome responded with this version I typed it exactly as it was in his example.
-
@Leslye Jackson Your screenshot does not match exactly to my solution. My solution starts with
=(
whereas the formula in your screenshot starts with
=VALUE
Here is my solution:
=(VALUE(SUBSTITUTE([% Complete]@row, "%", "")) / 100) * [Weight Complete]@row
Try copy/pasting it from here to your Smartsheet instead of retyping.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@David Joyeuse Thank you for your comment !!! I went back and looked at the placement of all the parenthesis. The one by 100 was just hanging out by itself!!! I love the hunt and the thrill of finally getting it right!
Finally the answer to it all :) Thank you guys!!!
=VALUE(SUBSTITUTE([% Completes]@row, "%", "")) / 100 * [Weight Complete]@row
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!