Circular Reference Error

VinceF · Nov 12, 2023

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

ADJUSTED

9/24

9/27

9/1

10/1

10/4

10/18

10/24

11/12

3

ADJ

TODAY

PLAYER

INDEX

QUOTA

Quota

4

ADAM ALTMAN

22

20

0

5

ADAM PIOCH

6

ALLEN PRAET

7

ANTHONY STEFANI

29

8

AUSTIN ROMZEK

26

25

26

9

BILL KNIGHT

10

BILLY UHL

11

BRANDON LESKE

12

BRIAN KNOX

24

13

CHARLIE SCHULTZ

14

CHRIS KIPP

15

CRAIG COMO

16

CRAIG MCCARVER

24

25

23

17

DALE VANDERVERDE

18

DAN KOSMALSKI

19

DAN RAICEVICH

20

DAN SERVITO

16

21

DAN STAUB

26

22

DAVE REGIANI

23

DEAN PIOCH

24

DINO CERVI

25

DREW WEAVER

28

26

GALEN KERSTAN

23

27

GREG PINE

28

HUNTER WENDT

17

29

JAKE PRITCHARD

30

JAKE SLOAN

31

JAKE TRANSIT

34

31

32

34

32

JAMES BATTAGLIA

17

15

33

JEFF DUNCAN

34

JEFF KAMLAY

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 · Nov 12, 2023

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 · Nov 12, 2023

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:

Excel Formula:

=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

Excel Formula:

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:

Excel Formula:

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

Excel Formula:

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:

Excel Formula:

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

VinceF · Nov 12, 2023

6StringJazzer said:
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:

Excel Formula:

=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

Excel Formula:

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:

Excel Formula:

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

Excel Formula:

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:

Excel Formula:

=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 · Nov 12, 2023

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.

Excel Formula:

=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 · Nov 13, 2023

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 · Nov 13, 2023

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 · Nov 13, 2023

@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 · Nov 14, 2023

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

Circular Reference Error

VinceF

Board Regular

Excel Facts

awoohaw

Well-known Member

6StringJazzer

Well-known Member

VinceF

Board Regular

6StringJazzer

Well-known Member

VinceF

Board Regular

6StringJazzer

Well-known Member

awoohaw

Well-known Member

VinceF

Board Regular

Similar threads

Forum statistics

Share this page

Circular Reference Error

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock