Rounding Thousands/Millions/Billions

[zjohnw]

I'm trying to format large dollar values to $0.0K, $000.0M, and $000.0B formats without it rounding up in the ten thousands digit or ignoring the five numbers to the left of the decimal.

Numbers I have: ........Want ............. What I Get
$989,999 ................. $0.9K ............. $1.0K
$111,496,000 ........... $111.4M ......... $111.5M
$111,496,000,000 ..... $111.4B ......... $111.5B

I'm using conditional formating to get the $0.0K, $000.0M, and $000.0B formats.
<=1000000 ....................................... _($0.0,,"K"_)
>=1000000000 .................................. _($.0,,"M"_)
Between=1000000 and 1000000000 ..... _($.0,,,"B"_)

Need help on how to ignore the rounding up on each value?

Thanks
JW

 

[Nalani]

Welcome to the Board

Don't know if this will work for you or not.

Excel Workbook
	A	B
1	989,999	$0.9K
2	111,496,000	$111.4M
3	111,496,000,000	$111.4B
Sheet1

 

[zjohnw]

Thanks for the info I will see if I can get it to work for what I need. I have a lot of these figures that I need to change and the values are not in any order. Looking for one formula that can be used on every cell in a row. I will see if I can combine these to get one that work on combine with conditional format.

Thanks again for the help.

JW

 

[zjohnw]

Thanks for the info I will see if I can get it to work for what I need. I have a lot of these figures that I need to change and the values are not in any order. Looking for one formula that can be used on every cell in a row. I will see if I can combine these to get one that work on combine with conditional format.

Thanks again for the help.

JW

I gave this a try and A1 has the same result for $999,999 as it does for $9,999. I have rows that have values that are all dirrent between $0 and $999B.

 

[zjohnw]

Thank you for the help.

Below is what I used. There are probably better ways, but its works.

=IF(C18<1000000,"$"&(ROUNDDOWN((C18/1000000),1)&"K"),IF(C18>1000000000,"$"&(ROUNDDOWN((C18/1000000000),1)&"B"),"$"&(ROUNDDOWN((C18/1000000),1)&"M")))

Thanks
JW

 

[Nalani]

It might be me but I'm not clear on what exactly you are wanting.

Assuming:

K = Thousands
M = Millons
B = Billions

Numbers I have: ........Want
$989,999 ................. $0.9K ............. Wrong - .9K = 900
$111,496,000 ........... $111.4M ......... Correct
$111,496,000,000 ..... $111.4B ......... Correct

If you want the Thousands from 989,999 it would be 989 or possibly 989.9
And if you wanted the thousands from 9,999 it would be 9 or 9.9

I see that you want just the first 4 numbers for Million and Billion, just not sure of what is needed for < 1,000,000

Your formula here IF(C18<1000000,"$"&(ROUNDDOWN((C18/1000000),1)&"K"),

Returns $0K for 9,999

Maybe change to IF(C18<1000000,"$"&(ROUNDDOWN((C18/1000),1)&"K"),

 

[Armando Montes]

Try this:
=IF(A1>10^9,FLOOR(A1/10^9,0.1)&"B",IF(A1>10^6,FLOOR(A1/10^6,0.1)&"M",FLOOR(A1/10^6,0.1)&"K"))