I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given 3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data

Assuming the data is in column A
=IF(RIGHT(A1,1)="1",A1&"c",IF(RIGHT(A1,1)="5",A1&" b",IF(RIGHT(A1,1)="9",A1&"a","")))

Now this only handles the .1, .5, and .9 as specified
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given 3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data

Try this
=INT(A1)&CHOOSE(MOD(A1*10,10)+1,"x","c","x","x","x ","b","x","x","x","a")
I intially used MOD(A1,1) but this gave round-off errors
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John Mac" <John wrote in message
...
I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given
3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data

Many thanks, that has worked but what I now need is for the decimal place to
be replaced by the letter eg 5.5 becomes 5b

"John Bundy" wrote:

Assuming the data is in column A
=IF(RIGHT(A1,1)="1",A1&"c",IF(RIGHT(A1,1)="5",A1&" b",IF(RIGHT(A1,1)="9",A1&"a","")))

Now this only handles the .1, .5, and .9 as specified
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given 3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data

No problem, just a long chunk
=IF(RIGHT(A1,1)="1",LEFT(A1,LEN(A1)-1)&"c",IF(RIGHT(A1,1)="5",LEFT(A1,LEN(A1)-1)&"b",IF(RIGHT(A1,1)="9",LEFT(A1,LEN(A1)-1)&"a","")))

This makes it 3.a

=IF(RIGHT(A1,1)="1",LEFT(A1,LEN(A1)-2)&"c",IF(RIGHT(A1,1)="5",LEFT(A1,LEN(A1)-2)&"b",IF(RIGHT(A1,1)="9",LEFT(A1,LEN(A1)-2)&"a","")))

This is 3a
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

Many thanks, that has worked but what I now need is for the decimal place to
be replaced by the letter eg 5.5 becomes 5b

"John Bundy" wrote:

Assuming the data is in column A
=IF(RIGHT(A1,1)="1",A1&"c",IF(RIGHT(A1,1)="5",A1&" b",IF(RIGHT(A1,1)="9",A1&"a","")))

Now this only handles the .1, .5, and .9 as specified
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given 3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data

Thanks Bernard - I see what you mean about round off errors. This has worked
brilliantly!
Sorry another question - if I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round off
the answer to the nearest of those required decimal places?

"Bernard Liengme" wrote:

Try this
=INT(A1)&CHOOSE(MOD(A1*10,10)+1,"x","c","x","x","x ","b","x","x","x","a")
I intially used MOD(A1,1) but this gave round-off errors
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John Mac" <John wrote in message
...
I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given
3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data

Many thanks for your help - have a good New Year

"John Bundy" wrote:

No problem, just a long chunk
=IF(RIGHT(A1,1)="1",LEFT(A1,LEN(A1)-1)&"c",IF(RIGHT(A1,1)="5",LEFT(A1,LEN(A1)-1)&"b",IF(RIGHT(A1,1)="9",LEFT(A1,LEN(A1)-1)&"a","")))

This makes it 3.a

=IF(RIGHT(A1,1)="1",LEFT(A1,LEN(A1)-2)&"c",IF(RIGHT(A1,1)="5",LEFT(A1,LEN(A1)-2)&"b",IF(RIGHT(A1,1)="9",LEFT(A1,LEN(A1)-2)&"a","")))

This is 3a
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

Many thanks, that has worked but what I now need is for the decimal place to
be replaced by the letter eg 5.5 becomes 5b

"John Bundy" wrote:

Assuming the data is in column A
=IF(RIGHT(A1,1)="1",A1&"c",IF(RIGHT(A1,1)="5",A1&" b",IF(RIGHT(A1,1)="9",A1&"a","")))

Now this only handles the .1, .5, and .9 as specified
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"John Mac" wrote:

I wish to assign text to data values - for example if a cell contains 3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given 3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original data

Thus does it
=INT(A1)+CHOOSE(MOD(A1*10,10)+1,1,1,1,5,5,5,5,9,9, 9)/10
but you will need to change to 1,1,1,.....9 part to fit YOUR rules
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John Mac" wrote in message
...
Thanks Bernard - I see what you mean about round off errors. This has
worked
brilliantly!
Sorry another question - if I am averaging a set of data and only want the
answer to have the decimal place of either .1, .5, or .9 how can I round
off
the answer to the nearest of those required decimal places?

"Bernard Liengme" wrote:

Try this
=INT(A1)&CHOOSE(MOD(A1*10,10)+1,"x","c","x","x","x ","b","x","x","x","a")
I intially used MOD(A1,1) but this gave round-off errors
happy new year
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"John Mac" <John wrote in message
...
I wish to assign text to data values - for example if a cell contains
3.1
then I wish it to given the text equivalent 3c, 3.5 given 3b, 3.9 given
3a,
4.1 given 4c, 4.5 given 4b etc..
All this needs to be done in a seperate column next to the original
data