BIG PROBLEM 2

W

Wu

I have the following data,

1. 103
2. 1002
3. 205

The last digit of above number represent how many zero it is.
103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M)

How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M , 3491
to 3.49K ?

Not change to 10000, 2000000........................
 
J

John C

=CHOOSE(LOOKUP(INT(A1/10)*10^RIGHT(A1,1),{0,1;1000,2;1000000,3}),INT(A1/10)*10^RIGHT(A1,1),(INT(A1/10)*10^RIGHT(A1,1))/10^3&"K",(INT(A1/10)*10^RIGHT(A1,1))/10^6&"M")

Currently, will handle up to millions, if you need billions, just add a 4th
category in the lookup, and on the fourth choice, divide by 10^9 then add the
"B"
 
R

Ron Rosenfeld

I have the following data,

1. 103
2. 1002
3. 205

The last digit of above number represent how many zero it is.
103 equal to 10000 (10K), 1002 equal to 10000 (10k), 205 equal to 2000000(2M)

How to use the function to change 103 to 10K, 1002 to 10K, 205 to 2M , 3491
to 3.49K ?

Not change to 10000, 2000000........................
Click to expand...


Try to keep your questions in the same thread.

Using any of the routines that you were provided, you could Custom Format:

"[<1000]0_ ;[<1000000]0,K;0,,\M"
--ron
 
Top