| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique draws "Football"
I have problem that is driving me nuts, I confess I do not have the
skills to solve. I want to find the how many teams Aston Villa has done a double draw over the season. =IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$D3)*($BE$3:$BE$40=$BG$3:$BG$40 )*((BP3="d")*(BQ3="d")),1))),$BF3,"") With CRTL+ALT+ENTER. I get the correct answers in this case Chelsea. Then I get Fulani and Middlebrow in has only played Aston Villa once. In row 2 column BD:BS are the header columns The data is in BD3:BD40 HS = Home Score, AS = Away Score Hr =Home Result (I.e W, D, or L) Column BP AR =Away Result (I.e wd, d, or dl) Column BQ The format has shifted. the data is the same with until the solid line below as the games have not been played as yet. BD BE BF BG Team HS Team AS P W D L pp wd d dl Hr AR Hg Aged Arsenal 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 2 Reading 1 1 1 0 0 1 0 0 1 W l 1 -1 Aston Villa 2 Newcastle 0 1 1 0 0 1 0 0 1 W l 2 -2 West Ham 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Watford 0 Aston Villa 0 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 2 Charlton 0 1 1 0 0 1 0 0 1 W l 2 -2 Chelsea 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Tottenham 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Fulham 1 1 0 1 0 1 0 1 0 D d 0 0 Liverpool 3 Aston Villa 1 1 1 0 0 1 0 0 1 W l 2 -2 Aston Villa 2 Blackburn 0 1 1 0 0 1 0 0 1 W l 2 -2 Everton 0 Aston Villa 1 1 0 0 1 1 1 0 0 L w -1 1 Wigan 0 Aston Villa 0 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Middlesbrough 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Man City 3 1 0 0 1 1 1 0 0 L w -2 2 Portsmouth 2 Aston Villa 2 1 0 1 0 1 0 1 0 D d 0 0 Sheff Utd 2 Aston Villa 2 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 0 Bolton 1 1 0 0 1 1 1 0 0 L w -1 1 Aston Villa 0 Man Utd 3 1 0 0 1 1 1 0 0 L w -3 3 Tottenham 2 Aston Villa 1 1 1 0 0 1 0 0 1 W l 1 -1 Charlton 2 Aston Villa 1 1 1 0 0 1 0 0 1 W l 1 -1 Aston Villa 0 Chelsea 0 1 0 1 0 1 0 1 0 D d 0 0 Man Utd 3 Aston Villa 1 1 1 0 0 1 0 0 1 W l 2 -2 Aston Villa 2 Watford 0 1 1 0 0 1 0 0 1 W l 2 -2 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Newcastle 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 West Ham 0 0 0 0 0 0 0 Reading 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Arsenal 0 0 0 0 0 0 0 Fulham 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Liverpool 0 0 0 0 0 0 0 Aston Villa 0 Everton 0 0 0 0 0 0 0 Blackburn 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Wigan 0 0 0 0 0 0 0 Middlesbrough 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Portsmouth 0 0 0 0 0 0 0 Man City 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Sheff Utd 0 0 0 0 0 0 0 Bolton 0 Aston Villa 0 0 0 0 0 0 0 Thanks in advance |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique draws "Football"
I assume that AE2 holds the team being checked, say Aston Villa, but what is
in D2:D40? Maybe try this alternative =IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$AE$2)* ($BE$3:$BE$40=$BG$3:$BG$40),1))),$BF3,"") -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Michael" wrote in message ... I have problem that is driving me nuts, I confess I do not have the skills to solve. I want to find the how many teams Aston Villa has done a double draw over the season. =IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$D3)*($ BE$3:$BE$40=$BG$3:$BG$40)*((BP3="d")*(BQ3="d")),1) )),$BF3,"") With CRTL+ALT+ENTER. I get the correct answers in this case Chelsea. Then I get Fulani and Middlebrow in has only played Aston Villa once. In row 2 column BD:BS are the header columns The data is in BD3:BD40 HS = Home Score, AS = Away Score Hr =Home Result (I.e W, D, or L) Column BP AR =Away Result (I.e wd, d, or dl) Column BQ The format has shifted. the data is the same with until the solid line below as the games have not been played as yet. BD BE BF BG Team HS Team AS P W D L pp wd d dl Hr AR Hg Aged Arsenal 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 2 Reading 1 1 1 0 0 1 0 0 1 W l 1 -1 Aston Villa 2 Newcastle 0 1 1 0 0 1 0 0 1 W l 2 -2 West Ham 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Watford 0 Aston Villa 0 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 2 Charlton 0 1 1 0 0 1 0 0 1 W l 2 -2 Chelsea 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Tottenham 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Fulham 1 1 0 1 0 1 0 1 0 D d 0 0 Liverpool 3 Aston Villa 1 1 1 0 0 1 0 0 1 W l 2 -2 Aston Villa 2 Blackburn 0 1 1 0 0 1 0 0 1 W l 2 -2 Everton 0 Aston Villa 1 1 0 0 1 1 1 0 0 L w -1 1 Wigan 0 Aston Villa 0 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Middlesbrough 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Man City 3 1 0 0 1 1 1 0 0 L w -2 2 Portsmouth 2 Aston Villa 2 1 0 1 0 1 0 1 0 D d 0 0 Sheff Utd 2 Aston Villa 2 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 0 Bolton 1 1 0 0 1 1 1 0 0 L w -1 1 Aston Villa 0 Man Utd 3 1 0 0 1 1 1 0 0 L w -3 3 Tottenham 2 Aston Villa 1 1 1 0 0 1 0 0 1 W l 1 -1 Charlton 2 Aston Villa 1 1 1 0 0 1 0 0 1 W l 1 -1 Aston Villa 0 Chelsea 0 1 0 1 0 1 0 1 0 D d 0 0 Man Utd 3 Aston Villa 1 1 1 0 0 1 0 0 1 W l 2 -2 Aston Villa 2 Watford 0 1 1 0 0 1 0 0 1 W l 2 -2 -------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------- Newcastle 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 West Ham 0 0 0 0 0 0 0 Reading 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Arsenal 0 0 0 0 0 0 0 Fulham 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Liverpool 0 0 0 0 0 0 0 Aston Villa 0 Everton 0 0 0 0 0 0 0 Blackburn 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Wigan 0 0 0 0 0 0 0 Middlesbrough 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Portsmouth 0 0 0 0 0 0 0 Man City 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Sheff Utd 0 0 0 0 0 0 0 Bolton 0 Aston Villa 0 0 0 0 0 0 0 Thanks in advance |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique draws "Football"
Just seen the point of another test, so a slight adjustment
=IF(AND($BD3=$AZ$1,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$AZ$1)* ($BE$3:$BE$40=$BG$3:$BG$40)*($BQ$3:$BQ$40="d"),1)) ),$BF3,"") -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Michael" wrote in message ... I have problem that is driving me nuts, I confess I do not have the skills to solve. I want to find the how many teams Aston Villa has done a double draw over the season. =IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$D3)*($ BE$3:$BE$40=$BG$3:$BG$40)*((BP3="d")*(BQ3="d")),1) )),$BF3,"") With CRTL+ALT+ENTER. I get the correct answers in this case Chelsea. Then I get Fulani and Middlebrow in has only played Aston Villa once. In row 2 column BD:BS are the header columns The data is in BD3:BD40 HS = Home Score, AS = Away Score Hr =Home Result (I.e W, D, or L) Column BP AR =Away Result (I.e wd, d, or dl) Column BQ The format has shifted. the data is the same with until the solid line below as the games have not been played as yet. BD BE BF BG Team HS Team AS P W D L pp wd d dl Hr AR Hg Aged Arsenal 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 2 Reading 1 1 1 0 0 1 0 0 1 W l 1 -1 Aston Villa 2 Newcastle 0 1 1 0 0 1 0 0 1 W l 2 -2 West Ham 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Watford 0 Aston Villa 0 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 2 Charlton 0 1 1 0 0 1 0 0 1 W l 2 -2 Chelsea 1 Aston Villa 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Tottenham 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Fulham 1 1 0 1 0 1 0 1 0 D d 0 0 Liverpool 3 Aston Villa 1 1 1 0 0 1 0 0 1 W l 2 -2 Aston Villa 2 Blackburn 0 1 1 0 0 1 0 0 1 W l 2 -2 Everton 0 Aston Villa 1 1 0 0 1 1 1 0 0 L w -1 1 Wigan 0 Aston Villa 0 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Middlesbrough 1 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 1 Man City 3 1 0 0 1 1 1 0 0 L w -2 2 Portsmouth 2 Aston Villa 2 1 0 1 0 1 0 1 0 D d 0 0 Sheff Utd 2 Aston Villa 2 1 0 1 0 1 0 1 0 D d 0 0 Aston Villa 0 Bolton 1 1 0 0 1 1 1 0 0 L w -1 1 Aston Villa 0 Man Utd 3 1 0 0 1 1 1 0 0 L w -3 3 Tottenham 2 Aston Villa 1 1 1 0 0 1 0 0 1 W l 1 -1 Charlton 2 Aston Villa 1 1 1 0 0 1 0 0 1 W l 1 -1 Aston Villa 0 Chelsea 0 1 0 1 0 1 0 1 0 D d 0 0 Man Utd 3 Aston Villa 1 1 1 0 0 1 0 0 1 W l 2 -2 Aston Villa 2 Watford 0 1 1 0 0 1 0 0 1 W l 2 -2 -------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------- Newcastle 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 West Ham 0 0 0 0 0 0 0 Reading 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Arsenal 0 0 0 0 0 0 0 Fulham 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Liverpool 0 0 0 0 0 0 0 Aston Villa 0 Everton 0 0 0 0 0 0 0 Blackburn 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Wigan 0 0 0 0 0 0 0 Middlesbrough 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Portsmouth 0 0 0 0 0 0 0 Man City 0 Aston Villa 0 0 0 0 0 0 0 Aston Villa 0 Sheff Utd 0 0 0 0 0 0 0 Bolton 0 Aston Villa 0 0 0 0 0 0 0 Thanks in advance |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique draws "Football"
On Mon, 29 Jan 2007 10:50:15 -0000, "Bob Phillips"
wrote: I assume that AE2 holds the team being checked, say Aston Villa, but what is in D2:D40? Maybe try this alternative =IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40 =BF3)*($BF$3:$BF$40=$AE$2)* ($BE$3:$BE$40=$BG$3:$BG$40),1))),$BF3,"") Bob, I must apologise for the formatting as it looks like a right dog's dinner. In D3:D40 are the home team this is duplicated to BD3:BD40. E3:E40 duplicate to BE3:BE40 Home Score. As the MIN function does not like blanks cells I used the formula =IF(ISNUMBER(E3),E3,0). The above formula produced Middlesbrough Portsmouth Sheff Utd West Ham Chelsea Arsenal Fulham Wigan Chelsea is the only team that Aston Villa drawn home and away. The others have play Aston Villa only once and drew. The second formula: =IF(AND($BD3=$AZ$1,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$AZ$1)* ($BE$3:$BE$40=$BG$3:$BG$40)*($BQ$3:$BQ$40="d"),1)) ),$BF3,"") Produced the following. Portsmouth Sheff Utd West Ham Chelsea Arsenal Wigan However in this example is outputting the away teams that have played as of yet. Chelsea is the only team that Aston Villa drawn home and away. The others have play Aston Villa only once and drew. I hope that this makes sense. Thanks Michael |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique draws "Football"
It only showed Chelsea for me.
-- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Michael" wrote in message ... On Mon, 29 Jan 2007 10:50:15 -0000, "Bob Phillips" wrote: I assume that AE2 holds the team being checked, say Aston Villa, but what is in D2:D40? Maybe try this alternative =IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40 =BF3)*($BF$3:$BF$40=$AE$2) * ($BE$3:$BE$40=$BG$3:$BG$40),1))),$BF3,"") Bob, I must apologise for the formatting as it looks like a right dog's dinner. In D3:D40 are the home team this is duplicated to BD3:BD40. E3:E40 duplicate to BE3:BE40 Home Score. As the MIN function does not like blanks cells I used the formula =IF(ISNUMBER(E3),E3,0). The above formula produced Middlesbrough Portsmouth Sheff Utd West Ham Chelsea Arsenal Fulham Wigan Chelsea is the only team that Aston Villa drawn home and away. The others have play Aston Villa only once and drew. The second formula: =IF(AND($BD3=$AZ$1,$BE3=$BG3,MIN(IF(($BD$3:$BD$40= BF3)*($BF$3:$BF$40=$AZ$1)* ($BE$3:$BE$40=$BG$3:$BG$40)*($BQ$3:$BQ$40="d"),1)) ),$BF3,"") Produced the following. Portsmouth Sheff Utd West Ham Chelsea Arsenal Wigan However in this example is outputting the away teams that have played as of yet. Chelsea is the only team that Aston Villa drawn home and away. The others have play Aston Villa only once and drew. I hope that this makes sense. Thanks Michael |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique draws "Football"
On Tue, 30 Jan 2007 00:01:16 -0000, "Bob Phillips"
wrote: It only showed Chelsea for me. Bob, From your formula AZ1 = Aston Villa. out of curiosity what results did you get form the first formula? This is driving me nuts. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique draws "Football"
On Tue, 30 Jan 2007 01:40:02 +0000, Michael
wrote: On Tue, 30 Jan 2007 00:01:16 -0000, "Bob Phillips" wrote: It only showed Chelsea for me. Bob, From your formula AZ1 = Aston Villa. out of curiosity what results did you get form the first formula? This is driving me nuts. Bob thanks I got it working by adding Bh3=1 bh is the column for played game played. Thanks as this was so logical I missed the logic in it. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Setting up a validation of data listbox to provide the unique items within a range | Excel Worksheet Functions | |||
| existing unique value formula help (index(match(countif))) | Excel Worksheet Functions | |||
| how do i find unique avg buy prices in multiple group of buys/sell | Excel Worksheet Functions | |||
| code not unique find latest date | Excel Discussion (Misc queries) | |||
| Count Unique Values | Excel Worksheet Functions | |||
Linear Mode
