format number to go 6 decimal places on the right

[Former Member]

I am currently using: =Sum(ToNumber([Tons Emitted])) + FormatNumber([Tons Emitted];"######.######")

I also tried =Sum([Tons Emitted]) + FormatNumber([Tons Emitted];"######.######")

I am getting the results to where half of my numbers are right, the other half it repeats the number as: 12345.612345.6

and I get values with two decimal places like:0.62.617708

What can I do to take a sum of numbers and have them show the number going out at a minimum of 6 decimals to the right??

Thank you

Angela

[mhmohammed · Accepted solution]

Hi Angela,

Please disregard my previous post.

Question: Why are you using Sum() + Formatnumber()?

Instead, can you just use =Formatnumber(Sum([Tons Emitted]);"#,##0.000000")

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

Thank you! That worked.

[Former Member]

Hi Angela,

Formatnumber function converts the output to text and this is unnecessary load if you want this format only for display purpose. So for better performance don't use the formula instead right click the column displaying these values --> Format number -->Custom --> mention the format what you want i.e. #,##0.000000

[mhmohammed]

Hi Angela,

Try this:

=Sum(Formatnumber(ToNumber([Tons Emitted]);"######.000000")

Thanks,

Mahboob Mohammed

[Former Member]

When I do that I get: The expression or sub-expression at position 5 in the 'Sum' function uses an invalid data type. (IES 10037)

[Former Member]

Hi Angela,

As i mentioned in earlier post the return type of the function Formatnumber is string that is the reason you are getting invalid data type error as you cannot apply sum on string.

did you try the solution which i mentioned earlier?

[Former Member]

I was able to get it to work using the response from Mahboob, I did look to try this option you gave me but I don't have an option to Format Number from the right click menu.

[Former Member]

Hi angela,

You want to do sum and apply formatting on this fied "ToNumber([Tons Emitted])"??

then create a variable/formula as Sum(ToNumber([Tons Emitted]))

Then if you right click the column displaying this value you will get Format number option.

If you apply format number function and right click the column you will see only Format text option. Basically it will vary based on the data type of the column