Millions and Thousands in Custom format

[ecrodrig]

I am trying to do a custom format were the output on my cell will be in Millions if is and in thousands, if it isn't


Example : the number 18699 should Come up as 18.7K and 78663.88 should come up as 0.1M

I am sure there is a why of combing the formulas within the Custom format field but I can't seem to make it work.

I've tried this:

[>=1000000] #,##0.0,, "M" ; [<1000000] #,##0.0, "K"

But my answers are all coming up in K its like its disregarding the first condition.

Any help is appreciated. Thanks

 

[Kerryx]

Is the value 78663.88 correct that is 21K away from 0.1M(100,000K) you want to round up to ?

 

[ecrodrig]

yes, I would round it up

 

[Kerryx]

So unless you change the >=value for example >=70000 it will always show as K, the order goes as follows checks first to see if the value is >=1000000 will then round and add M, if not true then everything else is rounded and has K added to it
[>=1000000] #,##0.0,, "M" ; [<1000000] #,##0.0, "K"

so in order to do what you need the custom format needs to be for example
[>=75000] #,##0.0,, "M" ;[<75000] #,##0.0, "K"
you can change the 75000 to anything you want

Book1
	B	C
2	18699	18.7 K
3	78663.88	0.1 M
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=B2