Easy Help

[VanCleave8]

Hey guys first timer here, as you can tell Ive struggled to insert a picture, Ive been trying for an hour. Hopefully, we can get by with this. So im creating a golf stats sheet to share with buddies. I want to create a Stroke Differential column. Here is what Id like to accomplish...I want my formula in M4. I need it to do this...anytime a name in column E matches I4 "BOGEY" then take the difference from Column D and Column F. So the math would look like 99-84=15....77-65=12...15+12=27 and continue that for the rest of the sheet. Then I need to be able to copy that formula down Column M to each golfer. In the actual sheet there is a blank column in H as D-G and I-M are their own tables. Any help I would greatly appreciate, Ive been screwing around trying to make this work for hours and cant seem to get it to work past the first row. Sorry again about not having a screenshot, hopefully this still works.

JVC

	D	E	F	G	I	J	K	L	M
3	MY SCORE	OPPONENT	OPP. SCORE	RESULT	OPPONENT	WON	LOSS	STREAK	STROKE DIFF
4	99	BOGEY	84	L	BOGEY
5	74	JMACK	88	W	JMACK
6	77	BOGEY	65	L	BG
7	78	CAPTAIN	78	T	CAPTAIN

<tbody>
</tbody>

DISREGARD EVERYTHING BELOW THIS

STROKE DIFF
4 99	BOGEY	84	L		BOGEY
W	JMACK
65	L		BG
7 78	78	T		CAPTAIN

<tbody>
</tbody>

<tbody>
</tbody>

 

[Snakehips]

So you only want a result based on 'BOGEY' ? Not for other names?
Do you want eg 15 and 27 to be as in my previous post but other rows blank?

 

[JLGWhiz]

Whiz, yes that's correct but I need the formula to run down the entire E column. So that formula needs to run E6 because it says bogey also. Then add that answer to 15 making the new answer 27. That make sense?

It was not a question. The formula you want is in the box where it says Code:

 

[Snakehips]

Just in case ...

Excel Workbook
	D	E	F	G	H	I	J	K	L	M
3	*	*	*	*	*	*	*	*	*	????
4	99	Bogey	84	*	*	*	*	*	*	15
5	74	Jmack	88	*	*	*	*	*	*	*
6	77	Bogey	65	*	*	*	*	*	*	27
7	78	Captain	78	*	*	*	*	*	*	*
8	70	Bogey	80	*	*	*	*	*	*	17
Sheet1

NB forget Ctrl + Shift + Enter for either. Not necessary.

 

[JLGWhiz]

In case somebody does not use all caps in column E.

=IF(UPPER(E4)="BOGEY",D4-F4,"")

formula would be posted in M4 in this case.

 

[VanCleave8]

Just in case ...
Sheet1

