Need help generating a complex formula

[Michaelson]

I am trying to put together a sports data spreadsheet. On the main data sheet I want to include two automatically generated columns to display the team's turnaround times between games.

This is the layout of the relevant part of the sheet. So let's say we wanted to generate this data for the highlighted team Wests Tigers. It's a simple enough matter of subtracting the date of the previous game they played from the date of their most recent game. In this case a formula that in the end boils down to '=B32-B21'

But is this possible to generate automatically?

I plan to have two columns, one for 'Home Team Turnaround' and one for away teams. So what I need is a formula that takes the date from the 'Date' column, then finds the team in question's most recent game from either the 'Home Team' or 'Away Team' column, then extracts the corresponding date for that game so it can be detracted from the initial date.

Does that make sense?

I'm thinking the round number (column A) may come in handy for this but still don't really know where to start... Any help would be greatly appreciated!

 

[DonkeyOte]

You might consider first adding a "key" column to the right on each row that concatenates Round, Home Team and Away Team into a single string, eg:

Z2:
=$A2&":"&$E2&":"&$I2&":"
copied down

Then, if we assume Home Turnaround to be displayed in Col X

X2:
=IF($A2=1,"n/a",$B2-SUMIF($Z:$Z,$A2-1&"*:"&$E2&":*",$B:$B)
copied down

For Away side (Col Y) we simply adjust the references in the Col Z test accordingly:

Y2:
=IF($A2=1,"n/a",$B2-SUMIF($Z:$Z,$A2-1&"*:"&$I2&":*",$B:$B))
copied down

(you could also use a MATCH with wildcard if preferred)

The same results could be achieved without use of Col Z, however, IMO it's worth adding for sake of efficiency.

 

[cgcamal]

Hi Michaelson,

I'm not sure if it is what you need, the following array formula take current value in "Home Team" column and look for more recent
dates to do the substraction.

As you can see in Result column (J), the values with blue background are the results for Team2 in column E.

B3-B2=1
B12-B7=5
B22-B15=7

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Rd	Date	Day	Venue	Home Team	Home H/T	Home 2H	Home Score	Away Team	Dates differences
2		11/03/2011			Team1				Team2	No previous game
3		12/03/2011			Team2				Team3	1
4		13/03/2011			Team3				Team4	1
5		14/03/2011			Team4				Team5	1
6		15/03/2011			Team5				Team6	1
7		16/03/2011			Team6				Team2	1
8		17/03/2011			Team7				Team8	No previous game
9		18/03/2011			Team8				Team9	1
10		19/03/2011			Team9				Team10	1
11		20/03/2011			Team10				Team4	1
12		21/03/2011			Team2				Team5	5
13		22/03/2011			Team3				Team6	9
14		23/03/2011			Team4				Team7	3
15		24/03/2011			Team5				Team2	3
16		25/03/2011			Team6				Team9	3
17		26/03/2011			Team7				Team10	3
18		27/03/2011			Team8				Team1	9
19		28/03/2011			Team9				Team4	3
20		29/03/2011			Team10				Team3	3
21		30/03/2011			Team1				Team4	3
22		31/03/2011			Team2				Team5	7
23		01/04/2011			Team3				Team6	3
24		02/04/2011			Team4				Team7	3
25		03/04/2011			Team5				Team8	3
...

Insert in J2 and copy down:

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
Hope this helps.

Regards

 

[DonkeyOte]

Cesar, given Array context I would advise you replace the volatile INDIRECT approach with INDEX

edit: FWIW, given the dataset is sorted I think you could simplify that approach.

 

[cgcamal]

Cesar, given Array context I would advise you replace the volatile INDIRECT approach with INDEX

edit: FWIW, given the dataset is sorted I think you could simplify that approach.

Thanks DonkeyOte,

I'll have present in my mind your kindly suggestion, replacing INDIRECT with INDEX the formula would be:

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter these manually yourself


Simplify the formula is a pending task for me

Regards.

Many thanks again.

 

[Michaelson]

Thankyou both for your responses. cg, your second suggestion seems to work, thanks! I don't really understand it though unfortunately, which means I can't work out what to change to generate the list for the Away team. Do you mind providing that formula as well?

Donkey, I tried your suggestion and as you can see got some funny results, like not working for rounds 2 and 3 at all, and getting some anomalous wrong results for the numbers it does generate.

Anyway, that's neither here nor there, just thought it may be of interest.

Having generated that Key column though, I wonder is there a way that I could generate a column that lists teams returning from the bye for those teams that didn't play in the round previous? That would be great if I could!

Thankyou both again!

 

[Michaelson]

I don't really understand it though unfortunately, which means I can't work out what to change to generate the list for the Away team. Do you mind providing that formula as well?

Just replace every E with an I and vice versa obviously.

Duh!

Any help generating that "Coming off Bye" column would be greatly appreciated.

 

[cgcamal]

Having generated that Key column though, I wonder is there a way that I could generate a column that lists teams returning from the bye for those teams that didn't play in the round previous?

You're welcome Michael.

Regarding the other question, please upload a sample excel file and show how should be the output you need for that
"Coming off Bye" column, and where should be to appera the result. Explain a little bit to anybody could
understand and help easier.

Regards.