Circular Reference Error

[VinceF]

Hello..
For me this is a tough one....thanks for any assistance.
Golf Sheet

When I enter the formula G4+B4 into cell P4 I get a circular Reference error.

G4 reports the last value in row 4
C4 represents how the player scored against their quota for that round
B4 is the quota adjustment based on C4's result.

I'd like cell P4 to automatically populate based on the criteria in cells C4 & B4.

I hope that I explained this so that you can understand what it is that I'm trying to do..
Thanks,
VinceF
Excel 2016

RESULTS 11-5-23 INDIANWOOD QUOTA & SKINS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		MAIN		INSTRUCTIONS		(0/2) No Adjustment (3/6) +1 or -1pt (7/9) +2pts or -2pts (10) or More +3pts or -3 pts
2					STARTING	STARTING	ADJUSTED		9/24	9/27	9/1	10/1	10/4	10/18	10/24	11/12
3		ADJ	TODAY	PLAYER	INDEX	QUOTA	QUOTA		Quota	Quota	Quota	Quota	Quota	Quota	Quota	Quota	Quota	Quota	Quota	Quota
4		-3	-10	ADAM ALTMAN	9.0	23	20							22	20	0
5				ADAM PIOCH
6				ALLEN PRAET
7				ANTHONY STEFANI			29		29
8				AUSTIN ROMZEK			26		25		25	26
9				BILL KNIGHT
10				BILLY UHL
11				BRANDON LESKE
12				BRIAN KNOX			24					24
13				CHARLIE SCHULTZ
14				CHRIS KIPP
15				CRAIG COMO
16		+1	+5	CRAIG MCCARVER	7.7	25	23		24	24		25	23		23
17				DALE VANDERVERDE
18				DAN KOSMALSKI			17							17
19		-1	-3	DAN RAICEVICH	4.3	29	28		28
20				DAN SERVITO			16				16
21				DAN STAUB			26								26
22				DAVE REGIANI
23				DEAN PIOCH			33		33			33
24		0	0	DINO CERVI	3.0	30
25				DREW WEAVER			28		28
26				GALEN KERSTAN			23					23
27				GREG PINE
28				HUNTER WENDT			17			17
29				JAKE PRITCHARD
30				JAKE SLOAN
31				JAKE TRANSIT			34		31	32		32	32	32	34
32		+1	+5	JAMES BATTAGLIA	14.1	18	15		17		17		15
33		0	+2	JEFF DUNCAN	3.5	30	28			28
34				JEFF KAMLAY			26								26
Player Quota History

