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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
So in your top row A is in column 1 and K is in column 2
Should the count be 1 or 2 ?
 

Peter3580

New Member
Joined
Feb 17, 2018
Messages
17
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:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
Im still a little confused. You're better off producing what you expect the output to look like.
 

Peter3580

New Member
Joined
Feb 17, 2018
Messages
17

ADVERTISEMENT

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

Peter3580

New Member
Joined
Feb 17, 2018
Messages
17
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>
 

Peter3580

New Member
Joined
Feb 17, 2018
Messages
17

ADVERTISEMENT

<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
 

Peter3580

New Member
Joined
Feb 17, 2018
Messages
17
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:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,080
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:

Forum statistics

Threads
1,148,290
Messages
5,745,879
Members
423,983
Latest member
blackworx

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
Top