How to make a formula/function look at the most recently inputted cells

Hi Everyone,

I am creating a workbook of football scores that I will be updating weekly (or after each round of Premier League games is played.) I currently have made a sheet with team names in column A and also in row 1, making a kind of matrix which will show all the scores. I want to be able to calculate each team's current form (looking at last 6 games) by a simple average of their scores. The problem is my table is not in chronological order. Not sure if I have provided enough information here as this is my first post.

I will also be making another table looking at goals scored/conceded/points for each game, but the 'Team by Team' matrix will very much be the master sheet as I only want to input the most recent scores and for the rest of the sheets to update from that.

Thanks in advance for any help

Craig

There is nothing in that table to give you any date information or sequence of results, so how can you look for the last so-many games?

Perhaps you could have two rows for each team in your table - one for the score and the other for the fixture date, or even duplicate the table in another sheet but have dates instead of scores (more difficult for data entry).

I think you are going to have to have a fixture list of some sort, and that might be the starting point for data entry.

Hope this helps.

Pete

Here's a similar thread from a few month's back:

http://www.excelforum.com/excel-form...ng-a-team.html

Hope this helps.

Pete

Hi All,

Thanks for the replies. Sorry if I wasn't clear before - I know I don't have any fixture dates, to manually write out the fixtures for each team would be a horrible task (especially since I eventually want to apply this process to other leagues)

I was really asking if I can look for a range and then excel would give the most recent x records for that range, perhaps looking at the save date for the file? I did suspect before that this is probably not possible but just wanted to confirm or otherwise with you guys.

If this is indeed not possible, I was thinking that I may as well set up another table with the 20 teams in column A then week 1 in column B week 2 in column 3, and input the scores in here weekly also. That way I just have to move the range down 1 row every time I add a record.

Hope this makes sense and thanks again for the help.

Craig

You could find a full league Schedule online that you could pull into Excel. Then using the schedule and the score matrix you could derive the last 6 game goals for (GF) and goals against (GA) for each team.

The problem you would run into would be when one team plays another team more than one time. I have done this before for american football for fantasy leagues and you are best off building a table

Imagine the "-" are the columns, and below is some dummy data. You can then pivot and do subtotals/averages, (home/away), etc.
Week # - Team 1 - Team 2 - Location (home/road) - GF - GA
1 - Man U - Man City - Home - 3 - 2
1 - Man City - Man U - Away - 2 - 3