Cell Formulas
Range	Formula
B4:B34	B4	=IF(C4="","", IF(C4=0,0, IF(C4=1,0, IF(C4=2,0, IF(C4=3,1, IF(C4=4,1, IF(C4=5,1, IF(C4=6,1, IF(C4=-1,0, IF(C4=-2,0, IF(C4=-3,-1, IF(C4=-4,-1, IF(C4=-5,-1, IF(C4=-6,-1, IF(C4=7,2, IF(C4=8,2, IF(C4=9,2, IF(C4=-7,-2, IF(C4=-8,-2, IF(C4=-9,-2, IF(C4>=10,3, IF(C4>=-10,-3, IF(C4=-11,3, IF(C4=-12,3, IF(C4=-13,3, IF(C4=-14,3, IF(C4=-15,3, )))))))))))))))))))))))))))
C4:C34	C4	=IFERROR(INDEX(Main!$AK$11:$AK$50,MATCH($D4,Main!$G$11:$G$50,0)),"")
E4:E34	E4	=IFERROR(INDEX(Main!$H$11:$H$50,MATCH(D4,Main!$G$11:$G$50,0)),"")
F4:F34	F4	=IFERROR(INDEX(Main!$J$11:$J$50,MATCH(D4,Main!$G$11:$G$50,0)),"")
G4:G34	G4	=IFERROR(LOOKUP(2,1/($I4:$AL4<>""),$I4:$AL4),"")
P4	P4	=G4+B4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H4:H100	Cell Value	contains "FALSE"	text	NO
F4:F100	Expression	=ISNUMBER(#REF!)	text	NO
F4:F100	Expression	="if(and($B$2=""skins"",$E$2=ismumber)"	text	NO
F4:F100	Expression	=ISTEXT($D$36)	text	NO

 

[awoohaw]

you have range that includes the cell P4 in the formula that is in G4. See the underlined portions below:
(You're trying to add it to itself).

G4:G34

G4

=IFERROR(LOOKUP(2,1/($I4:$AL4<>""),$I4:$AL4),"")

 

[6StringJazzer]

We need to break down the logic of the calculation you want to do.


G4 reports the last value in row 4
That is correct. Note that in this example the last value in row 4 is P4.

C4 represents how the player scored against their quota for that round
C4 is a lookup for data on another sheet, but it's probably sufficient to know that it is retrieving data based on the player name in column D.

B4 is the quota adjustment based on C4's result.
B4 has what is technically known as "long-*** formula." But I get the idea of what it's doing.

I'd like cell P4 to automatically populate based on the criteria in cells C4 & B4.
And here is where the problem is. That description is not what the formula in P4 says:

=G4+B4

Nothing about C4 in that formula, although B4 is calculated from C4.

The reason you have the circular reference is that P is using G in its formula, but G is getting its value by scanning the row and getting the value from P.

Can you define what you want in P4 in more detail? What value are you trying to calculate in P4? If you were doing this on paper and pencil, how would you calculate P4?

Bonus Round

I think you have a logic problem in the formula in C4. You are using a cascading IF, which means that as soon as the evaluation is TRUE, it returns a value and stops. Way down in the formula you have

IF(C4>=-10,-3,

All the previous IF conditions cover all cases where C4>=-10. So pick any value where C4>=-10. Some IF will be TRUE before you ever reach this one. Did you mean "<="?

You also have four separate tests that all give the same result:

IF(C4=-11,3,
IF(C4=-12,3,
IF(C4=-13,3,
IF(C4=-14,3,
IF(C4=-15,3,

This could be replaced by

IF(C4<=-11,3,

I also wonder what logic you are using to get the results. I notice that a negative number will give a negative or 0 result, except for these last five values that give a +3 result. Is this an error? Should these really be -3?

If those last negative values should really give a result of -3, your entire formula can be replaced by this:

=LOOKUP(C4,{-99,-9,-6,-2,3,7,10},{-3,-2,-1,0,1,2,3})

 

[VinceF]

We need to break down the logic of the calculation you want to do.


G4 reports the last value in row 4
That is correct. Note that in this example the last value in row 4 is P4.

C4 represents how the player scored against their quota for that round
C4 is a lookup for data on another sheet, but it's probably sufficient to know that it is retrieving data based on the player name in column D.

B4 is the quota adjustment based on C4's result.
B4 has what is technically known as "long-*** formula." But I get the idea of what it's doing.

I'd like cell P4 to automatically populate based on the criteria in cells C4 & B4.
And here is where the problem is. That description is not what the formula in P4 says:

=G4+B4

Nothing about C4 in that formula, although B4 is calculated from C4.

The reason you have the circular reference is that P is using G in its formula, but G is getting its value by scanning the row and getting the value from P.

Can you define what you want in P4 in more detail? What value are you trying to calculate in P4? If you were doing this on paper and pencil, how would you calculate P4?

Bonus Round

I think you have a logic problem in the formula in C4. You are using a cascading IF, which means that as soon as the evaluation is TRUE, it returns a value and stops. Way down in the formula you have

IF(C4>=-10,-3,

All the previous IF conditions cover all cases where C4>=-10. So pick any value where C4>=-10. Some IF will be TRUE before you ever reach this one. Did you mean "<="?

You also have four separate tests that all give the same result:

IF(C4=-11,3,
IF(C4=-12,3,
IF(C4=-13,3,
IF(C4=-14,3,
IF(C4=-15,3,

This could be replaced by

IF(C4<=-11,3,

I also wonder what logic you are using to get the results. I notice that a negative number will give a negative or 0 result, except for these last five values that give a +3 result. Is this an error? Should these really be -3?

If those last negative values should really give a result of -3, your entire formula can be replaced by this:

=LOOKUP(C4,{-99,-9,-6,-2,3,7,10},{-3,-2,-1,0,1,2,3})

Woohaw, thank you for your reply..
6StringJazzer, to answer your question regarding cell Pa
P4 would be the result of cell O4 plus B4 which the result would be 17. 17 represents the players adjusted quota...I can easily manually do the math and enter 17 into P4 but I was hoping to automate it... hope this helps.
VinceF

 

[6StringJazzer]

OK, I think I see what you are trying to do. There is some confusion about the order in which Excel does things.

I suggest you use this formula in column J then copy to the right.

=IF(J$3="","",$B4+I4)

I have a feeling there is something going on here that you haven't told us about. Column C is labelled TODAY. Does this value change from day to day?

And of course there is still the issue of your formula in column B.

 

[VinceF]

Thanks again for your reply... I'm going to attempt to give a better explanation of what this sheet is doing.
This tracks a golf game where each player earns points (quota) based on their scores per hole and adjust their quota based on the last round of golf played.
A player earns 1 point for bogie, 2 pts for par, 4 pts for birdie and 6 for an eagle....
Adam started with a 23 quota but it's been adjusted down to 20, as shown in cell G4.
Adam played a round today and in cell C4 it reports that he missed his quota by -10 points and according to the adjustment table his quota going into the next round will be -3 less (as shown in cell B4) or 20-3= 17
I'm trying to get cell P4 to show the adjusted quota which would come into play on his next round by putting in the formula G4 - B4 but I get the Cir Ref error message. I tried using helper cells but it still gave the error message.

I hope that this clarifies it enough to understand the logic which my hope is that it results in a solution.
Thanks,
VinceF

 

[6StringJazzer]

I think this needs VBA. You are capturing a series of numbers in I:T that are calculated based on the value in B at an instant in time. If you put formulas in I:T that use B, and B changes all the time, those calculated values are going to change all the time too. But you want them to be static. Therefore I think you need VBA.

I am not going to try to develop an actual VBA solution for this without having a complete file to test it on. If you have some way to share your file (Dropbox, Google Docs, etc.) I can give it a try.

 

[awoohaw]

@VinceF
I'm familiar with this golf game scheme and point concept. But, it has been a while. But, why do you need to have the score in P4 included in the calculations of the quota when that date's match hasn't been played yet? I notice you are not posting regular scores, but you do have an INDEX recorded. Is that handicap index? And how does that figure into the quota calculation? And what happens when the handicap index changes. Or does that not matter since the quota tracking sort of manages the player's current skill level? Are you open to a different format/table view? I don't think a VBA solution is the only way. But, a better understanding of what you want would help.

 

[VinceF]

Jeff & Awoohaw, Thank you both for your assistance.
Jeff...I don't have dropbox or Google docs but I'd be more than happy to email you the file so you can see it in its entirety in hopes of better understanding what it is that I'm trying to accomplish.
Awoohaw, To answer your questions...Column I thru AL represents the players currant quota. After a round is played I manually input the adjusted quota into the row. Column G looks at the row and reports back the last value in the row...this is reported back to the main sheet. The Index # in column F is just the starting index to establish the players quota #...for instance if the players index is 7 and adjusting for the course rating/slope the players hdcp is 9 then I take 36-9 which gives me a quota # 17. Once a player has played a round the index number is no longer needed.

Hope this helps
VinceF