Jump to content

This topic is 2885 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2885 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.