Microsoft Excel Logical Test Functions

[rocket01]

Not really sure if this is the correct place to post this, but I was wondering if anyone had any idea about these as I am putting together a scoring sheet for my darts team and I am struggling with a formula.

 

Basically I want to record the names of a person who finishes a game in 17 darts or less AND is the winner of that particular game, for instance player A throws first and throws 15 darts, however player 2, throwing second wins in 14 darts.  I don't want to record player A's leg was a losing leg so his name shouldn't be recorded and so on.

 

I have created a formula for recording the scores each player has made that is above 99 and I though that it would be easy enough to alter this data to show the correct data however this has not been the case.

 

Formula for recording scores above 99:

 

=IF(COUNTIF(G8:U8,">99")=0,"-",COUNTIF(G8:U8,">99"))

 

I've probably confused you all now but if anyone has any idea of how this could be achieved I would be greatful for the assistance.

 

Many Thanks

[DavidAM]

This will probably require an array formula (sometime called "control-enter" formulas).  If you can post the layout of your data (column headings and a couple of rows), I might be able to help.

[rocket01]

Here is the cell layout, this is just a single one but there are 9 on each side, so 18 in total.

 

 

The significant column in "T" and the first cell is "T8" and the Final Cell "T67" 

 

The cell that contains the information whether or not the player has won that particular game is "U"  If there is a numeric value within the cells in this column, that shows that the player has won that game.

 

Thanks 

 

 

 

[DavidAM]

OK.  This may be a little more difficult than I expected.  I have a couple more questions.

 

You want to record the names ... - where and in what format?  Are you looking for a list (i.e. Tom, Dick, Harry)?  Or do you have a summary sheet that lists the players and you're looking to make a mark or count in the player's row for "quick games" (I'm not familiar with darts terminology, what do you call these games?)?

 

Are you looking for a count of "quick legs" in a game or just any game where any leg is "quick"

 

the basic formua (per leg) would be

=if(and(T8<17,isnumber(U8)),"QuickWin","-")

 

But I'm a little confused by the forumla in your previous post as compared to your spreadsheet:

=IF(COUNTIF(G8:U8,">99")=0,"-",COUNTIF(G8:U8,">99"))

G8-R8 look like scores. But T8 is the number of darts used so I don't see why it is in the forumla (although, I guess they will not be using 99 darts so it doesn't matter).  And U8 is the won or lost column (according to your last post) so why is it in that forumla?

[rocket01]

basically that formula was for another part of the document that i attempted to modify.  I used that as an example maybe i should have made it more clear, but the basic principle is to list the names of the players who achieve outstanding performances i.e.  Matthew Williams 17, AN Other 15 etc etc.

 

The terminology for each game is "leg" and there are maximum of 5 "legs" per match, there are 9 matches in total.  Any person winning with a total of dats thrown lower than 18 is considered worthy of recording and these stats go onto the website.  The same goes for scores of "180" and also any scores used to finish the game that are >99.  The forumla that i posted above is for recording the scores of >99 in the statistics sheet, however it does not record the name of the player.  Myabe I should have provided more detail when asking the question.

 

In the Excel Workbook there are several individual pages, basically 9 match pages which transfer recorded data onto a "Summary" page, this is where I would like thsi data to appear

 

If this is not possible then I will have to go through them and record them manually, it was just seeing if i could make theo whole scoresheet completely automatic.  Would it be easier if i sent you a copy of the excel book and highlighted the data that would need to be used?  This would possibly make it a little more clearer because it really is difficult to explain the system without a full visual.