Creating a formula with a drop down box of percentages

Leslye Jackson

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

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.

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.

David Joyeuse

Hi @Leslye Jackson

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.

Leslye Jackson

@David Joyeuse Thank you so much for your feedback however if I remove the Count function I get an #invalid operation error

David Joyeuse

Ah Probably because your % completed column is a dropdown list. Try this:

=VALUE([% Completes]@row)*[Weight Complete]@row

Leslye Jackson

@David Joyeuse I am so thankful for your feedback however this gave me an error as well

lrmerlino88886

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

Leslye Jackson

Its like I need to get the formula to recognize the different values within the drop down box column

Leslye Jackson

@lrmerlino88886 Thank you so much for your feedback unfortunately I still got the error

Leslie Merlino

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.

Leslye Jackson

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

:)

Leslie Merlino

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

Leslye Jackson

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

Leslie Merlino

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?

Leslye Jackson

Yes I have them listed as percentages

Leslie Merlino

I used this and it works no matter how I have the dropdown set:

Leslye Jackson

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