Circular Reference

[pheroh]

Hello,

I have a sheet that simulates inventory levels at a store with deliveries for replenishment. I have the demand and leadtime be random so I am using conditions and INDIRECT multiple times.

A circular reference error has appeared and I cannot trace it to solve it as Excel is showing me 6 cells contributing to the circular computation! If I use a fixed value for leadtime (Column H), I have no issue. If I use the random one (Column J) which doesn't seem to be dependent on the required value of Net Stock (Column F), I get the circular reference.

Here is the Excel sheet:
https://www.dropbox.com/s/l829sndqgpr1977/circular ref.xlsx?dl=0

Can anyone please let me know how to fix it?

 

[Andrew Poulsom]

Welcome to MrExcel.

F10 references J10 and I10 references F10 and J10 references I10.

 

[bschwartz]

The circular reference is because the value in F10 is derived from J10, which is derived from I10, which is derived from F10.

F10: =G10+J10-N10
J10: =IFERROR(INDIRECT("I"&(ROW()-K10)),0)
I10: =IF((E10/$B$6=INT(E10/$B$6)),IF(F10<=$B$8,IF($B$10>$B$6,($B$7-O10-INDIRECT("I"&ROW()-$B$6)),($B$7-O10)),0),0)

You have similar circular references throughout the spreadsheet.

 

[pheroh]

Aha! Thanks.

Any suggestion on how I can avoid that? Ultimately, every solution I think of, still links to F10 :\

 

[bschwartz]

Can you explain what you are trying to accomplish?

 

[pheroh]

Can you explain what you are trying to accomplish?

(Summary is underlined) The column ordrd quantity simulates when a purchase order is placed. The criteria is once the review period (B6) is reached and the inventory on hand (Column F) is below "Order Point" (B8), an order is placed that will take a leadtime of 3 (B10) to arrive in column H.

Now as you can see it's a fixed leadtime. What I am trying to achieve is have a random leadtime based on the average and standard deviation (B10 & B11).

Column J simply tries to read the ordrd quantity so it can then be added to inventory on hand (column F) in the same day it arrives.

Now this is what I don't understand: If I use column H instead of column J in the formula in Column F, there is no problem despite that fact that it still relies on Column I just like column J.

 

[bschwartz]

Ok, let's go slow here. I understand columns F thru H, and column N. For column H, I am using this formula:

Cell H4:

=IF(AND(SUM(H1:H3)=0,OFFSET(F4,-$B$10,0)<$B$8),$B$7-OFFSET(F4,-$B$10,0),0)

I changed J4 to H4 in column F so we get back to your initial starting point.

Now, how should the J rule be different from the rule in column H? They are both material in, but J is to based off of what criteria?

Also what is column K doing?

 

[pheroh]

It is either I use column H or J. Column H gets material in based on a fixed lead time while J on a randomly generated value that is generated at Column L. Column K reads the last lead time generated for the latest ordrd quantity (Column I). Column J will read from K to see if the lead time is now passed to get the material in (I could remove column K altogether and just use its formula in J). What it really does is just read the ordrd quantity at I - lead time from K. If nothing was ordered (zero), then it will put zero.

Hope that was clear.