*	D	E	F	G	H	I	J	K	L	M
3	*	*	*	*	*	*	*	*	*	????
4	99	Bogey	84	*	*	*	*	*	*	15
5	74	Jmack	88	*	*	*	*	*	*	*
6	77	Bogey	65	*	*	*	*	*	*	27
7	78	Captain	78	*	*	*	*	*	*	*
8	70	Bogey	80	*	*	*	*	*	*	17

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:16px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula M4 =IF(E4="BOGEY",SUMPRODUCT((E$4:E4=E4)*(D$4:D4-F$4:F4)),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

NB forget Ctrl + Shift + Enter for either. Not necessary.

Tony,

You got it man THANK YOU!! This is how the formula came out to be exact, but you had it down.

=IF(E4=I4,SUMPRODUCT((E$4:E100=E4)*(D$4:D100-F$4:F100)),"0")

 

[VanCleave8]

I lied there were still issues when I started changing names and adding different names to the list. Here is a more comprehensive look at the entire tab...

Imagine after the Result column there is a page break and a new table. So M4 would need to return (D4-F4)+(D6-F6)+(D13-F13)+(D14-F14) = 9
M5 would need to return (D5-F5)+(D16-F16) = 4
M6 would return 0 because Ive never played BG.
and so on and so on down the list.

Hopefully, this bigger example makes it a little easier.

A	B	C	D	E	F	G	H	I	J	K	L	M
2	MY RECENT OPPONENTS		VS. OPPONENT ALL TIME
3	DATE	LOCATION	MY SCORE	OPPONENT	OPP. SCORE	RESULT		OPPONENT	W	L	STREAK	STROKE DIFF
4	12.31	Sun Willows	99	BOGEY	84	L		BOGEY	2	2	W2
5	12.29	Sun Willows	74	JMACK	88	W		JMACK	1	1	L1
6	12.28	Sun Willows	77	BOGEY	65	L		BG
7	12.27	Sun Willows	78	JVC	78	T		CAPTAIN
8	12.25	Sun Willows	78	JVC	87	W		POSEIDON
9	12.11	Sun Willows	86	ANDY	67	L		JVC	1	0	W1
10	11.31	Sun Willows	88	RICKY	88	T		RICKY	0	0
11	11.22	Sun Willows	78	FLUFF	87	W		JIM ROCK
12	11.21	Sun Willows	97	FERGIE	94	L		ANDY	0	2	L2
13	10.22	Sun Willows	73	BOGEY	84	W		YETI
14	10.11	Sun Willows	87	BOGEY	94	W		FLUFF
15	9.21	Sun Willows	92	ANDY	88	L		FERGIE	0	1
16	9.11	Sun Willows	97	JMACK	79	L		MITCHIE
								TOTAL	4	6

<tbody>
</tbody>

 

[Snakehips]

Now that helps!

Try...

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
1	MY RECENT OPPONENTS
2								VS. OPPONENT ALL TIME
3	DATE	LOCATION	MY SCORE	OPPONENT	OPP. SCORE	RESULT		OPPONENT	W	L	STREAK	STROKE DIFF
4	12.31	Sun Willows	99	BOGEY	84	L		BOGEY	2	2	W2	9
5	12.29	Sun Willows	74	JMACK	88	W		JMACK	1	1	L1	4
6	12.28	Sun Willows	77	BOGEY	65	L		BG
7	12.27	Sun Willows	78	JVC	78	T		CAPTAIN
8	12.25	Sun Willows	78	JVC	87	W		POSEIDON
9	12.11	Sun Willows	86	ANDY	67	L		JVC	1	0	W1	-9
10	11.31	Sun Willows	88	RICKY	88	T		RICKY	0	0		0
11	11.22	Sun Willows	78	FLUFF	87	W		JIM ROCK
12	11.21	Sun Willows	97	FERGIE	94	L		ANDY	0	2	L2	23
13	10.22	Sun Willows	73	BOGEY	84	W		YETI
14	10.11	Sun Willows	87	BOGEY	94	W		FLUFF				-9
15	9.21	Sun Willows	92	ANDY	88	L		FERGIE	0	1		3
16	9.11	Sun Willows	97	JMACK	79	L		MITCHIE
17	9.11	Augusta	68	Tiger	68			TIGER				0
Sheet1

 

[VanCleave8]

PERFECT!! And this time im not lying. Figured it would be easier with a bigger example of what I was trying to achieve.

If your bored, is there any way to get column L to auto populate? Those in the example were entered manually. Columns J and K are good to go with the COUNTIFS function.

Thanks again Tony, and i cant wait to see Tiger in April!!

Jake

 

[Snakehips]

What the devil else would I do on new year's eve?

Copy this into L4
Definitely an array formula requiring confirmation with Ctrl + Shift + Enter to add the curly braces.
Then drag down.

=IF((J4+K4)>0,IF(MAX(($E$4:$E$100=I4)*($G$4:$G$100="L")*ROW($G$4:$G$100))>MAX(($E$4:$E$100=I4)*($G$4:$G$100="W")*(ROW($G$4:$G$100))),"L","W")&MAX(SUMPRODUCT(($E$4:$E$100=I4)*($G$4:$G$100="L")*(ROW($G$4:$G$100)>MAX(($E$4:$E$100=I4)*($G$4:$G$100="W")*(ROW($G$4:$G$100))))),SUMPRODUCT(($E$4:$E$100=I4)*($G$4:$G$100="W")*(ROW($G$4:$G$100)>MAX(($E$4:$E$100=I4)*($G$4:$G$100="L")*ROW($G$4:$G$100))))),"")

Happy New Year !!

 

[VanCleave8]

Tony,

Happy New Year,

That W/L streak worked great however is was pulling information from the bottom not the top. I intend on adding new data to the top of the list so the most recent match is always at the top. Any chance there is an easy fix for this??

Thanks much,

Jake