Is there a way to stop percentage from converting in a formula?

sharkasits

I have two columns Value and Units that individuals are filling out. Some rows will have percentages for the Value and others will have numbers. I have third column Current Value that combines Value and Units, but when someone enters a percentage

Example:

• Value = 15.25%
• Unit = "women in Group A"
• Current Value Formula = [Value]@row + " " + [Unit]@row
• The formula converts that to ".1525 women in Group A".
• What I want it to convert to "15.25% women in Group A"

MichaelTCA

Hello,

When using a simple function like this with a mix of text and numbers, the function converts the cell format to a text (string). Removing the % format for numbers. This is definitely something that needs to be addressed by Smartsheet, if possible.

From a programming standpoint, I understand the separation, but even if a cell is a % being changed to text, it should be converted properly. The code would have to be updated if this were to be "fixed".

Attributes cannot be both a text and number or the software will fail.

You can try using a helper column:

None of the cells are formatted as a % in this example.

I usually hide the helper columns if there's multiple people viewing the same sheet.

Samuel Mueller

@sharkasits would something like this work then?

If(value@row<=1, value@row*100+"% "+unit@row, value@row + unit@row)

If someone enters 0.25 it will return 25% ..., or if someone enters 25, it will return 25 ....

Unless you can have a value that's less than 1 that's not a percent, or a value greater than 1 that is a percent. Otherwise you will need the checkbox stating this is a percent.

Samuel Mueller

@sharkasits You probably have to do the conversion manually

=value@row*100+"% "+unit@row

sharkasits

Thanks @Samuel Mueller that would work if all the values were percentages, but many aren't so I can't apply that logic to the full column. Do you know if there is a way to identify if the value entered is a percentage?

I tried using

FIND("%", [Value]@row) 

but it still returns 0 when the % is entered.

Samuel Mueller

@sharkasits You could do if the value is <= 1 maybe. But it appears that if it's an actual number the percent does not appear in a find formula. It's just a formatting thing.

Toufong Vang

@sharkasits, this will work for any numeric value in the "Value" column.

=IF(Value@row = INT(Value@row), Value@row + "00.00% ", INT(Value@row * 100) + "." + MID((Value@row + "0000"), FIND(".", Value@row + "0000") + 3, 2) + "% ") + Unit@row

Explanation

Value@row = INT(Value@row) checks to see if the value is a whole number (an integer). If so, simply append "00.00%" to it. --- Value@row + "00.00% "

If the value is not a whole number, then:

(1) Multiply the value by 100 and use INT() to grab everything left of the decimal ---INT(Value@row * 100)

(2) Append "0000" to the original value and return the 2 numbers that are 3 positions from where we find the "." --- MID((Value@row + "0000"), FIND(".", Value@row + "0000") + 3, 2)

(3) Concatenate the result from #1, "." , result from #2, and "% "

Append Unit@row to the result of the IF() statement.

Cheers!

MichaelTCA

Hello,

When using a simple function like this with a mix of text and numbers, the function converts the cell format to a text (string). Removing the % format for numbers. This is definitely something that needs to be addressed by Smartsheet, if possible.

From a programming standpoint, I understand the separation, but even if a cell is a % being changed to text, it should be converted properly. The code would have to be updated if this were to be "fixed".

Attributes cannot be both a text and number or the software will fail.

You can try using a helper column:

None of the cells are formatted as a % in this example.

I usually hide the helper columns if there's multiple people viewing the same sheet.

sharkasits

Thank you @MichaelTCA. It sounds like I would have to put it to the user to identify if the value is a percentage with a checkbox or something.

The API has displayName which shows the the value as a string. I guess I could do some helper columns and run a job outside to update the percentages, but the values wouldn't be updated real time. I wish we had access to displayName in the formulas for things like this.

MichaelTCA

@sharkasits That would work. A nested IF function in the helper column can evaluate what to return depending on the checkbox and input.

Honestly, I don't know much about the API in Smartsheet.

I hear ya though! Or at the very least, a function to convert the type.

You could try this too. Another way to use helper columns that I like to call "The Long Way". Use the JOIN function to combine them. The JOIN1 column; there is no delimiter so there's nothing between the values. The JOIN2 column has a " " delimiter.

I'm writing out a sentence in the columns and using the JOIN function. It's kind of like a back door. 🙂 The only inputs you would need is the value and unit. The output would be from the JOIN2 column.

Toufong Vang

@sharkasits, you don't necessarily need to ask the user if the value they entered is a string (e.g., 20.25%) or a numeric value (e..g., 0.2025).

If the values in the column Value contains a mix of strings and numbers, then you can create a helper column, (e.g., Value_Num) into which you can convert the cell entry into a numeric value using a formula like...

=IF(ISTEXT(Value@row), IFERROR(VALUE(SUBSTITUTE(Value@row, "%", "")) / 100, VALUE(Value@row)), VALUE(Value@row))

..and then use my updated formula..

=IF([Value_Num]@row = INT([Value_Num]@row), [Value_Num]@row + "00.00% ", INT([Value_Num]@row * 100) + "." + MID(([Value_Num]@row + "0000"), FIND(".", [Value_Num]@row + "0000") + 3, 2) + "% ") + Unit@row

Note, also, that Smartsheet number/column formatting can obscure the fact that some entries are strings while others are numeric. In the screenshot below, the circled entries remain strings while the rest are numeric. The formula in Value_Num converts the string values into numeric values so that the formula in COL10 works as you originally requested.

Toufong Vang

sharkasits

@Toufong Vang the problem I have is that the field can contain percentages or non percentage numbers. So sometimes someone so it could be Value: 12 Units: Dogs -> 12 Dogs or Value: 12% Units: Dogs -> 12% of Dogs.

Samuel Mueller

@sharkasits would something like this work then?

If(value@row<=1, value@row*100+"% "+unit@row, value@row + unit@row)

If someone enters 0.25 it will return 25% ..., or if someone enters 25, it will return 25 ....

Unless you can have a value that's less than 1 that's not a percent, or a value greater than 1 that is a percent. Otherwise you will need the checkbox stating this is a percent.

sharkasits

https://community.smartsheet.com/discussion/comment/356403#Comment_356403

I don't have enough control over what gets entered. It's a wide array of metrics that changes from year to year. I think the checkbox is the best solution.

MichaelTCA

@Toufong Vang @sharkasits I like the process Toufong Vang uses. From a programming stand point, the functions can do a lot more than convert the view of a decimal to percent. Thank you for sharing that! I'm definitely writing them down to reference in the future.

For user inputs, a checkbox can make it easier. Having the boolean variable can also make conditions in analyzing data further along easier and with shorter formulas. Having a standard input like 15.25 instead of .1525 would also help remove extra columns and require less formulas.

The trick will be to use cross-references and columns as little as possible if you're going to be analyzing the data from the same sheet for years to come. Smartsheet has its limits.