nested IFs and ANDs

Peter3580

New Member
Joined
Feb 17, 2018
Messages
17
AK10987628
71
J1
KQ73
<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <tbody> </tbody>








I haveseven columns of data.
An eighth column shows the total for the row.
I wantto count when A (Ace), K (King) or Q (Queen) occurs in columns 1, 2 and 3 butonly when the eighth column is greater than 3.
A canonly occur in column 1
K canoccur in column 1 and 2
Q canoccur in column 1 2 and 3

Can dothe IF parts but hopless when trying to put the AND part in
Thanks

Peter
<colgroup><col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <tbody> </tbody>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
So in your top row A is in column 1 and K is in column 2
Should the count be 1 or 2 ?
 
Upvote 0
I want the answer, 1, to be in 3 columns if there is A, K and Q in the column and in 4 rows. So that i have a 3x4 matrix filled with 1s. Does this make any sense.

Thanks

Peter
 
Last edited:
Upvote 0
Im still a little confused. You're better off producing what you expect the output to look like.
 
Upvote 0
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"> </o:lock></v:path></v:stroke></v:shapetype>
<tbody> </tbody>
AK10987628LTC=4110
71000
J1000
KQ73110
KQ4 3LTC=6 110
J103 3 000
AKQ4 4 111
Q52 3 100
K10975 5LTC=7 100
Q1032 4 100
4 1 000
Q52 3 100The data depicts a duplicate bridge hand.
13 cards in each hand. I am trying to measure the Losing Trick Count.
The LTC depends on the number of cards in each suit and the amount
of Aces, Kings and Queens held.

Peter
sorry I don't know how to add text. hope this helps
<colgroup><col width="80" style="width: 60pt;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <col width="15" style="width: 11pt; mso-width-source: userset; mso-width-alt: 433;"> <col width="80" style="width: 60pt;"> <col width="13" style="width: 10pt; mso-width-source: userset; mso-width-alt: 374;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="3"> <col width="80" style="width: 60pt;" span="7"> <tbody> </tbody>
 
Upvote 0
AK10987628LTC=4110
71000
J1000
KQ73110
KQ4 3LTC=6 110
J103 3 000
AKQ4 4 111
Q52 3 100
K10975 5LTC=7 100
Q1032 4 100
4 1 000
Q52 3 100
<colgroup><col width="80" style="width: 60pt;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="10"> <col width="15" style="width: 11pt; mso-width-source: userset; mso-width-alt: 433;"> <col width="80" style="width: 60pt;"> <col width="13" style="width: 10pt; mso-width-source: userset; mso-width-alt: 374;"> <col width="32" style="width: 24pt; mso-width-source: userset; mso-width-alt: 945;" span="3"> <col width="80" style="width: 60pt;"> <tbody> </tbody>
 
Upvote 0
<o:p> </o:p>
A<o:p></o:p>
B<o:p></o:p>
C<o:p></o:p>
D<o:p></o:p>
E<o:p></o:p>
F<o:p></o:p>
G<o:p></o:p>
H<o:p></o:p>
I<o:p></o:p>
J<o:p></o:p>
K<o:p></o:p>
<o:p> </o:p>
LTC<o:p></o:p>
1<o:p></o:p>
SPADES<o:p></o:p>
A<o:p></o:p>
K<o:p></o:p>
10<o:p></o:p>
9<o:p></o:p>
8<o:p></o:p>
7<o:p></o:p>
6<o:p></o:p>
2<o:p></o:p>
8<o:p></o:p>
<o:p> </o:p>
1<o:p></o:p>
1<o:p></o:p>
0<o:p></o:p>
2<o:p></o:p>
HEARTS<o:p></o:p>
7<o:p></o:p>
1<o:p></o:p>
<o:p> </o:p>
0<o:p></o:p>
0<o:p></o:p>
0<o:p></o:p>
3<o:p></o:p>
CLUBS<o:p></o:p>
J<o:p></o:p>
1<o:p></o:p>
<o:p> </o:p>
0<o:p></o:p>
0<o:p></o:p>
0<o:p></o:p>
4<o:p></o:p>
DIAMOND<o:p></o:p>
K<o:p></o:p>
Q<o:p></o:p>
7<o:p></o:p>
3<o:p></o:p>
<o:p> </o:p>
0<o:p></o:p>
1<o:p></o:p>
1<o:p></o:p>
<tbody> </tbody>
<o:p> </o:p>
The above table represents a Duplicate Bridge hand of 13playing cards.
<o:p> </o:p>
I am trying to write a formula that will calculate theLosing Trick Count (LTC) of the hand.
<o:p> </o:p>
Only the first threecards in any suit can be losers - Only the Ace, King, and Queen are winners<o:p></o:p>
<o:p> </o:p>
In the SPADES suit are 8 cards – the Ace and King arewinners, so there is 1 ‘loser’
<o:p> </o:p>
In the HEARTS suit there is 1 card there it is not the Ace thereforeis a ‘loser’. Only 1 card is held in this suit so there can only be 1 ‘loser’.
<o:p> </o:p>
In the CLUBS suit there is also only 1 card, which is notthe Ace therefore is a ‘loser.
<o:p> </o:p>
In the DIAMONDS suit there are 3 cards – the King and Queenare winners but there is no Ace therefore there is 1 ‘loser’.
<o:p> </o:p>
The Losing Trick Count for this hand is 4.
<o:p> </o:p>
I want a formula to calculate the LTC along the lines of:
<o:p> </o:p>
If B1(throughto B4) = A, K or Q and K1(through to K4) is equal to, or more than 3 then enter 1 in the table
If B1(throughto B4) = K or Q and K8 is equal to, or more than 3 then enter 1 in the table
If B1(throughto B4) = Q and K8 is equal to, or more than 3 then enter 1 in the table
 
Upvote 0
nobody can help then, thought it might be a simple task for the experts, but, hey ho. I will try elsewhere. Anyway thanks for previous help

Regards

Peter
 
Last edited:
Upvote 0
I would use a different system of representing cards, using numbers 13=A 12=K, 11 = Q etc.

Then =SUMIF(A1:C1, "<11") would return the number of losers in row 1.


Or you could use this to count the losers in row 9.
=SUMPRODUCT(--(MATCH(A9:C9,{"A","K","Q","J",10,9,8,7,6,5,4,3,2},0)>3))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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