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?

 

[gruntingmonkey]

Not sure if this helps, but the formula should be

Minimum
=IF(G46>F46,100, IF(G46=F46,50, 0))

Maximum
=IF(O46>N46,100, IF(O46=N46,50, 0))

The count bit just sees how many cells has been filled in the row and uses the last two figures.

 

[gruntingmonkey]

So I did the following and I no longer get the message

Excel Options > Formulas, tick Enable iterative calculation

 

[gruntingmonkey]

soooo, I did the Math

=IF(INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(H9:O9)+7,4),"1","")&ROW())>INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(H9:O9)+6,4),"1","")&ROW()),100, IF(INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(H9:O9)+7,4),"1","")&ROW())=INDIRECT(SUBSTITUTE(ADDRESS(1,COUNTA(H9:O9)+6,4),"1","")&ROW()),50, 0))

I decreased the CountA possibility and increased the minimum value to 7 meaning that it will never fall on E (5).