3D Barrel Racing Spreadsheet

[Kelley492775]

I need help setting up a spreadsheet that will arrange times, places, and divisions automatically if possible. To explain, each D is different. In a 4D format, The 1D needs to be the quickest time then the 2D is .5 Seconds off the quickest time. The 3D is 1.0 off the quickest time and the 4D is 2.0 off the quickest time. in a 3D format each D is 1.0 second split off the quickest time. Here is an example of the setup of how I manually do it. thanks so much in advanced.

#	RIDER'S NAME	HORSE'S NAME	TIME	PLACE	DIVISION
35	Lacie Brownless	Sister	15.686	1st	1D
11	Carlie Redfern	Peanut	16.092	2nd	1D
7	Julie Shaw	Roxy	16.152	3rd	1D
22	Rainey Booska	Jake	16.157	4th	1D
37	Elana Marchetti	Cashmere	16.417
14	Carlie Cooper	Bug	16.526
1	Pam Alderman	Dunie	16.671
23	Brittany Osburn	Tango	16.676
42	Julie Shaw	Bubba J	16.689	1ST	2D
33	Laurie Boger	Delta Jet	16.742	2nd	2D
2	Meghan Artymowycz	Scarlett	16.785	3rd	2D
32	Donna Lowery	Raisin	16.874	4th	2D
6	Stacy Marchetti	Felicity	16.879
31	Kendall Green	Jetter	17.095
28	Melissa Green	Annie	17.223
12	Carrie Wilmer	Ruger	17.438
36	Taylor Cronk	Katara	17.5
8	Brooke Buffkin	Harley	17.65
45	Denise Vickers	Harley	17.66
29	Kim Redfern	Dash	17.869	1ST	3D
16	Kat McCaslin	Levi	17.943	2nd	3D
21	Brittany Shortt	Bailey	17.959	3rd	3D
3	Caressa Thomas	Rocket	18.002	4th	3D
10	April Bratcher	Boo	18.008
47	Kayla Kiechle	Urban Tiger	18.219
18	Candace Fuller	Moonshine	18.259
48	Brooke Buffkin	CJ	18.263
41	Eve Santiago	My Lady	18.297
5	Taylor Cronk	Toph	18.414
43	Darlene Hawk	Rosa	18.496
9	Sarah Lapinski	Passion	18.956
44	Samantha Williams	Tucker	19.301
50	Kat Citta	Major	19.368
4	Jenny Parrott	Biscuit	19.944
30	Emily Faith Holder	Moxie	20.147
13	Missi Crapps	Martini	20.165
39	Brittany Darford	Mr No Name	21.004
46	Abby Campbell	Bubbles	21.621
25	Sage Stovall	Angel	24.237
24	Slone Fleckinger	Chancey	25.402
15	Alexis Huland	Buster	NT
17	Katie Corona	Power Commnder	NT
19	Jackie Brockhurst	Razz	nt
20	Emily Bevers	Rumor	nt
26	Shelby Gromme	Rusty	nt
27	Pam Alderman	RJ	nt
34	Lexi Woods	Chica	nt
38	Kaelynn Hawk	Luna	nt
40	Michelle Cartwright	Jessie	nt
49	KAMERON Laube	Slammer	nt

<tbody>
</tbody>

 

[stunnrock]

For 3D, try this in E2:

=IF(((E1="PLACE")+(E1=""))*(RIGHT($F2,1)="D"),"1st",IF(E1="","",INDEX({"2nd";"3rd";"4th";""},LEFT(E1,1))))

In F2:

=IF(COUNTIF(F1:F$1,"1D")<4,"1D",IF(($D2>$D$2+1)*(COUNTIF(F1:F$2,"2D")<4),"2D",IF(($D2>$D$2+2)*(COUNTIF(F1:F$2,"3D")<4),"3D","")))

Copy them down as far as required.

I'll leave you to work out the 4D.

 

[Kelley492775]

ok I copies and pasted that and its throwing me a little arrow sign and says #value in the E column and just the symbol in the F Column. lol im dumb I guess.

For 3D, try this in E2:

=IF(((E1="PLACE")+(E1=""))*(RIGHT($F2,1)="D"),"1st",IF(E1="","",INDEX({"2nd";"3rd";"4th";""},LEFT(E1,1))))

