Hey all,

I'm trying to throw together a spreadsheet to analyse statistical data for a local sports comp. I'm pretty new to Excel (I'm using 2007 on XP), and I've been pretty happy at what I've been able to achieve, but there are two sets of statistics that are causing me grief: Those that relate to when a team are coming off the bye, and when a team last played on a particular day.

What I need are a set of columns in the main data sheet that produce positive results when these conditions are met. Something like this:


Basically, to consider the 'Home Team off Bye' column, I need a formula that will:

Take the Home Team's name --> check the 'Home Team' and 'Away Team' columns for the most recent instance of that team's name (this is the real sticking point) --> return a positive result if [current round #]-[round # of most recent game]=2.

So in cell E70 I should get a positive hit, as Canterbury's previous game was in round 10.

For the 'Off Monday' Columns I'm after the same thing, except checking if the previous game was on a Monday night.

Does that make sense? I hope someone can help me, I've been really struggling to work this out!

Cheers in advance!