Hi John

I'm not sure what you mean here.
The Mod(5.1,1)*10+1,0) returns 2, which Choose then correctly selects
"a" from the series as a result.

--
Regards

Roger Govier


"John Mac" wrote in message
...
Roger

Using this method:
=CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i")
how
do you get to replace the decimal without losing the whole number (ie.
5.1
becomes 5a)

"Roger Govier" wrote:

Hi Bernard

Yes, I get the same result as you with XL2003.
With the series 2.0,2.1 etc through to 3.0 in A1:A11 I get the letter
series
z,a,b,b,c,e,f,g,g,i,z
whereas with your second formula I get the correct series
z,a,b,c,d,e,f,g,h,i,z

Looking at the =MOD(A1,1)*10+1 part of the formula and the
=MOD(A1*10,10)+1without the Choose function the return is the series
of
numbers 1,2,3,4,5,6,7,8,9,10,1 in both cases, with no detectable
difference even when extending decimal places to 30.

It therefore appears as though Choose is giving rise to the error.

Amending the first formula to
=CHOOSE(ROUND(MOD(A1,1)*10+1,0),"z","a","b","c","d ","e","f","g","h","i")
gives the correct results.
As you say, very odd indeed.

I would also like to send best wishes to you (and everyone else) for
the
New Year.


--
Regards

Roger Govier


"Bernard Liengme" wrote in message
...
To help answer a question on this group, I wanted to convert the
decimal part of a number (in the set 1, 1.1, 1.2, ...... 9.7, 9.8,
9.9 ) to a letter. Thus 2.1 would yield a, 2.2 would give b.

I experimented with
=CHOOSE(MOD(A1,1)*10+1,"z","a","b","c","d","e","f" ,"g","h","i")
but it did not quite work (for example 3.8 gave 'g' and not 'h')
So I tried
=CHOOSE(MOD(A1*10,10)+1,"z","a","b","c","d","e","f ","g","h","i")
and this worked.

Odd since, at the integer level, =MOD(A1,1)*10+1 and
=MOD(A1*10,10)+1
give the same result.

Using the Evaluate Formula tool (I work with XL 2003) I was unable
to
see why my original formula did not work.
=CHOOSE(MOD(3.8,1)*10+1,"z","a","b","c","d","e","f ","g","h","i")
yields
=CHOOSE(9,"z","a","b","c","d","e","f","g","h","i") but the next
result
is g not h
Very odd!!!
Happy New Year to all
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email