IF and AND functions help required

  • Thread starter Gruffty the Hiver
  • Start date
G

Gruffty the Hiver

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?
 
R

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",""))
 
S

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", "")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top