IF and AND functions help required

[Guest]

Hi all

I need to set up a formula that will use two separate cells to provide a
text result in a third cell. The problem is that the first cell has an upper
and a lower limit to the values that influence the text output.

Here's what I'm trying to do:

Cell A1 contains a HEX2DEC value of between 0 and F(15).
Cell B1 contains a text value of either "So", "Na", "Hv" or "CC"
Cell C1 needs to output a the text "NH" when the following conditions in A1
and B1 are met:

If A1<=4 or A1>=10 and B1="Hv" then C1="NH"

If A1 has the value of 5, 6, 7, 8 or 9 then C1 should return a blank cell.
If B1 has "So", "Na" or "CC" in it as text then C! should return a blank cell.

How do I put all of that into a formula in Excel?

 

[Ragdyer]

Try this:

=IF(OR(OR(A1={5,6,7,8,9}),OR(B1={"so","na","cc"})),"","NH")

 

[Ragdyer]

Forgot about "Hv".

Try this instead:

=IF(OR(OR(A1={5,6,7,8,9}),OR(B1={"so","na","cc"})),"",IF(B1="Hv","NH",""))

 

[Stan Brown]

Sun, 8 Apr 2007 10:14:02 -0700 from Gruffty the Hiver

If A1<=4 or A1>=10 and B1="Hv" then C1="NH"

If A1 has the value of 5, 6, 7, 8 or 9 then C1 should return a blank cell.
If B1 has "So", "Na" or "CC" in it as text then C! should return a blank cell.

How do I put all of that into a formula in Excel?

One approach is a nested IF:
=IF( B1<>"Hv", "", IF( AND(A1>=5,A1<=9), "", "NH")

Another approach would combine AND and OR inside IF:
=IF( AND( OR(A1<=4,A1>=10),B1="Hv" ), "NH", "")

 

[Guest]

many thanks, everyone - you've been a great help.