Nesting an If factor and using the range name

S

Silly Patty

I am taking a course for advanced excel and I need help with a
question>
I am working on a spread sheet for real estate. I am supposed to set
up a formula using NESector and CornerLot classifying them as shown
below.
1, 2 or 3.
Can any one help?????
NESector CornerLot Classification
Yes Yes 1
Yes No 2
No No 3
 
B

Bernard Liengme

Assuming the NESector is in A1 and the first row of "Yes"s is A2 and B2;
The formula needed in C1 is =IF(A2="Yes", IF(B2="Yes", 1, 2), 3)
But what is needed with NES = No and Corner = Yes ?
=IF(A2="Yes", IF(B2="Yes", 1, 2), IF(B2="No", 3, "What to do?")
 
T

Trevor Shuttleworth

One way:

=IF(AND(A2="yes",B2="yes"),1,IF(AND(A2="yes",B2="no"),2,IF(AND(A2="no",B2="no"),3,"error")))

or

=IF(COUNTIF(A2:B2,"yes")=2,1,IF(COUNTIF(A2:B2,"yes")=1,2,3))

Depends on what you want to do if NESector could be "No" and CornerLot could
be "Yes". And what do you want to do if the cell values are anything other
than "Yes" and "No" ?

Regards

Trevor
 
Top