Is there any way around this circular reference error?

[JenniferMurphy]

This table tracks the odds and the rankings for several teams. These odds change every few days. The Δ columns show how the rankings changed since the last previous ranking.

2022 NBA playoff odds.xlsx
	C	D	E	F	G	H	I	J	K
6	Team	5/6	Rank3	Δ2-3	5/3	Rank2	Δ1-2	5/1	Rank1
7	A	200	1	+2	150	3	-1	175	2
8	B	175	2	-1	175	1	+3	150	4
9	C	160	3	=0	150	3	-2	180	1
10	D	150	4	-2	160	2	+1	170	3
Sheet3

Cell Formulas
Range	Formula
E7:E10	E7	=RANK.EQ([@[5/6]],[5/6])
F7:F10	F7	=[@Rank2]-[@Rank3]
H7:H10	H7	=RANK.EQ([@[5/3]],[5/3])
I7:I10	I7	=[@Rank1]-[@Rank2]
K7:K10	K7	=RANK.EQ([@[5/1]],[5/1])

I would like to combine the rank and Δ columns like this:

2022 NBA playoff odds.xlsx
	M	N	O	P	Q	R	S
6	Team	5/6	Rank3	5/3	Rank2	5/1	Rank1
7	A	200	1 (+2)	150	3 (-1)	175	2
8	B	175	2 (-1)	175	1 (+3)	150	4
9	C	160	3 (=0)	150	3 (-2)	180	1
10	D	150	4 (-2)	160	2 (+1)	170	3
Sheet3

But when I put this equation in H7

=RANK.EQ([@[5/3]],[5/3]) & " (" & [Rank1]-[Rank2] & ")"

it gets a circular reference error. Is there any way around this?

 

[6StringJazzer]

I'm not sure why you are getting a circular reference error but I definitely see a problem in what you are trying to do.

The formula you showed for H7 includes this this expression
[Rank1]-[Rank2]
but in your new format, Rank2 is no longer a number, it is text, and you cannot subtract it. Using the data you showed, for row 7 this would be
2 - "3 (-1)"
and it can't be done, it will give you a #VALUE error.

Combining multiple bits of data that is numeric to make it a string is usually counterproductive.

 

[JenniferMurphy]

I'm not sure why you are getting a circular reference error but I definitely see a problem in what you are trying to do.

The formula you showed for H7 includes this this expression
[Rank1]-[Rank2]

Did you mean I7? That's the cell that contains the formula you mention. The formula in H7 is =RANK.EQ([@[5/3]],[5/3]).

but in your new format, Rank2 is no longer a number, it is text, and you cannot subtract it. Using the data you showed, for row 7 this would be
2 - "3 (-1)"
and it can't be done, it will give you a #VALUE error.

Combining multiple bits of data that is numeric to make it a string is usually counterproductive.

The table in my second minisheet is all text. It is for illustration only. There are no formulas in any of the cells. I created it by copying the entire first table to the clipboard then pasting the values to the right of the first table. I then edited columns O and Q to show what I want the contents to be.

The formula that gets a circular reference is =RANK.EQ([@[5/3]],[5/3]) & " (" & [Rank1]-[Rank2] & ")". I put it in H7 which I wanted to look like Q7, in the second minisheet.

 

[Alex Blakenburg]

Does it do what you want if you change it to this:

=RANK.EQ([@[5/3]],[5/3]) & " (" & [@Rank1]-[@Rank2] & ")"

 

[JenniferMurphy]

Does it do what you want if you change it to this:

=RANK.EQ([@[5/3]],[5/3]) & " (" & [@Rank1]-[@Rank2] & ")"

Nope. I tried it both with the @s inside and outside the brackets (see H7 & H8).

2022 NBA playoff odds.xlsx
	C	D	E	F	G	H	I	J	K
6	Team	5/6	Rank3	Δ2-3	5/3	Rank2	Δ1-2	5/1	Rank1
7	A	200	1	+2	150	0	-1	175	2
8	B	175	2	-1	175	0	+3	150	4
9	C	160	3	=0	150	3	-2	180	1
10	D	150	4	-2	160	2	+1	170	3
Sheet3

Cell Formulas
Range	Formula
E7:E10	E7	=RANK.EQ([@[5/6]],[5/6])
F7:F10	F7	=[@Rank2]-[@Rank3]
H7	H7	=RANK.EQ([@[5/3]],[5/3]) & " (" & @[Rank1]-@[Rank2] & ")"
I7:I10	I7	=[@Rank1]-[@Rank2]
H8	H8	=RANK.EQ([@[5/3]],[5/3]) & " (" & [@Rank1]-[@Rank2] & ")"
H9:H10	H9	=RANK.EQ([@[5/3]],[5/3])
K7:K10	K7	=RANK.EQ([@[5/1]],[5/1])

Curiously, they don't get the circular reference every time. ????? And they both return the value "0".

Also, when I click on the MiniSheet button, a tiny blue dot appears very briefly (~1 second) with three sets of about 8-10 blue lines with arrow heads from that dot to cells below the tables. It disappears too quickly for me to be able to capture it. What could that be?

 

