Complicated (for me) If statement

Dan190984

New Member
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 > .

Thanks the formula should have read

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

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

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

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?

Maybe

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

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?

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!

Replies
1
Views
211
Replies
8
Views
277
Replies
1
Views
248
Replies
6
Views
255
Replies
1
Views
231

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.

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

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