Formula Help

[Nagman]

Hi guys

Im new to forum and need some help..sorry to be demanding after just joining if someone can help explain me to do this would be great

I wish to make a spreasheet to help me make picks in 2 team sports,il try show example below. Its based on the amount of goals a team scores in previous and current season

So we have team A and team B

I need to make calculation of average goals based on number of games played last season,goals scored and goals conceded and also include current season

example last season data

Barcelona: games played 35
​

goals scored 75
goals conceded 35

current season data
Barcelona: games played 20
goals scored 35
goals conceded 20

Therfore overall to date is:
Barcelona: games played 57 (35+20 from above)
goals scored 111 (75+35)
goals conceded 53 (35+20)

So Barcelonas overall score record to date is (75+35) - (35+20) = 55

thats the first part so basically i need a sheet to be able to do this for 2 teams...so i can just enter the goals scored/conceded/games played into a cell and then the sum will be done automatically

Then as i mention we need it for 2 teams : TEAM A (barcelona) and TEAM B

So we have TEAM A which is barcelona =55
Then say we have TEAM B= -8

I then use formula 55-(-8)= 63
Once i have the above figure of 63 i need to divide this by the number of games played so 63/25= 2.52

The figure of 25 is just for arguments sake, i will again enter this into the sheet to get the figure of 2.52

If all this can be put on one sheet it would be great

Thats about it,i hope i explain ok and someone can help me with formula

 

[Blade Hunter]

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:143px;" /><col style="width:117px;" /><col style="width:48px;" /><col style="width:64px;" /><col style="width:143px;" /><col style="width:105px;" /><col style="width:43px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; font-size:10pt; ">last season data</td><td > </td><td > </td><td > </td><td style="font-family:Verdana; font-size:10pt; ">last season data</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Verdana; font-size:10pt; text-align:left; ">Barcelona</td><td >Games Played</td><td style="text-align:right; ">35</td><td > </td><td style="font-family:Verdana; font-size:10pt; text-align:left; ">Man Utd</td><td >Games Played</td><td style="text-align:right; ">20</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals Scored</td><td style="text-align:right; ">75</td><td > </td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals Scored</td><td style="text-align:right; ">80</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals conceded</td><td style="text-align:right; ">35</td><td > </td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals conceded</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Verdana; font-size:10pt; ">current season data</td><td > </td><td > </td><td > </td><td style="font-family:Verdana; font-size:10pt; ">current season data</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Verdana; font-size:10pt; text-align:left; ">Barcelona</td><td >Games Played</td><td style="text-align:right; ">20</td><td > </td><td style="font-family:Verdana; font-size:10pt; text-align:left; ">Man Utd</td><td >Games Played</td><td style="text-align:right; ">25</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals Scored</td><td style="text-align:right; ">35</td><td > </td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals Scored</td><td style="text-align:right; ">50</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals conceded</td><td style="text-align:right; ">20</td><td > </td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals conceded</td><td style="text-align:right; ">10</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Verdana; font-size:10pt; ">Overall to date is:</td><td > </td><td > </td><td > </td><td style="font-family:Verdana; font-size:10pt; ">Overall to date is:</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Verdana; font-size:10pt; ">Barcelona</td><td >Games Played</td><td style="text-align:right; ">55</td><td > </td><td style="font-family:Verdana; font-size:10pt; ">Man Utd</td><td >Games Played</td><td style="text-align:right; ">45</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals Scored</td><td style="text-align:right; ">110</td><td > </td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals Scored</td><td style="text-align:right; ">130</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals conceded</td><td style="text-align:right; ">55</td><td > </td><td style="font-family:Verdana; font-size:10pt; "> </td><td >Goals conceded</td><td style="text-align:right; ">15</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >Final Statistic</td><td style="text-align:right; ">0.73</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A7</td><td >=A2</td></tr><tr><td >B7</td><td >=B2</td></tr><tr><td >E7</td><td >=E2</td></tr><tr><td >F7</td><td >=F2</td></tr><tr><td >B8</td><td >=B3</td></tr><tr><td >F8</td><td >=F3</td></tr><tr><td >B9</td><td >=B4</td></tr><tr><td >F9</td><td >=F4</td></tr><tr><td >A12</td><td >=A2</td></tr><tr><td >B12</td><td >=B2</td></tr><tr><td >C12</td><td >=C2+C7</td></tr><tr><td >E12</td><td >=E2</td></tr><tr><td >F12</td><td >=F2</td></tr><tr><td >G12</td><td >=G2+G7</td></tr><tr><td >B13</td><td >=B3</td></tr><tr><td >C13</td><td >=C3+C8</td></tr><tr><td >F13</td><td >=F3</td></tr><tr><td >G13</td><td >=G3+G8</td></tr><tr><td >B14</td><td >=B4</td></tr><tr><td >C14</td><td >=C4+C9</td></tr><tr><td >F14</td><td >=F4</td></tr><tr><td >G14</td><td >=G4+G9</td></tr><tr><td >B17</td><td >=(C14-G14)/C12</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[Nagman]

thank you for your help blade hunter,much appreciated....pity you are a man utd fan lol joke

thanks again

 

[Blade Hunter]

thank you for your help blade hunter,much appreciated....pity you are a man utd fan lol joke

thanks again

Any time, post back if you need anything else.