14 Replies Latest reply: Aug 2, 2017 1:17 PM by Sunny Talwar

# Display decimals without condensing or trailing zeros

Without formatting, numbers are condensing after 5 decimal places.

Now I can format this to show all the numbers formatted to 10 decimal places, but I don't want the trailing zeros.

Is it possible to do this with only formatting functions? I'd rather not use string functions to remove the excess zeros.

=ValueLoop(1,10)Desired Result
10.1
2

0.01

30.001
40.0001
50.00001
60.000001
70.0000001
80.00000001
90.000000001
100.0000000001

Thanks.

• ###### Re: Display decimals without condensing or trailing zeros

=Num(0.00200, '##')

• ###### Re: Display decimals without condensing or trailing zeros

Is this not the same as applying no formatting? I'm still getting e notation after 5 decimal places.

• ###### Re: Display decimals without condensing or trailing zeros

Apply Num(X,'##')

where X is expression used by you above as Num(1/pow(10,ValueLoop......,'#.#########')

• ###### Re: Display decimals without condensing or trailing zeros

Use

=Num(0.00200, '(dec)')

• ###### Re: Display decimals without condensing or trailing zeros

This seems like it technically should work, but it seems to be manipulating the actual results somehow.

• ###### Re: Display decimals without condensing or trailing zeros

Use this

=Num(1/pow(10,ValueLoop(1,10)),'#.'&Repeat('#',ValueLoop(1,10)))

• ###### Re: Display decimals without condensing or trailing zeros

Thanks Pratyush, but I need a way to format the decimals in general, not just for this valueloop example.

• ###### Re: Display decimals without condensing or trailing zeros

Unfortunately, I think I'm going to have to use string functions to remove the trailing zeros.

This is the best I could come up with:

Replace(RTrim(Replace(Num(1/pow(10, ValueLoop(1,10)),'#.##########'),'0',' ')),' ','0')

• ###### Re: Display decimals without condensing or trailing zeros

Hi Philip

Maybe you can build a logic like, lets say your value is

N = 12.0000008000

You can find no. Of significant digits after decimal

Use N-floor(N) =. 0000008000

Divide this number by 1, 1/.0000008000=1250000

Use log of this number,  which returns 6.09, use ceil (6.09) which returns 7

Use this in the repeat function.

Hence your expression looks like

Num(N,'#.'&repeat ('#', ceil(log (1/(N-floor(N) )))))

THIS should work.

I couldnt try this out, sorry as I am travelling but something like this would definitely work for you.

Please check the expression before you use.

Regards

Pratyush

• ###### Re: Display decimals without condensing or trailing zeros

Try this may be

=Num(Num(1/pow(10, ValueLoop(1, 10)), '##.##############'), '##.' & Repeat('#', FindOneOf(Mid(Num(1/pow(10, ValueLoop(1, 10)), '##.##############'), Index(Num(1/pow(10, ValueLoop(1, 10)), '##.##############'), '.')), '123456789', -1)-1))

• ###### Re: Display decimals without condensing or trailing zeros

Thanks Sunny, but I think if I have to use string functions, I'll probably just stick to removing the trailing zeros like this: Replace(RTrim(Replace(Num(1/pow(10, ValueLoop(1,10)),'#.##########'),'0',' ')),' ','0')

• ###### Re: Display decimals without condensing or trailing zeros

Make sense.... there was one time when I thought that # meant optional and 0 meant definitive. But it seems that it might have gotten changed in one of the releases (I thought this would have worked ##.############, but it did not)

• ###### Re: Display decimals without condensing or trailing zeros

That's also what I had originally assumed, but I've never been able to see a difference between the two. Hopefully in a future release we will get a functioning optional character.

• ###### Re: Display decimals without condensing or trailing zeros

Yup, I agree