If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Function to calculate a team's form over previous 3 games only
Hi,
I keep a spreadsheet of a local football (soccer) league, I'd like to find a function that calculates how many of a home team's last 3 home games were won, drawn or lost (as well as goals scored & conceded). I keep the matches and results in date order in cells B4:G309 and a table of current form in AB4:AZ21 I currently use an array formula which has to be manually updated as the season progresses to take account of that ie at the beginning of the season the formula would look like this to calculate the total games won at home {=SUM((C4:C309="Team name")*(G4:G309=1))} where Column C contains the home teams and Column G is the calculated result (i.e. 1=Home win) As the season progresses the formula would simply change from C4:C309 to C100:309 for example. I'm tired of doing this manually and I'm sure there must be a logical way (and therefore an Excel function) of doing this without manually altering the figures telling Excel where to look for the information. I'm unsure what that function is though, hence this request for help. I'd also like to be able to do it, if possible, without recourse to VBA (as my knowledge of that extends to recording macros to sort data only). I only want Excel to calculate the results of the last 3 instances of any given team at home (or away for teams playing away) prior to their upcoming fixture. My tiny brain is being taxed to the limit on this one, so all offers of inspiration etc. gratefully received! Many thanks in advance, Stewart |
#2
|
|||
|
|||
Hi
see your other post -- Regards Frank Kabel Frankfurt, Germany "Jock" schrieb im Newsbeitrag ... Hi, I keep a spreadsheet of a local football (soccer) league, I'd like to find a function that calculates how many of a home team's last 3 home games were won, drawn or lost (as well as goals scored & conceded). I keep the matches and results in date order in cells B4:G309 and a table of current form in AB4:AZ21 I currently use an array formula which has to be manually updated as the season progresses to take account of that ie at the beginning of the season the formula would look like this to calculate the total games won at home {=SUM((C4:C309="Team name")*(G4:G309=1))} where Column C contains the home teams and Column G is the calculated result (i.e. 1=Home win) As the season progresses the formula would simply change from C4:C309 to C100:309 for example. I'm tired of doing this manually and I'm sure there must be a logical way (and therefore an Excel function) of doing this without manually altering the figures telling Excel where to look for the information. I'm unsure what that function is though, hence this request for help. I'd also like to be able to do it, if possible, without recourse to VBA (as my knowledge of that extends to recording macros to sort data only). I only want Excel to calculate the results of the last 3 instances of any given team at home (or away for teams playing away) prior to their upcoming fixture. My tiny brain is being taxed to the limit on this one, so all offers of inspiration etc. gratefully received! Many thanks in advance, Stewart |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
creating form with selections that change based on user's previous selection | Robert Bogart | Database Design | 1 | June 15th, 2004 06:05 PM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |
Calendar Object | Steve | Setting Up & Running Reports | 1 | May 18th, 2004 04:44 PM |
Form Doesn't Go To New Record | Steve | New Users | 15 | May 16th, 2004 04:33 PM |