I can't seem to find the problem with this worksheet calculation...

**paykanti** · 07-29-2009, 10:42 AM

Very irritated by this as i've played around with it for a while. I hope you can help without requiring an in-depth explanation as to the nature of the project as it's to do with some complex stochastic modelling but I think the issue is a basic Excel one I'm having.

To just explain, it's two worksheets. The 'Generator' worksheet is fine and it's just a random number generator which has created 200 random numbers in D6:D205 which I have defined as Rand1.

Now on the 'simulation' worksheet is where my problems arise. I've defined F8:J11 as CumuMatrix and F12:J12 as StateVector. The idea is that I start with a starting state (B9) and using the formula =LOOKUP(Rand1, OFFSET(CumuMatrix,B9,0,1,5), StateVector) in B10 I will end up with a new state. Since the OFFSET function directs the search to the appropriate row of the matrix CumuMatrix (B9 contains the current state, which is the row number, where the top row is always numbered 0); the parameters 1 and 5 refer to the number of rows (1) and the number of columns (5) which are to be looked at. Once the function has found the appropriate entry it returns the corresponding value from StateVector.

But as you can see, the chain in column B keeps getting stuck whenever it reaches state 4. Why is this?

Really desperate for some help as I've been playing around with it for hours now and it's doing my head in, cheers.

**DonkeyOte** · 07-29-2009, 11:13 AM

I suspect it's down to your use of OFFSET... for ex.

OFFSET(CumuMatrix,2,0,1,5)

equates to F10:J10... I suspect you mean for it to refer to F9:J9, ie revise your function to subtract one from value of preceding B value for use in Row offset:

B10: =LOOKUP(Rand1, OFFSET(CumuMatrix,B9-1,0,1,5), StateVector)
copy down

Though you could as easily use INDEX I think...

B10: =LOOKUP(Rand1,INDEX(CumuMatrix,MATCH(B9,$E$8:$E$11,0),0),StateVector)

I'm also assuming you're aware that using RAND1 in row10 for ex. pulls the value from Row 10 in the Generator Sheet (ie 0.0186 rather than the 2nd value 0.187922)