I have a small problem.
My requirement is as follows:

I have two cells and have enabled manual calculation. The first cell loops through the values 1-5 whenever F9 key is pressed.
The function for this is
B9=IF(B9="1","2",IF(B9="2", "3", IF(B9="3", "4", IF(B9="4", "5", "1"))))
Whenever the value of cell B9 changes from 5 to 1, I need the value of cell C3 to change, but never otherwise.
The complication is that cell C3 changes randomly every time B9 changes from value 5 to value 1.
Right now, my formula for C3 is
C3=IF(B9="5",Rand()*100,C3)
Yes, I know that a circular reference is created, so I used manual calculation and made iteration limit to 1, change limit to 0 in Tools>Options>Calculation.
It seems to work, but I need a better solution, because I cannot refer cell C3 from any other formula as it says it causes a circular reference. I don't know why the circular reference in C3 is not caught in the first place.
Can anybody help me with this issue?