XL2K3: 1 in 1,000 1

[MeGustaXL]

OK I admit I'm dense, but how do I express a result such as 0.10564 as "X in 1,000" (or 10,000 in a Million or whatever)?

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[AnotherHiggins]

Are you looking for something like this?
[tab]=A1*1000 & " in a thousand"


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[MeGustaXL]

Ha-Ha!!!! [ROFL]

Of course! I said I was dense - thanks John and have a STAR.

You can have another one if you can remind me how to scale it to integers in a variable number of thousands, so that 0.0005 becomes "5 in 10,000" and 0.00002 is "2 in 100,000"

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[AnotherHiggins]

=int(A1*1000) & " in a thousand"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[MeGustaXL]

Hmmm... Nearly

But that would make 0.00002 "0 in a thousand". I'd like to automatically change the 1000 in =int(A1*1000) & " in a thousand" to 100,000. Or whatever.

Chris

So you ride yourselves over the fields,
and you make all your animal deals,
and your wise men don't know how it feels...

Ian Anderson

 

[GlennUK]

Something like@

=INT(A1*10^ROUNDUP(ABS(LOG10(A1)),0))&" in a "&TEXT(10^ROUNDUP(ABS(LOG10(A1)),0),"#,##0")

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

Nice, Glen. I had already been working on something more complicated which I'll go ahead and post since I think Chris wants the "in a thousand" part spelled out.

- - -

Excel doesn't have a built-in function to convert numbers to words. I have some VBA code that will do it, but it is probably overkill for your situation since it works for all numbers 1 through 999,999.

Here's an idea, though:

Start by pasting this table into another sheet:
[tt]
# Dec.
Places Multiplier Spelled Out
1 10 Ten
2 100 One Hundred
3 1,000 One Thousand
4 10,000 Ten Thousand
5 100,000 One Hundred Thousand
6 1,000,000 One Million
7 10,000,000 Ten Million
8 100,000,000 One Hundred Million
9 1,000,000,000 One Billion
10 10,000,000,000 Ten Billion
11 100,000,000,000 One Hundred Billion
[/tt]
Let's use a named range for that table - we'll call it tblDecimalConversion.

Now over on the main sheet, if your number is in A1, you can use this formula:
[tab][COLOR=blue white]=A1 * VLOOKUP((LEN(A1)-(LEN(INT(A1))+1)), tblDecimalConversion, 2) & " in " & VLOOKUP((LEN(A1)-(LEN(INT(A1))+1)), tblDecimalConversion, 3)[/color]
(I'm assuming that the value in A1 will never be >=1)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[MeGustaXL]

Wow!

Glenn = STAR
John = STAR also

Both superb, both do what I want.

Admittedly, I started out spelling "in a thousand" simply because that's how you say it if you read it out loud. Glenn's answer gives the numbers perfectly, but John's even saves me the bother of typing! Result!

Thanks Guys