Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
BIG PROBLEM 2
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........................ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
BIG PROBLEM 2
=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" -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Wu" wrote: 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........................ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
BIG PROBLEM 2
On Thu, 30 Oct 2008 07:49:00 -0700, Wu wrote:
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........................ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |