Creating a formula with a drop down box of percentages

Leslye Jackson

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 :)

David Joyeuse

Hi @Leslye Jackson

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.

Leslye Jackson

@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.

David Joyeuse

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.

Paul Newcome

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.

Leslye Jackson

@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.

David Joyeuse

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 %.

Leslye Jackson

@David Joyeuse Thanks so much!! How did I do? I added a parenthesis in front of Value and removed the space between the quotations

Paul Newcome

You need to remove the 19 after [Weight Complete]. You should have either a row number OR @row, not both.

David Joyeuse

Ah nice one Paul, I missed that :)

Leslye Jackson

@David Joyeuse @Paul Newcome This change updated my error which is progress in my book :)

David Joyeuse

@Leslye Jackson

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.

Leslye Jackson

@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.

Paul Newcome

@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.

Leslye Jackson

@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