FedSpeaker
New Member
- Joined
- Feb 3, 2011
- Messages
- 5
I will appreciate if anyone can help me out with the following array problem.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
The problem uses data from two tables (tables 1 & 2 below) to fill the cells with question marks in table 3 at the bottom.
<o></o>
Understanding the table data:
Table 1 contains a sporting schedule with all teams in column A and each team’s opponents by week in the columns to the right. Table 2 contains the weekly score for each team listed in column A. So looking at the example data in Table 2 we can see that Team1 scored 10 in week 1. Also looking at Table 1 it can be seen that the team did so against its Wk 1 opponent (Team2).
<o></o>
The problem:
I would like to build equations in Table 3 that would count wins (W), losses (L), and ties (T). In other words the W equation in Table 3 must count (or sum) all the wins Team3 had against each opponent.
For example the W equation would have to first look in Table 1 for a particular team (let’s take Team3) and it should determine how many times (and when) it played against a particular opponent. Then the equation must retrieve the individual score (or scores) from Table 2, both for the team that is being analyzed (Team3) and the opponent in question. In the example below I chose to analyze Team3 so the first equation for the W column corresponding to the first opponent listed (Team 1) needs to search in Table 1 for Team 3, then find all the instances (weeks) in which Team3 played against Team1. Since the equation is to determine how many games Team3 won against Team1, then the equation must fetch the score for both Team3 and Team1 for each week they played against each other and proceed to make a comparison to determine whether Team3 had a higher score than Team1. In every instance that Team3 had a higher score than Team1 the W equation must add 1 to the count (or the sum). For every instance Team1 had a higher score the equation must return zero (0) and then continue checking the array to see if there are other matchups between the two teams.
I tried to find a solution for this (please see the two equations below) but I couldn’t get the logic behind the solution to yield the correct values. I have successfully used a similar approach to fetch opponents from an array for other data manipulation – like summing scores, etc., but this time around I came to a dead end. I think the problem has to do with the comparison of values but I am not sure why my solution is not working. Any tips as to what sort of approach I need to take will be of help and greatly appreciated.
<o></o>
P.S. I only need an equation that works for W in Table 3 since the equation for L should be similar in structure and the one for T should be even easier once the W and L are known. The W equation should return a number (zero or greater). For example Team3 beat Team4 both times they played (6-5 in Wk1 and 5-4 in Wk4). Therefore, the W cell in Table 3 for opponent Team4 should return a count of 2 because Team3 beat Team4 both times they played in the sample schedule contained in Table1.
With the tables below I indicate the columns and rows in an attempt to make it easier for you to identify data.
<o></o>
Anyone please help, thank you.
Table 1: Opponents
<o></o>
>>>>> Col A Col B Col C Col D Col E
Row5 Teams Wk1 Wk2 Wk3 Wk4
Row6 Team1 Team2 Team3 Team4 Team2
Row7 Team2 Team1 Team4 Team3 Team1
Row8 Team3 Team4 Team1 Team2 Team4
Row9 Team4 Team3 Team2 Team1 Team3
Table 2: Scores
>>>>> Col A Col B Col C Col D Col E
Row14 Teams Wk1 Wk2 Wk3 Wk4
Row15 Team1 10 4 5 3
Row16 Team2 2 5 0 8
Row17 Team3 6 6 11 5
Row18 Team4 5 5 7 4
<o></o>
Table 3: Record for Team 3 vs opponents…
>>>>> Col A Col B Col C Col D Col E <o></o>
Row23 Opp W L T
Row24 Team1 ? ? ?
Row25 Team2 ? ? ?
Row26 Team3 - - -
Row27 Team4 ? ? ?
<o></o>
My first approach returned all zeros. Did not work.
=SUM(IF(IF(($A$6:$A$9="Team3")*($B$6:$E$9=$A24),$B$15:$E$18)>IF(($A$6:$A$9=$A$24)*($B$6:$E$9="Team3"),$B$15:$E$18),IF(($A$15:$A$18="Team3")*ISNUMBER(B$15:E$18),1,0)))
Breaking down first approach…
IF(($A$6:$A$9="Team3")*($B$6:$E$9=$A24),$B$15:$E$18) >> This to find the score for Team3 against the opponent (seems to work fine)
IF(($A$6:$A$9=$A$24)*($B$6:$E$9="Team3"),$B$15:$E$18) >> This to find the score for the opponent against Team3 (resulting array seems to display as intended when using CTR+SHIFT+ENTER.
IF(($A$15:$A$18="Team3")*ISNUMBER(B$15:E$18) >> I added this condition to make sure the equation considered only cells with numbers because I have other equations in place feeding scores weekly to Table 2.
My main problem with this approach was that the equation seemed to return the wrong value when I added the first condition with the second one using the > sign. I used the greater than sign (>) because I need to compare the values for Team3 and the opponent.
<o></o>
My second approach did not work either because it seemed to only be reading part of the data. The equation did not return all zeros but still returned the wrong value.
=SUM(IF(IF(($A$6:$A$9="Team3")*($B$6:$E$9=$A24),$B$15:$E$18)>IF(($A$6:$A$9=$A$24)*($B$6:$E$9="Team3"),$B$15:$E$18),1,0))
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o>The problem uses data from two tables (tables 1 & 2 below) to fill the cells with question marks in table 3 at the bottom.
<o
></o>Understanding the table data:
Table 1 contains a sporting schedule with all teams in column A and each team’s opponents by week in the columns to the right. Table 2 contains the weekly score for each team listed in column A. So looking at the example data in Table 2 we can see that Team1 scored 10 in week 1. Also looking at Table 1 it can be seen that the team did so against its Wk 1 opponent (Team2).
<o
></o>The problem:
I would like to build equations in Table 3 that would count wins (W), losses (L), and ties (T). In other words the W equation in Table 3 must count (or sum) all the wins Team3 had against each opponent.
For example the W equation would have to first look in Table 1 for a particular team (let’s take Team3) and it should determine how many times (and when) it played against a particular opponent. Then the equation must retrieve the individual score (or scores) from Table 2, both for the team that is being analyzed (Team3) and the opponent in question. In the example below I chose to analyze Team3 so the first equation for the W column corresponding to the first opponent listed (Team 1) needs to search in Table 1 for Team 3, then find all the instances (weeks) in which Team3 played against Team1. Since the equation is to determine how many games Team3 won against Team1, then the equation must fetch the score for both Team3 and Team1 for each week they played against each other and proceed to make a comparison to determine whether Team3 had a higher score than Team1. In every instance that Team3 had a higher score than Team1 the W equation must add 1 to the count (or the sum). For every instance Team1 had a higher score the equation must return zero (0) and then continue checking the array to see if there are other matchups between the two teams.
I tried to find a solution for this (please see the two equations below) but I couldn’t get the logic behind the solution to yield the correct values. I have successfully used a similar approach to fetch opponents from an array for other data manipulation – like summing scores, etc., but this time around I came to a dead end. I think the problem has to do with the comparison of values but I am not sure why my solution is not working. Any tips as to what sort of approach I need to take will be of help and greatly appreciated.
<o
></o>P.S. I only need an equation that works for W in Table 3 since the equation for L should be similar in structure and the one for T should be even easier once the W and L are known. The W equation should return a number (zero or greater). For example Team3 beat Team4 both times they played (6-5 in Wk1 and 5-4 in Wk4). Therefore, the W cell in Table 3 for opponent Team4 should return a count of 2 because Team3 beat Team4 both times they played in the sample schedule contained in Table1.
With the tables below I indicate the columns and rows in an attempt to make it easier for you to identify data.
<o
></o>Anyone please help, thank you.
Table 1: Opponents
<o
></o>>>>>> Col A Col B Col C Col D Col E
Row5 Teams Wk1 Wk2 Wk3 Wk4
Row6 Team1 Team2 Team3 Team4 Team2
Row7 Team2 Team1 Team4 Team3 Team1
Row8 Team3 Team4 Team1 Team2 Team4
Row9 Team4 Team3 Team2 Team1 Team3
Table 2: Scores
>>>>> Col A Col B Col C Col D Col E
Row14 Teams Wk1 Wk2 Wk3 Wk4
Row15 Team1 10 4 5 3
Row16 Team2 2 5 0 8
Row17 Team3 6 6 11 5
Row18 Team4 5 5 7 4
<o
></o>Table 3: Record for Team 3 vs opponents…
>>>>> Col A Col B Col C Col D Col E <o
></o>Row23 Opp W L T
Row24 Team1 ? ? ?
Row25 Team2 ? ? ?
Row26 Team3 - - -
Row27 Team4 ? ? ?
<o
></o>My first approach returned all zeros. Did not work.
=SUM(IF(IF(($A$6:$A$9="Team3")*($B$6:$E$9=$A24),$B$15:$E$18)>IF(($A$6:$A$9=$A$24)*($B$6:$E$9="Team3"),$B$15:$E$18),IF(($A$15:$A$18="Team3")*ISNUMBER(B$15:E$18),1,0)))
Breaking down first approach…
IF(($A$6:$A$9="Team3")*($B$6:$E$9=$A24),$B$15:$E$18) >> This to find the score for Team3 against the opponent (seems to work fine)
IF(($A$6:$A$9=$A$24)*($B$6:$E$9="Team3"),$B$15:$E$18) >> This to find the score for the opponent against Team3 (resulting array seems to display as intended when using CTR+SHIFT+ENTER.
IF(($A$15:$A$18="Team3")*ISNUMBER(B$15:E$18) >> I added this condition to make sure the equation considered only cells with numbers because I have other equations in place feeding scores weekly to Table 2.
My main problem with this approach was that the equation seemed to return the wrong value when I added the first condition with the second one using the > sign. I used the greater than sign (>) because I need to compare the values for Team3 and the opponent.
<o
></o>My second approach did not work either because it seemed to only be reading part of the data. The equation did not return all zeros but still returned the wrong value.
=SUM(IF(IF(($A$6:$A$9="Team3")*($B$6:$E$9=$A24),$B$15:$E$18)>IF(($A$6:$A$9=$A$24)*($B$6:$E$9="Team3"),$B$15:$E$18),1,0))
