nested IFs and ANDs

[Peter3580]

									A K 10 9 8 7 6 2 8 7 1 J 1 K Q 7 3 <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>

 

[Peter3580]

Thanks. I will give this a try.

Peter

This does not solve my problem. I think IFs and AND are the way forward but i just dont know enough about Excel. Prehaps a bridge player who understands the Losing Trick Count would be best placed to tell me how to go about it.

Cheers

Peter

 

[mikerickson]

How does it fail to do what you want?
Does it not return the number of cards less than a Q in the first three cells of a row?

 

[Peter3580]

Losing Trick Count only counts Aces Kings and Queens. My example is supposed to show a hand that contains 8 Spades, 1 Heart, 1 Club and 3 Diamonds. I look at this hand and can see instantly that it has a losing trick count of 4. In the spades suit three tricks only are counted, it contains the Ace and King so for this suit the LTC is 1 (no Queen) The heart suit only has 1 card, it is not the A K or Q so there is LTC of 1 only. The club suit the same. The diamond suit contains 3 cards, K Q 7 therefore there is 1 LTC for this suit. A total of LTC of 4 for this hand. Hope this helps

Peter

 

[mikerickson]

So a losing trick count is
Look at your 3 high cards for each suit.
The LTC is the number of AKQ that are not in that group


Is LTC the total number of A,K or Q in the other hands. Getting a total for the hand is simply a matter of
=12-(SUMIF(A1:C4,"A")+SUMIF(A1:C4,"K")+SUMIF(A1:C4,"Q"))

 

[Peter3580]

this did not appear to do anything. I just copied and pasted into a cell, do I need to press any specific keys for it to calculate?.
I have written a word doc and will try and paste it here with some examples of Losing Trick Count

6 example hands showing Losing Trick Count

SPADES HEARTS DIAMONDS CLUBS Losers
Hand a K75 A7 9873 7532 9
Hand b A754 6 Q97653 Q4 7
Hand c K752 A K973 8742 7
Hand d K752 K Q973 8742 8
Hand e K872 A Q973 J742 7
Hand f 872 K8 Q764 J742 9

a Spades - 2 losers. Hearts - 1. Diamonds - 3. Clubs - 3. TOTAL - 9 losers.
b Spades - 2 losers. Hearts - 1. Diamonds - 2. Clubs - 2. TOTAL - 7 losers.
c Spades - 2 losers. Hearts - 0. Diamonds - 2. Clubs - 3. TOTAL - 7 losers.
d Spades - 2 losers. Hearts - 1. Diamonds - 2. Clubs - 3. TOTAL - 8 losers.
e Spades - 2 losers. Hearts - 0. Diamonds - 2. Clubs - 3. TOTAL - 7 losers.
f Spades - 3 losers. Hearts - 1. Diamonds - 2. Clubs - 3. TOTAL - 9 losers.

Counting Losers

Only the first three cards in any suit can be losers
Only the Ace, King, and Queen are winners
‘Droppable Honours’ count as losers (i.e. singleton King, or doubleton Queen)

Hope this helps

Peter

 

[Peter3580]

ah well. never mind I will try else where