Is there any way to get the nearest rounded off hundred, thousand and more?

[Pratyay_Rakshit]

Let’s say I have a number 546821.

I want to output the nearest ‘hundred thousand’, i.e. 550000, or the nearest ‘point something’ million ( I don’t know what else to call it ), i.e 600000.

If there was a way to count the digits, say a hypothetical countdigit() function, then I could’ve done it.

countdigit(546821)=6, say A
power(10, (A-1)) = 100000, say B
ceiling(546821/B)=6, say C
C*B = 600000

Rounding off to the nearest hundred thousand would require A-2 in the second step, and a few changes thereafter.

So, is there a way to count the number of digits?

Or is there any other way to achieve what I want, nevertheless?

[Suvrutt_Gurjar · Accepted solution]

@Pratyay_Rakshit,

Please explore if following helps. This is certainly not the best or most efficient expression but hopefully may give some starting point.

The expression for the NEAREST NUMBER is as follows

NUMBER(CEILING([INPUTNUMBER]/POWER(10, NUMBER(LEN(TEXT([INPUTNUMBER])))-1))*POWER(10, NUMBER(LEN(TEXT([INPUTNUMBER])))-1))

Please note both INPUTNUMBER and NEARESTNUMBER are number type columns.The expression works if there is NO thousands seperator in the input number. Other variations to arrive at the nearest lower or higher are of course possible. Of course I believe, more stringent requirements will make the expression that much more complex.

Some test results are as follows

Hope this helps.

[Pratyay_Rakshit]

This works.

[Steve]

Expression	Result
(ROUND(546821 / 10.0) * 10)	546,820
(ROUND(546821 / 100.0) * 100)	546,800
(ROUND(546821 / 1000.0) * 1000)	547,000
(ROUND(546821 / 10000.0) * 10000)	550,000
(ROUND(546821 / 100000.0) * 100000)	500,000
(ROUND(546821 / 1000000.0) * 1000000)	1,000,000