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

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