Convert Number To Decimal

[jgthtrotih]

<o></o>Hi All!
<o> </o>
I need a formula to take the numbers listed in cells D4:D50
<o> </o>
If below 1,000,000 than just keep that number value
<o> </o>
EXAMPLE DATA
<o> </o>
If the Ten Thousands place is 5 or below leave the same if it is higher round up. (use this concept for ALL PLACE VALUES

<o> </o>
1,500,000 to 1.5 million (if the rest are zeros)
<o> </o>
4,000,000 to 4 million
<o> </o>
1,564,535 than to 1.57 million
<o> </o>
1,545,535 than to 1.54 million
<o> </o>
1,555,000 than to 1.55 million
<o> </o>
Same concept for the rest as the Millions (move the rounded place up as needed)
<o> </o>
So for Billions it would be 10 Millions place (to round form) I think lol
<o> </o>
1,000,000,000 to 1 Billion
<o> </o>
1,500,000,000 to 1.5 Billion
<o> </o>
1,560,000,000 to 1.57 Billion
<o> </o>
1,578,000,000 to 1.58 Billion
<o> </o>
1,700,000,000 to 1.7 Billion
<o> </o>
I would like this to continue all the way up to place value 999 QUADILLION! (if possible) if the value can go bigger that would be great!

<o> </o>
<o></o>Return the answers in E4:E50


i think i explained my problem well but if not let me know!
<o></o>

 

[phxsportz]

Probably a slicker way to do it, but.. here ya go..

Sheet4

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 205px"><COL style="WIDTH: 123px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">1000000</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">1 Million</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">121500000</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">1.22 Billion</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">11500000000</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">115 Billion</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">1,750,000,000,000,000</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">1.75 Quadrillion</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">1,215,377</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">1.22 Million</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(LEN(A1) < 16,IF(AND(LEN(A1) > 6,LEN(A1) < 9),ROUND(A1/1000000,2) & " Million",ROUND(A1/100000000,2) & " Billion"), IF(LEN(A1) < 16,ROUND(A1/1000000000,2) & " Trillion",ROUND(A1/1000000000000000,2) & " Quadrillion"))</TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR><TR><TD></TD><TD></TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[bertie]

=IF(D4/10^6<100,ROUND(D4/10^6,2) & " million",ROUND(D4/10^9,2) & " billion")
</PRE>

 

[jgthtrotih]

hey thanks i like the way they work...but one problem.

when i enter a value of 60,000,000,000,000 (60 Trillion)

i get with Phxsportz formula 600000 Billion

with Betie formula 60000 Billion

for Phxsportz formula 1,234,567,890 i get 12.35 billion (1.23 billion)

betie works good there but when both formulas get to billion they start to mess up

 

[GTO]

Greetings,

I'm pretty much dog-poop at formulas, but taking from Bertie's and at your request of as far as it could be taken... I would think that I'd want to leave a 'bailout' IF, in case the value exceeds how many IF tests we can nest (for which I believe Chip Pearson has a workaround if you're really going to have numbers in the gajillions....)

Anyways, maybe?

Excel Workbook
	A	B	C
1	999,999	999,999	999,999
2	1,000,000	1.00 Million	1 Million
3	1,500,000	1.50 Million	1.5 Million
4	1,000,000,000	1.00 Billion	1 Billion
5	1,000,000,000,000	1.00 Trillion	1 Trillion
6	1,500,000,000,000,000	1.50 Quadrillion	1.5 Quadrillion
7	1,200,000,000,000,000,000	1.20 Quintillion	1.2 Quintillion
8	1,200,000,000,000,000,000,000	1,200,000,000,000,000,000,000	1,200,000,000,000,000,000,000
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=IF(A1/10^21>=1,A1,IF(A1/10^18>=1,TEXT(A1/10^18,"0.00")&" Quintillion",IF(A1/10^15>=1,TEXT(A1/10^15,"0.00")&" Quadrillion",IF(A1/10^12>=1,TEXT(A1/10^12,"0.00")&" Trillion",IF(A1/10^9>=1,TEXT(A1/10^9,"0.00")&" Billion", IF(A1/10^6>=1,TEXT(A1/10^6,"0.00")&" Million",A1))))))
C1	=IF(A1/10^21>=1,A1,IF(A1/10^18>=1,ROUND(A1/10^18,2)&" Quintillion",IF(A1/10^15>=1,ROUND(A1/10^15,2)&" Quadrillion",IF(A1/10^12>=1,ROUND(A1/10^12,2)&" Trillion",IF(A1/10^9>=1,ROUND(A1/10^9,2)&" Billion",IF(A1/10^6>=1,ROUND(A1/10^6,2)&" Million",A1))))))

Hope that helps,

Mark

Edit: I started with Text, but used Round in C Col. Whichever suits...

 

[jgthtrotih]

i love it works perfectly (i prefer Col C) it looks better.

Thank You Mark

just wondering can your forumla be change so that if 1000-999,999 ( just the hundred place) would show?

Ex

instead of saying 1000 it would say 1 Thousand

1100- would say 11 Hundred

2500- 25 Hundred

4500- 45 Hundred

10000- would say 10 Thousand
20000- 20 Thousand

100000- would say 100 Thounsand
200000- 200 Thousand

sorry i didn't add this at the beginning didnt know if i wanted it or not
Super Sorry!

Thanks For The Excellent Formulas

*ps need numbers to go soo high because im calculating light years (Space Figures)

 

[GTO]

Yeeks!

If 1000 results in '1 thousand', then 1100 could result in '1.1 thousand' as we were doing with larger values; or, we could have results such as '10 hundred' or '11 hundred'. To give mixed results in the range of 100-9900 (ie - '1 hundred'...'9 hundred', '1 thousand' to... '91 hundred'...'9 thousand') would seem a a bit painful to my poor brain leastwise.

There's a lot of nice folks here who are very knowledgeable in formula/worksheet functions though, so maybe someone will show us the way in this method.

In case not, would vba/udf be a consideration?

Mark

 

[jgthtrotih]

ya for sure...i have no problem with that... can vba/udf handle such a request?

can excel calculate over 999 quintillion? lol

 

[sanrv1f]

may be this,

Excel Workbook
	C	D
1	999999	999999
2	1000000	1 Mio
3	1500000	1.5 Mio
4	1000000000	1 Bio
5	1000000000000	1 Tri
6	1500000000000000	1.5 Qua
7	1200000000000000000	1.2 Qui
8	1200000000000000000000	1.2E+21
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jgthtrotih]

This does works but it doesnt return

when 10 Mio is in the cell it returns 1 Mio not 10 mio

same with bio tri qua qui..i like the formula tho