Type of function to use

[Jock]

Hi,

Firstly, I posted this in excel.misc but on reflection (seeing as I'm after
function help) I thought I'd post in here, so apologies for any duplication.

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

 

[Max]

.. 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

.... 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.

Perhaps something along these lines might work
based on your set-up ..

Assuming your matches and results table
are in Sheet1, B4:G309
and col G (Results) will be filled-in *progressively*

Put in A4: =IF(G4="","",ROW(A1))
Copy down to A309

This'll progressively put sequential numbers 1,2,3 ...
into A4:A309 as col G (Results) is filled down

In Sheet2
-------------
You've listed all the Teams in col A, A2 down

Team1
Team2
etc

Put in B2 and array-enter (CTRL+SHIFT+ENTER):

=IF(ISERROR(SUM(OFFSET(Sheet1!$G$3,LARGE(IF(Sheet1!$C$4:$C$309=TRIM(A2),Shee
t1!$A$4:$A$309),3),):OFFSET(Sheet1!$G$3,LARGE(IF(Sheet1!$C$4:$C$309=TRIM(A2)
,Sheet1!$A$4:$A$309),1),))),"",SUM(OFFSET(Sheet1!$G$3,LARGE(IF(Sheet1!$C$4:$
C$309=TRIM(A2),Sheet1!$A$4:$A$309),3),):OFFSET(Sheet1!$G$3,LARGE(IF(Sheet1!$
C$4:$C$309=TRIM(A2),Sheet1!$A$4:$A$309),1),)))

Copy B2 down

Col B will return the total of the last 3 results
(from col G in Sheet1) for the corresponding team in col A

If the team's results in col G in Sheet1 are less than 3 instances,
or for any unmatched team names, or empty cells in col A,
blanks: "" will be returned

Just adjust the "k" param i.e. the "3" in the LARGE(array,k)
part within the 1st OFFSET(...) :
.... LARGE(IF(Sheet1!$C$4:$C$309=TRIM(A2),Sheet1!$A$4:$A$309),3)

to say, "2", if you want the total of the last 2 instances
instead of the last 3 instances

 

[Max]

Clarification: Suggestion assumes
that the Teams' data in Sheet1 col C
is pre-sorted* by the Team,
viz. sample data-set below:

Sheet1
----------

A____B_____C___G < columns (cols D to F skipped)
1 01-Jun-04 Team1 1 < row4
2 15-Jun-04 Team1 0
3 01-Jul-04 Team1 1
4 01-Aug-04 Team1 1
B 15-Aug-04 Team1 B < B = "blank" (assume yet to play)
6 01-Jun-04 Team2 1
7 15-Jun-04 Team2 0
8 01-Jul-04 Team2 1
9 01-Aug-04 Team2 0
B 15-Aug-04 Team2 B < B = "blank" (assume yet to play)
etc

*if col C is not sorted by Team,
do a sort on the table using col C first
before putting in the formulas

In Sheet2
-------------
With the teams listed in A2 down:
Team1
Team2
etc

For the sample data-set above,
the formula in col B will return:
in B2: 2 < 0+1+1 (total of last 3 results for Team1)
in B3: 1 < 0+1+0 (total of last 3 results for Team2)