Rounding to a specific number of significant figures

[NgYingXue]

Hi all, anyone has a solution towards rounding to a specific number of significant figures instead of decimal places?

Sample Data (3sf):

Input	Expected Output
123456789	123
0.00456789123	0.00458
1.0023456789	1.00

Thanks.

[danilang]

hi @NgYingXue 

Here's a solution, though it's not particularly elegant

 This workflow acts on the digits in the string representation of the number.  Split the digits to rows.  Find the first non-zero digit, excl. minus and period characters and count the digits from there on.  Then work backwards from the end to implement the rounding, stopping at the last significant digit.  Keep all the digits until the last significant, and concatenate.

At this point the Rounded number is still in string format.  If you convert it to a double, you lose the trailing 0's in the third record.

Dan

[elijah_tqj]

Hi @danilang

Thanks for the suggested workflow. It works for most cases but I have hit a slight roadblock when it comes to rounding FX rates, specifically rates with more than 6 dp and with multiple zeroes.

For example, an FX rate of 0.000024 would be able to be rounded to 3 sigfigs using your workflow as it's still within the 6 dp auto-rounding logic by Alteryx. However, an FX rate of 0.0000238 would not be able to be rounded to 3 sigfigs based on the workflow as Alteryx would auto-round the number to 2.38e-05 before parsing and processing.

This causes issues when we convert the Double format to V_WString format as Alteryx would proceed to process it as 2|.|3|8|e|-|0|5, and produce an output of 2.38 instead of the expected 0.0000238 (rounded to 3 sigfigs)

Was hoping to get your advice on this scenario. Feel free to reach out if you require more clarification on my question.

Thank you in advance!

[Hipponoceros]

I figured out a solution with one Formula tool. I showed it with multiple formulas so you can see the results, but you could nest it into one. 

The Log10() function rounded down with Floor() basically gives the position of the leading digit in front or behind the decimal. 

Using that, move the decimal to have the desired number of digits before the decimal. 

Then round to a whole number which gives your significant digits.  Then move the decimal back to its original position.