Formula error!!

[redforest]

Hi

I have a created a worksheet and within it I want to change a text cell

value to a corresponding number value. There are 22 different 'text'
scenarios with different number values. For example if a cell contains
the entry "P1" then the value given would be 1, or if the entry was
"P2(ii)" then the value would be 2.5 etc. I am able to do the first 7
text(s) successfully using the IF command but then recieve an error
(formula length, I think). Here is the formula that works . . .
=IF(W8="P1",1,IF(W8="P1(i)",1,
IF(W8="P1(ii)",1.5,IF(W8="P2",2,IF(W8="P2(i)",2,IF(W8="P2(ii)",2.5,IF(W8="P­3",3,"F")))))))

Unfortunately I don't know how to make it work to accept all of my

text values.


Can anyone please help


R

 

[Bob Phillips]

=VLOOKUP(W8,{"P1",1;"P1(i)",1;"P1(ii)",1.5;"P2",2;"P2(i)",2;"P2(ii)",2.5;"P­
3",3,"F"},2,False)

just add extra pairs

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hi

I have a created a worksheet and within it I want to change a text cell

value to a corresponding number value. There are 22 different 'text'
scenarios with different number values. For example if a cell contains
the entry "P1" then the value given would be 1, or if the entry was
"P2(ii)" then the value would be 2.5 etc. I am able to do the first 7
text(s) successfully using the IF command but then recieve an error
(formula length, I think). Here is the formula that works . . .
=IF(W8="P1",1,IF(W8="P1(i)",1,
IF(W8="P1(ii)",1.5,IF(W8="P2",2,IF(W8="P2(i)",2,IF(W8="P2(ii)",2.5,IF(W8="P­
3",3,"F")))))))

Unfortunately I don't know how to make it work to accept all of my

text values.


Can anyone please help


R

 

[Bob Phillips]

typo, meant

=VLOOKUP(W8,{"P1",1;"P1(i)",1;"P1(ii)",1.5;"P2",2;"P2(i)",2;"P2(ii)",2.5;"P­
3",3},2,FALSE)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hi

I have a created a worksheet and within it I want to change a text cell

value to a corresponding number value. There are 22 different 'text'
scenarios with different number values. For example if a cell contains
the entry "P1" then the value given would be 1, or if the entry was
"P2(ii)" then the value would be 2.5 etc. I am able to do the first 7
text(s) successfully using the IF command but then recieve an error
(formula length, I think). Here is the formula that works . . .
=IF(W8="P1",1,IF(W8="P1(i)",1,
IF(W8="P1(ii)",1.5,IF(W8="P2",2,IF(W8="P2(i)",2,IF(W8="P2(ii)",2.5,IF(W8="P­
3",3,"F")))))))

Unfortunately I don't know how to make it work to accept all of my

text values.


Can anyone please help


R

 

[Harlan Grove]

Bob Phillips wrote...

typo, meant

=VLOOKUP(W8,{"P1",1;"P1(i)",1;"P1(ii)",1.5;"P2",2;"P2(i)",2;"P2(ii)",2.5;
"P­3",3},2,FALSE)

....

OP seems to want a return value of "F" when W8 matches none of the P*
tokens. Your formula just returns #N/A.

How about

=INDEX({"F",1,1,1.5,2,2,2.5,3},
1+SUM((W8={"P1";"P1(i)";"P1(ii)";"P2";"P2(i)";"P2(ii)";"P­3"})*{1;2;3;4;5;6;7}))

?

 

[Bob Phillips]

Note that Harlan's sweet formula may be suffering NG blight. The - in P-3
wasn't recognised when I copied it in, so it returned "F", you may need to
re-type it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Bob Phillips wrote...

typo, meant

=VLOOKUP(W8,{"P1",1;"P1(i)",1;"P1(ii)",1.5;"P2",2;"P2(i)",2;"P2(ii)",2.5;
"P­3",3},2,FALSE)

....

OP seems to want a return value of "F" when W8 matches none of the P*
tokens. Your formula just returns #N/A.

How about

=INDEX({"F",1,1,1.5,2,2,2.5,3},
1+SUM((W8={"P1";"P1(i)";"P1(ii)";"P2";"P2(i)";"P2(ii)";"P­3"})*{1;2;3;4;5;6;
7}))

?