If and or formula

[sd7000]

Hi all,

Need some help with if-and-or statement if possible. The formula would go in cell A10.
If D5="x" and D4=7, answer would be "H8", if D4 is blank, answer would be "x".
If D5=2 or 3, and D4=7, answer would be "H8", if D4 is blank, answer would be "v8".
If D5=1 or blank and D4=7 or blank, answer would be " ".

Thanks in advance for any help you might be able to offer.
Steve

[mohitspamz]

Hey!
Try this...

=IF(AND(D4=7,D5="X"),"H8",IF(AND(D4="",D5="X"),"X",IF(AND(D4=7,OR(D5=2,D5=3)),"H8",IF(AND(D4="",OR(D5=2,D5=3)),"V8",IF(AND(D4=7,OR(D5=1,D5="")),"","VALUE NOT FOUND")))))

Lengthy formula... dont know wot u want if none of the conditions match, so i added "value not found" at the end, u cud change that to wotever u like...

Hope it works out for u..

Regards
Mohit

[mohitspamz]

Hey!
Just for fun, i tried this other way and it worked too... all the blanks would have to infact be spaces " " for the formula to work though.... and u'd need to use and hide 3 reference columns...

Have a look! :P

Regards
Mohit

[Richard Buttrey]

Hi Steve,

There are two D4=blank with different requirements. Which takes precedence?
Rgds

[teylyn]

Hi,

this formula

=IF(OR(ISBLANK(D5),D5=1),"",IF(D5="x",IF(D4=7,"H8","x"),IF(D4=7,"H8","V8")))

will work, but you'd need to ensure that only valid entries can be made in D4 and D5

This formula

=INDEX({"","","H8","H8","H8";"","","V8","V8","x"},MATCH(D4,{7,0},0),MATCH(D5,{0,1,2,3,"x"},0))

will work and produce #N/A if D4 is something other than blank or 7, or D5 is something other than blank, 1, 2, 3 or x. It's not quite perfect, though, since it will treat blanks the same as zeros in D4 and D5, so, again you may want to make sure that data input is validated.

hth

[sd7000]

Thanks for all the help, I did a little adjusting, formulas work great, thanks again.