Complicated (for me) If statement

Dan190984

New Member
Joined
Mar 18, 2011
Messages
26
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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 > .
 
Upvote 0
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")))))
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Maybe

=IF(R4="","",INDEX({"W","D","L"},SIGN(IF(Q6="H",S4-R4,R4-S4))+2))
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,218,808
Messages
6,144,603
Members
450,557
Latest member
Lahoya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top