Put the numbers at the same scale

[#1]

Hi,

I've a large amount of datas with different formats and I want that all numbers have the same format. I know that exists the option above :

But I want All the number in Million (K). Because I have metrics in  BILLION and other in Million and to show both in same chart it doesn't make the analysis simple...

I am trying to use a expression like:

=IF(LEN(TRIM(SUM(FIELD1)))> 6 AND

       LEN(TRIM(SUM(FIELD1)))< 9,

            SUM(FIELD1)/1000,

                 if(LEN(TRIM(SUM(FIELD1)))> 8 AND

                      LEN(TRIM(SUM(FIELD1)))< 11,

                           SUM(FIELD1)/1000000,

                           SUM( FIELD1)))

But this expression counts the 'commas' too so it is not very effective

There exists a better way to do this??

[robert_mika]

Try to use Replace() to take off the comas

Trim only takes out empty spaces

LEN(TRIM(REPLACE(SUM(FIELD1),',','')))> 6....

[settu_periasamy · Accepted solution]

May be try this

=if(SUM(FIELD1) >= 1000000000, SUM(FIELD1)/1000000,

        IF(SUM(FIELD1) >= 10000000 ,

            SUM(FIELD1)/1000,                    SUM(FIELD1)))

[#4]

Settu_periasmy,

Thanks You just keep it simple

[settu_periasamy]

if it is worked can you mark it as correct or helpful and close this thread..