calculated result based on cell data? Pretty complicated...

[Natenator]

Hi all,

Long time lurker and first time poster. I regret to make my introduction one where I ask (beg) for help but am at a loss achievable results.

I'm compiling some football stats (not soccer). For anyone not familiar with football you have 4 downs to get a min. of 10 yards to achieve a first down. You could be 1st down and 10 yards to go, get 12 yards and you have a first down. Or you could be 3rd down and 12 yards to go, get 15 yards and achieve a first down. Or you could be 2nd down with 2 yards to go, get 3 yards and have a first down.

I am trying to populate a table with whether or not a first down was achieved (converted) based on the down, distance to go, and yardage gained on that play information is already in the table. I am praying someone here may be able to help?

Here's an example of some data in my file:

DN DIST GAIN CONVERTED
1 10 14 YES
1 10 3 NO
2 7 8 YES
1 10 5 NO
2 5 14 YES
1 10 3 NO
2 7 21 YES
2 22 0 NO
1 10 17 YES
1 10 0 NO
2 10 14 YES
1 10 9 NO
2 1 5 YES
1 10 75 YES
1 10 1 NO
2 9 8 NO
3 1 3 YES
1 10 6 NO
2 4 16 YES
1 10 0 NO

I manually wrote in whether the down was converted or not but ideally I am looking for a formula to help achieve that but I am pretty lost on how to achieve that ANY help would be appreciated.

This one file has 10K rows which is why I only entered a sample of the data so you can see what I am working with.

Thank you!!

Regards,
Nate

 

[Natenator]

I cannot edit my posts it seems or I would have cleaned up the look of the sample table. Sorry for it looking so bad.

 

[Scott T]

Excel 2010

	A	B	C	D
1	DN	Dist	Gain	CONVERTED
2	1	10	14	YES
3	1	10	3	NO
4	2	7	8	YES
5	1	10	5	NO
6	2	5	14	YES
7	1	10	3	NO
8	2	7	21	YES
9	2	22	0	NO
10	1	10	17	YES
11	1	10	0	NO
12	2	10	14	YES
13	1	10	9	NO
14	2	1	5	YES
15	1	10	75	YES
16	1	10	1	NO
17	2	9	8	NO
18	3	1	3	YES
19	1	10	6	NO
20	2	4	16	YES
21	1	10	0	NO

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula D2 =IF(C2>=B2,"YES","NO") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Copy the formula down.

 

[Natenator]

Excel 2010

	A	B	C	D
1	DN	Dist	Gain	CONVERTED
2	1	10	14	YES
3	1	10	3	NO
4	2	7	8	YES
5	1	10	5	NO
6	2	5	14	YES
7	1	10	3	NO
8	2	7	21	YES
9	2	22	0	NO
10	1	10	17	YES
11	1	10	0	NO
12	2	10	14	YES
13	1	10	9	NO
14	2	1	5	YES
15	1	10	75	YES
16	1	10	1	NO
17	2	9	8	NO
18	3	1	3	YES
19	1	10	6	NO
20	2	4	16	YES
21	1	10	0	NO

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula D2 =IF(C2>=B2,"YES","NO") <tbody> </tbody>

<tbody>
</tbody>

Copy the formula down.

Oh my god. I cannot believe I didn't think of that myself. Forest from the trees issue of looking at things for too long

Thank you and apologies for wasting your time with something so trivial.