J
Jock
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
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