Comparing two values

[nicoll]

I'm struggling with a formula to look at the contents in two cells and
return a result depending on the two values. Can anyone give me any pointers
please?

My values are as follows:

Cell A1 Cell B1 Cell C1( required result)
Blank any Y
0 any Y
1 or 2 non blank Y
1 or 2 blank N
3 or 4 non blank N
3 or 4 blank Y
5 blank N
5 non blank Y


Thanks for any help.

 

[Max]

Assuming the value to return if FALSE is "" ("blank")
(value if FALSE was not specified)
then the formulas in sequential order
from row2 down in col C could look something like ..

=IF(ISBLANK(A2),"Y","")
=IF(A3=0,"Y","")
=IF(AND(OR(A4=1,A4=2),NOT(ISBLANK(B4))),"Y","")
=IF(AND(OR(A5=1,A5=2),ISBLANK(B5)),"N","")
=IF(AND(OR(A6=3,A6=4),NOT(ISBLANK(B6))),"N","")
=IF(AND(OR(A7=3,A7=4),ISBLANK(B7)),"Y","")
=IF(AND(A8=5,ISBLANK(B8)),"N","")
=IF(AND(A9=5,NOT(ISBLANK(B9))),"Y","")

 

[nicoll]

Thanks Max

My question should have been clearer. The list of cell values I gave are all
possible entries in cells A1, B1 and I want the result in C1. I could nest
all the formula you provided but I was hoping there is an easier way to do
this. As I intend to copy the formula to other columns.

Thanks

 

[Ron Rosenfeld]

I'm struggling with a formula to look at the contents in two cells and
return a result depending on the two values. Can anyone give me any pointers
please?

My values are as follows:

Cell A1 Cell B1 Cell C1( required result)
Blank any Y
0 any Y
1 or 2 non blank Y
1 or 2 blank N
3 or 4 non blank N
3 or 4 blank Y
5 blank N
5 non blank Y


Thanks for any help.

This seems to work. Put it in some cell and copy down 7 rows. If you need
this for more numbers, one way would be to expand the array constants or use
the relationship between the numbers to generate array constants.

=IF(OR(A1=0,AND(OR(A1={1,2,5}),NOT(ISBLANK(B1))),AND(OR(A1={3,4}),ISBLANK(B1))),"y","n")


--ron

 

[nicoll]

Ron

Thanks for your help. It does the job. Except when cell b1 contains is a
result of a formula such as = if(z1=7,"","no"), I'm describing the cell as
being blank but it looks as of the result "" is not treated as a blank by
ISBLANK. I tried ISNONTEXT which works with "" but not an empty cell.

Thanks.

 

[Peo Sjoblom]

Use B1="" instead of ISBLANK(B1) for formulas with "" strings

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Max]

My apologies for the mis-reading of your post, Nicoll

Was perhaps mesmerized by
the one-too-many "blanks" and "non-blanks" <g>

Glad to see you've got the answers from Ron and Peo