FORMULA HELP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

B.B - 13 Feb 2007 00:41 GMT

I am cant get this to work, I need a formula so cell A1 will show 1 if cell
A2 is A, 2 if B and 3 if C
thanks to all

Reply to this Message

Dave Peterson - 13 Feb 2007 00:46 GMT

Maybe...

=code(upper(a1))-64

> I am cant get this to work, I need a formula so cell A1 will show 1 if cell
> A2 is A, 2 if B and 3 if C
> thanks to all

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 13 Feb 2007 00:50 GMT

Change that A1 to A2
=code(upper(a2))-64

> Maybe...
>
[quoted text clipped - 7 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

OM - 13 Feb 2007 09:47 GMT

Dave,

What a clever way of doing this ! I was thinking of nested If's but this is
much simpler.

Rob Lepper

> Change that A1 to A2
> =code(upper(a2))-64
[quoted text clipped - 11 lines]
>>
>> Dave Peterson

Reply to this Message

Vince Parsons - 13 Feb 2007 02:44 GMT

How about using some "IF" statements as follows:
=IF(A2="A",1,IF(A2="B",2,IF(A2="C",3,"")))

If A2 does not equal A, B or C, then the cell will be blank.

Dave's formula is better...

>I am cant get this to work, I need a formula so cell A1 will show 1 if
>cell A2 is A, 2 if B and 3 if C
> thanks to all

Reply to this Message

Joerg - 13 Feb 2007 04:03 GMT

Something like
=CHOOSE(A2,"A","B","C")

Joerg

> I am cant get this to work, I need a formula so cell A1 will show 1 if cell
> A2 is A, 2 if B and 3 if C
> thanks to all

Reply to this Message

Joerg - 13 Feb 2007 04:57 GMT

Sorry, this was not what you asked for. I'll give it another try:
=MATCH(A2,{"A","B","C",""})
This will result in 1,2 or 3 for one of the characters in the array, and 4
for all other characters. It's not case sensitive. It results in an error if
A2 contains a numeric value.

Joerg

> Something like
> =CHOOSE(A2,"A","B","C")
[quoted text clipped - 5 lines]
> > A2 is A, 2 if B and 3 if C
> > thanks to all

Reply to this Message