How to keep a value till the first two non zero numbers after decimal

Hello friends,

I have a column with numbers. What I want is if the value is greater than or equal to 1 then it should be rounded by two decimal points. And if the value is less than 1 then it should be rounded till the first two non-zero digits. Here is the example of the data and how the output should be?

Strength	Output
0.300	0.3
0.3300	0.3
0.0750000	0.075
0.075	0.075
0.001	0.001
0.0004	0.0004
0.00025	0.00025
0.000025000	0.00025
4.0023	4
23.2756	23.28

Can someone please help?

Workflow

Accepted answers

vizAlter

Hi @Pramod91 — Try this solution: (btw, how come "0.3300" to "0.3"?)

IF ToNumber([Strength]) >=1 THEN 
 ToString(Round(ToNumber([Strength]), 0.01))
ELSEIF ToNumber([Strength]) <1 THEN 
 REGEX_Replace([Strength], "([0-9.])([0]+$)", "$1") 
ELSE 
 [Strength]
ENDIF

All comments

jamielaird

Tougher than I expected!

Try this:

IF [Strength] >= 1 THEN Round([Strength],0.01)
ELSE '0.'+(PadLeft((Left(ToString(Round(ToNumber(PadRight(ToString(ToNumber(Replace(ToString([Strength]),'.',''))),3,'0')),10)),2)),(ToNumber(FindString(ToString([Strength]),Left((Left(ToString(Round(ToNumber(PadRight(ToString(ToNumber(Replace(ToString([Strength]),'.',''))),3,'0')),10)),2)),1)))),'0'))
ENDIF

The principal is:

If >= 1 round to 2 decimal places
If < 1 convert to a string, remove the '.' and convert back to a number (this gets rid of all the leading 0s and leaves everything from the first non-zero digit onwards) i.e. 0.0000000000789 -> 789
Round to 2 decimal places - we now have the final two non-zero characters including any rounding i.e. 789 -> 79
Calculate the number of leading zeros by finding the position of the first non-zero character in the string version of the original value (e.g. the 7 is character 13)
Pad with the appropriate number of zeros and prepend with '0.' then convert back to a number e.g. '0.' + '000000000079' -> 0.000000000079

There's a lot of switching between numeric and string datatypes within the formula and you could probably clean this up a bit

Give it a go and let me know if it works. NB row 8 of your suggested output didn't seem consistent with what you were asking for, so I assumed that you'd want to reflect the correct number of leading zeros in that case.

jdunkerley79

Interesting challenge

Should the second line expected answer be 0.33?

Using a little maths, I think:

round([Strength],min(0.01,pow(10,Floor(LOG10([Strength])) - 1)))

will do what you want

For all cases a minimum of 2 dp will be used. For smaller cases it will shift downwards as needed.

Have attached as a sample

round to 2 digits.yxmd

vizAlter

Hi @Pramod91 — Try this solution: (btw, how come "0.3300" to "0.3"?)

IF ToNumber([Strength]) >=1 THEN 
 ToString(Round(ToNumber([Strength]), 0.01))
ELSEIF ToNumber([Strength]) <1 THEN 
 REGEX_Replace([Strength], "([0-9.])([0]+$)", "$1") 
ELSE 
 [Strength]
ENDIF

Pramod91

Hey, thanks for the reply. But when I run the workflow, I get an error "Error: Formula (3): Parse Error at char(40): Type mismatch. String provided where a number is required. (Expression #1)"

Pramod91

@vizAlter Thank you so much. Your formula gives me the exact output I wanted.

Can you please explain a bit about the formula so that I can modify and use it in future for different cases?

Especially the the second part of the formula after Regex_

atcodedog05

Got to say you can do a lot of magic using Regex.

There is so much to explore

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703