FormatNumber ?!

[Former Member]

Hi,

When I use this formula:

=FormatNumber([Other Regions WI].[Netsalesaa];"###.###")

Result is:

######

Why?

If I use =FormatNumber([Other Regions WI].[Netsalesaa];"###_###")

Result is

###_###

Strange or am I missing something?

[Former Member · Accepted solution]

Try this

=Replace(FormatNumber(Other Regions WI.Netsalesaa;"###.###");",";".") you are first formatting the number and once the number is formatted with " , " present you use the replace function to replace " , " with " . "

[Former Member · Accepted solution]

It works!

Thank you

[Former Member]

Paulo Próspero,

while using the formatnumber formula the number of # values before the decimal are suppose to allow all the digits before decimal or else the function will round the value off. if you want the number 458957.33 to be displayed as 458957.3333 then your # should be #######.####. i.e 6 or more # before decimal.

the representation ####_### is not a valid decimal representation but the format number puts the _ from the left in the position where u have mentioned. its as simple as trying to show a 1000 as 1,000. to ask the formatnumber function to do the same is by using the formula formatnumber(obj;"#,###").

Thanks,

karthik

[Former Member]

Hi again,

The idea here is that Client wants that report displays numbers with . instead of , for separating thousands i.e:

432,882.66 should be 432.882,66

can this be done with a custom format or with FormatNumber ?

When I try both options I can't get the correct display

Thank you

[Former Member]

Hi,

I tried and came close enough but not a 100% but below code should help

=FormatNumber(OBJ;"###'.'##0.00")

what i was also thinking is seperate the digits after the decimal by some math formula and concatenate with formats u need

like say u have 432882.66 seperate it as 432882 and 66. Now co0ncatenate them 432.882","66

Thanks,

Karthik

[Former Member]

I also tried that one but... sorry

Because I can have 2, 3, 6, 9 or more digits, in this cell and by using this formula it can end with this layout:

.543.543 or .33 or .343