Complicated (for me) If statement

[Dan190984]

p q r s t
5 Opp Ven H A Form
6 Bournemouth H 1 2 L

=IF(R4="","",IF(R4>S4,"W",IF(R4=S4,"D",IF(R4<S4,"L"))))

Hi I have the above table in excel.
I need a formula that works out the same as the formula above when q6 is H however reverses this if q6 is A

 

[Gerald Higgins]

Hi, welcome to the board.

It looks like some of your formula is missing.
Maybe this is because it contains < and > characters.
This is a recognised bug on this board, where using those characters can mean some of your post disappears.
Try it again, inserting a space character after the < or > .

 

[Dan190984]

Thanks the formula should have read

=IF(R4="","",IF(R4 > S4,"W",IF(R4=S4,"D",IF(R4 < S4,"L"))))

 

[barry houdini]

Try like this

=IF(R4="","",IF(R4 > S4,IF(Q6="H","W","L"),IF(R4=S4,"D",IF(R4 < S4,IF(Q6="A","W","L")))))

 

[Dan190984]

Thanks for the reply.

That formula is not having the desired effect. If q6 is H all the W L or D appear correctly however if Q6 = A i just get L in T6 regardless of the contents of other cells

 

[barry houdini]

OK perhaps I'm misunderstanding this but if I put A in Q6 and 2 in R4 and 3 in S4 I get W with that formula. If I change Q6 to H then that changes to L, isn't that what you need? I assume R4 and S4 are numbers?

 

[jasonb75]

Maybe

=IF(R4="","",INDEX({"W","D","L"},SIGN(IF(Q6="H",S4-R4,R4-S4))+2))

 

[jasonb75]

Maybe

=IF(R4="","",INDEX({"W","D","L"},SIGN(IF(Q6="H",S4-R4,R4-S4))+2))

I didn't look at barry's suggestion when posting this, but both will give the same result, which is correct based on the information you have given us.

Maybe Q6 should be Q4 or R4 and S4 should be R6 and S6 instead?

 

[Dan190984]

OK perhaps I'm misunderstanding this but if I put A in Q6 and 2 in R4 and 3 in S4 I get W with that formula. If I change Q6 to H then that changes to L, isn't that what you need? I assume R4 and S4 are numbers?

Thanks Barry

It was late and evidently I was tired.

That works just fine. Thanks Very much!