Number Notation

[AnthonyDixon]

Is there a calculation or custom function that can accomplish the following number notation?

1                    -->    $1
10                  -->    $10
100                -->    $100
1000              -->    $1K
10000            -->    $10K
100000          -->    $100K
1000000        -->    $1M
10000000      -->    $10M
100000000    -->    $100M

Thank you

Anthony

[Steve Martino]

This can be parsed with a calculation.  A few questions about what you expect to see for results. Are these the only results?

1                    -->    $1
10                  -->    $10
100                -->    $100
1000              -->    $1K
10000            -->    $10K
100000          -->    $100K
1000000        -->    $1M
10000000      -->    $10M
100000000    -->    $100M

or will you have numbers like 295899?  If so , do you want those numbers rounded up-295K or 296K?

[Lee Smith]

Hi Anthony and welcome to the FM Forum,

 

1 hour ago, AnthonyDixon said:

Is there a calculation or custom function that can accomplish the following number notation?

 

Here are a couple of cf’s that may work for you, however, I’m not sure what you are really trying to accomplish.

http://www.briandunning.com/cf/244

http://www.briandunning.com/cf/393

 

[AnthonyDixon]

1 hour ago, Steve Martino said:

This can be parsed with a calculation.  A few questions about what you expect to see for results. Are these the only results?

1                    -->    $1
10                  -->    $10
100                -->    $100
1000              -->    $1K
10000            -->    $10K
100000          -->    $100K
1000000        -->    $1M
10000000      -->    $10M
100000000    -->    $100M

or will you have numbers like 295899?  If so , do you want those numbers rounded up-295K or 296K?

Yes, having the numbers rounded up would be great.

[comment]

It seems to me that there are two parts to this question:

1. How to round the number to the nearest (?) power of 10;
2. How to format the result as units, thousands or millions.

Note that I am perforce guessing about the first part, since you only gave examples of rounded numbers and did not explain the underlying logic.

Try the following calculation =

Let ( [
magnitude = Case ( number = 0 ; 0 ; Floor ( Log ( Abs ( number ) ) ) ) ;
r = Round ( number ; -magnitude ) 
] ;
Case (
r ≥ 10^6 ; Div ( r ; 10^6 ) & "M" ;
r ≥ 10^3 ; Div ( r ; 10^3 ) & "K" ;
r )
)

The result, of course, must be Text.

Edited May 3, 2016 by comment

[AnthonyDixon]

2 hours ago, comment said:

It seems to me that there are two parts to this question:

1. How to round the number to the nearest (?) power of 10;
2. How to format the result as units, thousands or millions.

Note that I am perforce guessing about the first part, since you only gave examples of rounded numbers and did not explain the underlying logic.

Try the following calculation =

Let ( [
magnitude = Case ( number = 0 ; 0 ; Floor ( Log ( Abs ( number ) ) ) ) ;
r = Round ( number ; -magnitude ) 
] ;
Case (
r ≥ 10^6 ; Div ( r ; 10^6 ) & "M" ;
r ≥ 10^3 ; Div ( r ; 10^3 ) & "K" ;
r )
)

The result, of course, must be Text.

Thank you so much for your help. Just what I was looking for. Originally I was just
looking for the notation for any number that fell into that range but your suggestion
of rounding up makes for a much better result for what I was trying to accomplish.

Thanks again
Anthony

 

[comment]

5 minutes ago, AnthonyDixon said:

your suggestion of rounding up makes for a much better result

Please note that contrary to the suggestion made by @Steve Martino, I am not rounding up, but to the nearest boundary.

For example, 949 is rounded down to "900", while 950 will be rounded up to "1K" - keeping with the most common rounding method of "round half up".

[AnthonyDixon]

11 hours ago, comment said:

Please note that contrary to the suggestion made by @Steve Martino, I am not rounding up, but to the nearest boundary.

For example, 949 is rounded down to "900", while 950 will be rounded up to "1K" - keeping with the most common rounding method of "round half up".

Yes this is perfect, Thank you again.