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?

 

[JenniferMurphy]

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 the result of that formula is used in the combined formula for Rank2. Since the formula involves a string concatenation the Rank1 result is text.
The circular reference is due to Rank2 referring to itself.

Aha! Now I see that it was I who misunderstood. So even if my circular reference could be resolved, I would still be adding numbers and text.

Thanks for taking the time to clarify. ??

 

[Alex Blakenburg]

No problem. Happy to help.