Understanding a formula with a circular reference

[gruntingmonkey]

Hello again,

I have a formula as below that works out the difference between two scores (1 to 5) which are placed between columns F & O. F being year 2017, g being 2018 etc.

It selects the last two columns and returns an icon of green arrow up, yellow stays the same and red down.

=IF(INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(F46:O46)+5,4),"1","")&ROW())>INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(F46:O46)+4,4),"1","")&ROW()),100, IF(INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(F46:O46)+5,4),"1","")&ROW())=INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(F46:O46)+4,4),"1","")&ROW()),50, 0))

I keep getting a circular cell calc error but cant work out where it is. Does anyone have any ideas?

 

[Jonmo1]

In order to determine the cause of the circular reference, we need to know which cell that formula was put in.

 

[gruntingmonkey]

Im an idiot. The above example was in Cell E46.

 

[Jonmo1]

You have this
INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(F46:O46)+5,4),"1","")&ROW())

If the result of COUNTA(F46:O46) happens to be 0, then that translates like this
INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(F46:O46)+5,4),"1","")&ROW())
INDIRECT(SUBSTITUTE(ADDRESS(1,0+5,4),"1","")&ROW())
INDIRECT(SUBSTITUTE(ADDRESS(1,5,4),"1","")&ROW())
INDIRECT(SUBSTITUTE("E1","1","")&ROW())
INDIRECT("E"&ROW())
INDIRECT("E"&46)
INDIRECT("E46")
E46 = Circular reference.


Same for the other one that uses +4 instead of +5
ADDRESS(1,COUNTA(F46:O46)+4,4)
If counta = 1, then it still boils down to E46.

 

[gruntingmonkey]

Have you any idea how I might not get a circular reference?

 

[Jonmo1]

Put the formula in a cell that cannot end up being referenced by the formula.
It's all dependent on the result of COUNTA(F46:O46)+5 and +4
It's minimum result is 4, which would be column D
It's maximum result is 15 column O

So the formula should be in columns A B C, or P+

 

[gruntingmonkey]

But its impossible for CountA of COUNTA(F46:O46) to be 0.

And I cant change where the cell is located.

I'm now confused as to what the formula does... Although it does bring back the correct answer!

 

[Jonmo1]

But its impossible for CountA of COUNTA(F46:O46) to be 0.

Is it possible to be 1 ? That has the same problem in the 2nd reference of counta()+4. 1+4 = 5 = E

 

[Jonmo1]

Here's what the formula does.
With the formula in E46
And say the counta = 5 for example (it's only a circular reference if it's 0 or 1)
That whole formula boils down to this (from the counta, address, substitute, row and indirect functions)
=IF(J46>I46,100, IF(J46=I46,50, 0))

 

[gruntingmonkey]

Nope, minimum value is 2. Maximum is 10.