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

sharkasits · 01/06/23

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 · 01/06/23

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 · 01/09/23

@sharkasitswould 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 · 01/06/23

@sharkasitsYou probably have to do the conversion manually

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

sharkasits · 01/06/23

Thanks@Samuel Muellerthat 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

找到(“%”,[价值]@row)

but it still returns 0 when the % is entered.

Samuel Mueller · 01/06/23

@sharkasitsYou 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 · 01/06/23

@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 · 01/06/23

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 · 01/06/23

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 · 01/07/23

@sharkasits这将工作。如果函数在helpe嵌套r 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 · 01/07/23

@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 columnValuecontains 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 anumeric valueusing 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 inValue_Numconverts the string values into numeric values so that the formula inCOL10works as you originally requested.

Toufong Vang · 01/07/23

sharkasits · 01/09/23

@Toufong Vangthe 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 · 01/09/23

@sharkasitswould 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 · 01/09/23

@sharkasits<\/a> would something like this work then?<\/p>

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

If someone enters 0.25 it will return 25% ..., or if someone enters 25, it will return 25 ....<\/p>

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.<\/p>","bodyRaw":"[{\"insert\":{\"mention\":{\"name\":\"sharkasits\",\"userID\":156473}}},{\"insert\":\" would something like this work then?\\nIf(value@row<=1, value@row*100+\\\"% \\\"+unit@row, value@row + unit@row)\\nIf someone enters 0.25 it will return 25% ..., or if someone enters 25, it will return 25 ....\\nUnless 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. \\n\"}]","format":"rich","dateInserted":"2023-01-09T15:40:42+00:00","insertUser":{"userID":137019,"name":"Samuel Mueller","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Samuel%20Mueller","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B9G8B70WXQWC\/nKTI46EH9WQOM.jpg","dateLastActive":"2023-08-25T18:19:58+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/356403#Comment_356403","embedType":"quote"}"> 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 · 01/09/23

@Toufong Vang @sharkasitsI 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 columnsas little as possibleif you're going to be analyzing the data from the same sheet foryears to come. Smartsheet has its limits.

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

Best Answers

Answers

Explanation

Categories