Formula error!!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

redforest - 14 Nov 2006 15:34 GMT

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="P3",3,"F")))))))

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

text values.

Can anyone please help

R

Reply to this Message

Bob Phillips - 14 Nov 2006 15:54 GMT

=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

Reply to this Message

Bob Phillips - 14 Nov 2006 15:56 GMT

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

Reply to this Message

Harlan Grove - 14 Nov 2006 19:01 GMT

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;
>"P3",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)";"P3"})*{1;2;3;4;5;6;7}))

?

Reply to this Message

Bob Phillips - 14 Nov 2006 21:01 GMT

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;
>"P3",3},2,FALSE)

Reply to this Message