In F2:

=IF(COUNTIF(F1:F$1,"1D")<4,"1D",IF(($D2>$D$2+1)*(COUNTIF(F1:F$2,"2D")<4),"2D",IF(($D2>$D$2+2)*(COUNTIF(F1:F$2,"3D")<4),"3D","")))

Copy them down as far as required.

I'll leave you to work out the 4D.

 

[stunnrock]

Improvement for E2:

=IF(OR((E1="")*(RIGHT($F2,1)="D"),$D2=$D$2),"1st",IF(E1="","",INDEX({"2nd";"3rd";"4th";""},LEFT(E1,1))))

 

[stunnrock]

Does your table start in A1?

 

[Kelley492775]

Ok i got it. How would you do .5 second splits inastead of 1.0 second splits?

 

[stunnrock]

Where the second formula has $D$2+1 and $D$2+2, you would change them to $D$2+.5 and $D$2+1 respectively.

 

[Kelley492775]

ok so I got it to be a 4D format, but please look a E11. It should be 1st in the 3D but its starting on E12 but the F column is correct with the 16.862 starting the 3d. Can you help please

#	RIDER'S NAME	HORSE'S NAME	OPEN	PLACE	Division
3	Diamond Scharfenstine	GL Dashing Blonde	15.854	1st	1D
8	Elizabeth Bradin	Smoke	16.027	2nd	1D
14	Angela Mack	Sweetie	16.106	3rd	1D
9	Julie Shaw	Bubba	16.121
4	Jason Tibbs	Bonita Blitz	16.177
6	Elana Marchetti	Cashmere	16.309
35	Soleil Byrtus	Jett	16.64	1st	2D
10	Barbara Calhoun	Zac	16.683	2nd	2D
13	Brittanee Worden	Atlantis	16.747	3rd	2D
5	Brandie Harrell	Sky Scapper	16.862		3D
7	Pam Alderman	Dunie	16.889	1st	3D
26	Lillie Desimone	Stetson	16.893	2nd	3D
25	Brittanee Worden	Whiteout	17.129	3rd
30	Kady Rittmanic	Legend	17.179
22	Kendall Worden	Taffy	17.249
20	Diamond Scharfenstine	Missy's Sun Drift	17.251
29	Emily Bevers	Rumor Has It	17.324
23	Brandie Harrell	Easily A Free Runner	17.351
40	Ricki Godsey	Queen Bee	17.371
18	Candace Fuller	Moonshine	17.427
38	Vera Byrtus	Jake	17.652
36	Dawn Wilborn	Docs Easy Stick	17.739
11	Hollie Dowling	Hollywood	17.749
16	Kat McCaslin	Piper	17.826
21	Meghan Artymowycz	Shy	17.892	1st	4D
28	Morgan Mathusa	Fuze	18.044	2nd	4D
17	Kristal Farrell	Hallie	18.12	3rd	4D
39	Sarah Capinski	Fancy	19.078
12	Chelsey Bash	Dash	19.169
34	Shayne Hilton	Flick	19.217
33	Jessica Vancil	Cody	19.479
37	Christina Sylvester	Dixie	19.497
32	Sage Stovall	Nellie	23.768
24	Tiffany Jenkins	Kody	27.599
2	Katrina DeVuyst (Devust)	Ceary	NT
15	Ally Marchetti	Comeback Kid	NT
19	Jessica Knowles	Brownie	NT
27	Hollie Dowling	Romeo	NT
31	Dallas Kelley	Hank	NT
1	DRAW OUT
41	Kat McCaslin	Drifter	21.732
42

<tbody>
</tbody><colgroup><col><col span="2"><col><col><col></colgroup>

 

[Kelley492775]

Sorry if this is a repeat but in the reply above I cant see my message just the spreadsheet. I need help with 16.862 line. That time should be 1st in the 3D, but its saying the time under is 1st,. how do I change that?

 

[stunnrock]

That needed a bit of a rethink:

In E2, then copied downward:

=IF(RIGHT($F2,1)="D",INDEX({"1st";"2nd";"3rd"},COUNTIF(INDEX($F:$F,MAX(2,ROW()-2)):$F2,$F2)),"")