Any suggestion to organise this data more efficient ?

[mikewongbiz]

Hi All,

I use Excel to manage my team scores every week. However, I cannot work out an efficient structure to manage and maintain the data. Basically, each team gets 1 point for beating the other team and 2 points if the total scores are higher. My current layout is below, however, it just seems not right...can anyone suggest a better way to manage/present the data as more weeks accumulate ? Thanks! Mike<br><br>

<table border=0 cellpadding=0 cellspacing=0 >
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 width=77 style='height:15.0pt;width:58pt'>Week</td>
<td class=xl7722973 width=77 style='width:58pt'></td>
<td class=xl7822973 width=77 style='width:58pt'>Game 1</td>
<td class=xl7822973 width=77 style='width:58pt'>Game 2</td>
<td class=xl7822973 width=77 style='width:58pt'>Game 3</td>
<td class=xl7822973 width=77 style='width:58pt'>Game 4</td>
<td class=xl7822973 width=77 style='width:58pt'>Total</td>
<td class=xl7722973 width=89 style='width:67pt'></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7822973>Team</td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team A</td>
<td class=xl7722973>450</td>
<td class=xl7722973>430</td>
<td class=xl7722973>500</td>
<td class=xl7722973>400</td>
<td class=xl7722973>1780</td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team C</td>
<td class=xl7722973>550</td>
<td class=xl7722973>460</td>
<td class=xl7722973>470</td>
<td class=xl7722973>350</td>
<td class=xl7722973>1830</td>
<td class=xl1522973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl7822973>Total Points</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team A</td>
<td class=xl7722973>0</td>
<td class=xl7722973>0</td>
<td class=xl7722973>1</td>
<td class=xl7722973>1</td>
<td class=xl7722973>0</td>
<td class=xl7722973>2</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'></td>
<td class=xl7722973>Team C</td>
<td class=xl7722973>1</td>
<td class=xl7722973>1</td>
<td class=xl7722973>0</td>
<td class=xl7722973>0</td>
<td class=xl7722973>2</td>
<td class=xl7722973>4</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>Week</td>
<td class=xl7722973></td>
<td class=xl7822973>Game 1</td>
<td class=xl7822973>Game 2</td>
<td class=xl7822973>Game 3</td>
<td class=xl7822973>Game 4</td>
<td class=xl7822973>Total</td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7822973>Team</td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team D</td>
<td class=xl7722973>600</td>
<td class=xl7722973>540</td>
<td class=xl7722973>580</td>
<td class=xl7722973>500</td>
<td class=xl7722973>2220</td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team B</td>
<td class=xl7722973>580</td>
<td class=xl7722973>560</td>
<td class=xl7722973>560</td>
<td class=xl7722973>490</td>
<td class=xl7722973>2190</td>
<td class=xl1522973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl7822973>Total Points</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team D</td>
<td class=xl7722973>1</td>
<td class=xl7722973>0</td>
<td class=xl7722973>1</td>
<td class=xl7722973>1</td>
<td class=xl7722973>2</td>
<td class=xl7722973>5</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'></td>
<td class=xl7722973>Team B</td>
<td class=xl7722973>0</td>
<td class=xl7722973>1</td>
<td class=xl7722973>0</td>
<td class=xl7722973>0</td>
<td class=xl7722973>0</td>
<td class=xl7722973>1</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>Week</td>
<td class=xl7722973></td>
<td class=xl7822973>Game 1</td>
<td class=xl7822973>Game 2</td>
<td class=xl7822973>Game 3</td>
<td class=xl7822973>Game 4</td>
<td class=xl7822973>Total</td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7822973>Team</td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team E</td>
<td class=xl7722973>480</td>
<td class=xl7722973>460</td>
<td class=xl7722973>490</td>
<td class=xl7722973>400</td>
<td class=xl7722973>1830</td>
<td class=xl7722973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team F</td>
<td class=xl7722973>550</td>
<td class=xl7722973>470</td>
<td class=xl7722973>470</td>
<td class=xl7722973>450</td>
<td class=xl7722973>1940</td>
<td class=xl1522973></td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl1522973></td>
<td class=xl7822973>Total Points</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'>1</td>
<td class=xl7722973>Team E</td>
<td class=xl7722973>0</td>
<td class=xl7722973>0</td>
<td class=xl7722973>1</td>
<td class=xl7722973>0</td>
<td class=xl7722973>0</td>
<td class=xl7722973>1</td>
</tr>
<tr height=20 style='height:15.0pt'>
<td height=20 class=xl7822973 style='height:15.0pt'></td>
<td class=xl7722973>Team F</td>
<td class=xl7722973>1</td>
<td class=xl7722973>1</td>
<td class=xl7722973>0</td>
<td class=xl7722973>1</td>
<td class=xl7722973>2</td>
<td class=xl7722973>5</td>
</tr>
<tr height=0 style='display:none'>
<td width=77 style='width:58pt'></td>
<td width=77 style='width:58pt'></td>
<td width=77 style='width:58pt'></td>
<td width=77 style='width:58pt'></td>
<td width=77 style='width:58pt'></td>
<td width=77 style='width:58pt'></td>
<td width=77 style='width:58pt'></td>
<td width=89 style='width:67pt'></td>
</tr>
</table>

 

[DeBeuz]

Hi,

I would remove in each week the line after the line with "Week" in column A, and also the 3 counting rows (and separator row).
Rename "Total" column to "Subtotal" and and an extra column "Total" at the end.
Sub total for team A (G2) would be: =IF(C2>C3,1,0)+IF(D3>D4,1,0)+IF(E3>E4,1,0)+IF(F3>F4,1,0)
Sub total for team C (G3) would be: =IF(C2>C3,0,1)+IF(D3>D4,0,1)+IF(E3>E4,0,1)+IF(F3>F4,0,1)
Note you can not drag this formula from row 2 unless you freeze the referenced cells.
Also note that for team C the last two arguments of each IF are reversed.

In the total column for team A (H2): =IF(G2>G3,2,0)+G2 and for the total for Team C (H3): =IF(G3>G2,2,0)+G3 (G2 and G3 switched)

Likewise for all other teams.

Does this help you?
Paul

 

[mikewongbiz]

Thank you Paul for your advice. Yes, it does make the sheet a lot more nicer and presentable. Cheers, Mike