IF function and circular reference

dear all (A file is attached)
i am facing a little trouble. i got three cells namely; M7, AC7 and BA7. Initially, an input value is entered in cell M7 which will be processed to give AC7. An IF function is entered in cell BA7. This IF function is really simple. It states that IF the difference between the values of M7 and AC7 is greater than 1 then, decrease 0.01 from M7, otherwise, retain the value of M7. The syntax is:
=IF((M7-AC7)>1, M7-0.01, M7)
As I told you, it is really simple and looks fine. I have no problem with it so far. The problem came about when I tried to make the circular reference. I am trying to make M7 to equal BA7. Well, before I made the circular reference, I made a manual entering of the values of cell M7. What I have done was that I put a value like 29.94 in M7 then I let the calculation to proceed. The the value that I get in BA7 (i.e. 29.93), I re-entered it in M7 then I repeated the process until I got a value (i.e. 29.90) that was not changing due to the use of the IF function. The manual calculations are shown in Table2 of the attached file.
I said to myself instead of entering the values manually, I can use a circular reference so that the cell M7 = cell BA7. Unfortunatelly, the circulation of the values instead of giving me 29.90 which is the right answer, it gave me 29.89 which ment that Excel has done one more step further of the IF function. In other words, Excel should have stopped decreasing 0.01 from M7 when M7-AC7 was less than one. This is achieved at M7=29.90 (as clearly seen in Table2). However, Excel went one step further and done: BA7=29.90-0.01=29.89 then it stopped subtraction at 29.89 which is not correct. I don't know why.
I tried with two other people to resolve this dilemma but we couldn't. Then, I decided to seek help from you if you can. If you want to ask any question for clarification I would be mre than happy to reply to you.

hi guys

can you help in this matter, please?

Hi
I got a reply to my question in another forum. You can refer to it in this link: http://www.ozgrid.com/forum/showthre...d=1#post358464

I was hoping someone who could/would download the attachment would help. I can't download it, so I can't see the specifics of you calculation sequence.

One thought that comes immediately to mind. Not knowing the details of the calculation algorithm, I don't know for sure if this is an issue, but could rounding error be part of the problem? I assume when you did the manual calculations, you hand entered the numbers to two decimal places (ie 29.90 meaning 29.900000000000....). What is Excel converging to? Could Excel be converging to a value slightly greater than 29.90 (say 29.9011111....) which would correctly round (to two digits) to 29.90. But that value causes the logical test to return true, because the difference between the two cells is ever so slightly greater than 1?

Dear MrShorty

Please refer to the link mentioned in reply#3. In that reply a very logical reason was attributed to cause the problem.

Abdrahim

This reason?

the problem occurs because with the circular reference in place, Excel doesn't have a clear order of calculations to follow and hence when M7 changes value BA7 gets updated before AC7 recalculates.

Not having seen your spreadsheet, I suppose it's possible in this case. My own experience with circular calculations suggests that Excel is completely capable of performing them correctly without losing track of calculation order or anything like that. I still believe there was simply a bug in the way you implemented your algorithm that, if flushed out, would have allowed you to use the circular calculation to get what you wanted.

However, it could easily have taken quite a bit of effort to debug your calculations. The other posters simply saw that your problem was readily amenable to the algorithms already present in Solver. Their advice was simply to modify the existing spreadsheet slightly and let Solver do the iteration for you. I would agree with this kind of thinking.

I'm just glad you resolved your issue. I don't know what problem you were solving, but the other posters felt that the problem was best solved using Solver. So now you've solved your problem, and learned the basics of using Solver to solve future issues. That's good.