[JenniferMurphy]

I think I know what the problem is. Here's a simple example.

The formulas in G6:G10 do have a circular reference. I am asking Excel to calculate the rank and also use that value to calculate the difference. ($E6-$G6). I do not understand why it returns a zero and not just the error.

Book1
	C	D	E	F	G	H	I	J	K
5	Team	05/03/22	Rank	05/06/22	Rank (Δ)	Rank	(Δ)	Rank (Δ)	Rank (Δ)
6	A	58	4	69	0	4	(0)	4(0)	4 (0)
7	B	56	5	89	0	1	(4)	1(4)	1 (4)
8	C	81	2	89	0	1	(1)	1(1)	1 (1)
9	D	60	3	75	0	3	(0)	3(0)	3 (0)
10	E	86	1	64	0	5	(-4)	5(-4)	5 (-4)
Sheet1

Cell Formulas
Range	Formula
G6:G10	G6	=RANK.EQ($F6,$F$6:$F$10) & " (" & $E6-$G6 & ")"
H6:H10	H6	=RANK.EQ($F6,$F$6:$F$10)
I6:I10	I6	="(" & $E6-$H6 & ")"
J6:J10	J6	=$H6 & $I6
K6:K10	K6	=RANK.EQ($F6,$F$6:$F$10) & " (" & $E6-$H6 & ")"
E6:E10	E6	=RANK.EQ(D6,$D$6:$D$10)

I guess I'll have to calculate the rank in a different column than the rank+delta as in columns J & K.

Any better suggestions?

 

[Alex Blakenburg]

Starting with your original Example with your replacement formula being in Column H Rank2.

        =RANK.EQ([@[5/3]],[5/3]) & " (" & [Rank1]-[Rank2] & ")"

You have a couple of issues.

1) In the formula you are referring to [Rank2], this means the formula is referring to itself, hence the circular reference.
2) As per @6StringJazzer presumably you are going to do the same thing to Rank3 & Rank1 which means that you are trying to deduct a string value. In the case of Rank2, Rank1 will be a string that you are trying to deduct from the Rank2 value.

I believe this means you will need to independently redo all the calculations eg for Rank2

=RANK.EQ([@[5/3]],[5/3]) & " (" & RANK.EQ([@[5/1]],[5/1]) -RANK.EQ([@[5/3]],[5/3]) & ")"

Where
Rank2 = RANK.EQ([@[5/3]],[5/3])
Rank1 = RANK.EQ([@[5/1]],[5/1])

 

[6StringJazzer]

I am a bit late I see but with regard to this:

Did you mean I7? That's the cell that contains the formula you mention. The formula in H7 is =RANK.EQ([@[5/3]],[5/3]).

No, I meant H7, per your post:

But when I put this equation in H7

=RANK.EQ([@[5/3]],[5/3]) & " (" & [Rank1]-[Rank2] & ")"

 

[JenniferMurphy]

We have some sort of misunderstanding regarding text vs numbers. I guess I am not explaining it very well. I am not trying to do math on text anywhere is these examples. All the math is in the first table, columns C-K. The second table (M-S) is all text. I created it manually just to show that I wanted the result to look like. There are no formulas in that table. Anyway, that's not important or relevant to the problem.

I do have a circular reference, so I need to find another way. I did a little experimenting with enabling Iterative Calculations, but haven't gotten that to work, either.

Thanks

 

[Alex Blakenburg]

Hello Jen,
I don't believe I have misunderstood. The reference to text is taking your Rank2 to its logical conclusion which is to replace Rank1 with a combined formula and ther result of that formula is used in the comnbined formula for Rank2. Since the formula involves a string concatentation the Rank1 result is text.
The circular reference is due to Rank2 referring to itself.

See if the below helps.

20220507 Table Formula combining 2 Rank JenniferMurphy v02.xlsx
	O	P	Q	R	S
13	Description	Column	Formula	Result Data Type	Comment
14	Original Separate Formulas	Rank2	=RANK.EQ([@[5/3]],[5/3])	Number
15		Δ1-2	=[@Rank1]-[@Rank2]	Number
16
17	Combined Formulas
18	Jennifer's Sample	Rank2	=RANK.EQ([@[5/3]],[5/3]) & " (" & [Rank1]-[Rank2] & ")"	Text	Circular Reference. Rank2 is referencing itself
19
20	Eliminate reference to self	Rank2	=RANK.EQ([@[5/3]],[5/3]) & " (" & [Rank1]-RANK.EQ([@[5/3]],[5/3]) & ")"	Text	This should work as long as Rank1 is not also changed, since it will be a text concatenation
21
22	Modify Above to replace Rank1 cell ref (is text with combined formula)	Rank2	=RANK.EQ([@[5/3]],[5/3]) & " (" & RANK.EQ([@[5/1]],[5/1]) -RANK.EQ([@[5/3]],[5/3]) & ")"	Text
Sheet1