Could Use Some Help With A Formula

[Scaparoo]

Hi folks, if anyone could help me out with the formula I am trying to
write I'd appreciate it. Basically I have two columns:

If the entry for column A is "y" and column B is blank I'd like a
value of "1" in column C. If the entry for column A is "y" and column
B is "*" then I'd like a value of "3". Lastly, if column A is blank
and column B has a "*" I'd like column C to remain blank:

y and blank = 1
y and * =3
blank and * = blank(or even 0)

I've written the following formula which works except for the last
combination where it still returns a 3:

=IF(B5="*","3",IF(A5="y","1",""))

Does anyone have any suggestions? Thanks!

 

[joeu2004]

If the entry for column A is "y" and column B is blank I'd like a
value of "1" in column C. If the entry for column A is "y" and column
B is "*" then I'd like a value of "3". Lastly, if column A is blank
and column B has a "*" I'd like column C to remain blank:

What about the many other conditions you did not cover? For example, when
A5 is "y" and B5 is neither blank nor "*". I assume you want the result to
appear to be blank.

=IF(AND(A5="y",B5=""),"1",IF(AND(A5="y",B5="*"),"3",""))

But do you really want the result to be the __text__ "1" and "3"? Usually,
we want the __numbers__ 1 and 3. In that case:


=IF(AND(A5="y",B5=""),1,IF(AND(A5="y",B5="*"),3,""))

Note that in all cases when the result is not 1 or 3, the result is the null
string (""). The cell __appears__ to be blank. But it really isn't; and it
can't be.

 

[Scaparoo]

What about the many other conditions you did not cover? For example, when
A5 is "y" and B5 is neither blank nor "*". I assume you want the result to
appear to be blank.

Correct, all other values are to result in a blank.

=IF(AND(A5="y",B5=""),"1",IF(AND(A5="y",B5="*"),"3",""))

But do you really want the result to be the __text__ "1" and "3"? Usually,
we want the __numbers__ 1 and 3. In that case:


=IF(AND(A5="y",B5=""),1,IF(AND(A5="y",B5="*"),3,""))

Note that in all cases when the result is not 1 or 3, the result is the null
string (""). The cell __appears__ to be blank. But it really isn't; and it
can't be.

Yes I wanted the numbers and not text values. Obviously I don't
normally write formulas

I plugged it in and it worked like a charm, thank you for your speedy
reply, I was getting a tad frustrated with the help file (and myself).