combining LOOKUP and IF functions

[ntnnj26]

I'm trying to use a state transition matrix in Excel. I randomly generate a
percentage and want to lookup the value in a corresponding table where this
value would fall if we sum across the row. For example, the first randomly
generated value is 13.5% and we are starting in state 6. Therefore, I want a
formula that looks in row 6 (starting state) and determines which column
(1-6) has the value 13.5% if you sum the values across (in this case the
answer is 5). Then 5 becomes the new starting state and so on. Is there a
formula or combination of formulas in Excel that can carry out this complex
function?
Thank you.

State transition matrix
1 2 3 4 5 6
1 85.00% 10.50% 3.10% 0.74% 0.66% 0.00%
2 8.90% 79.00% 9.20% 1.70% 1.20% 0.00%
3 1.00% 7.50% 73.00% 15.00% 2.50% 1.00%
4 0.00% 1.00% 13.00% 76.00% 9.00% 1.00%
5 0.00% 2.00% 5.00% 6.00% 71.00% 16.00%
6 0.00% 1.00% 1.00% 6.00% 9.75% 82.00%

 

[Joel]

This task is best writen in a custom function. You would pass into the
function the current state and the function would return the next state. It
really requires NO knowledge of excel. It is a simple Basic language program
that anybody who understands state machines as well as you do can write in a
few minutes.

The excel spreadsheet would simply have the formula =GetState(A5), where
cell A5 contains the current state.


the VBA function would look like this

Function GetState(CurrentState as Integer)


'Your basic code with one of the variables called NextState


GetState = NextState 'The return value of the function gets assigned
'to the function name

End Sub

The code is a random number genator and a two dimensional array.

 

[ntnnj26]

Thank you for your help. I actually have very limited knowledge of state
machines, I am just reading up on the matter now.
I think I may not have phrased my question correctly though. I have 250
randomly generated variables and I need to look them up in the matrix
according to my start state. So I need to involve in the equation the
randomly generated variable, the start state, and the matrix to get the
outcome (which will become the start state for the next step).

 

[ntnnj26]

Thank you for your help. I actually have very limited knowledge of state
machines, I am just reading up on the matter now.
I think I may not have phrased my question correctly though. I have 250
randomly generated variables and I need to look them up in the matrix
according to my start state. So I need to involve in the equation the
randomly generated variable, the start state, and the matrix to get the
outcome (which will become the start state for the next step).

 

[Joel]

excel is very nice in entering matrix type data, but has deficienties in
looking up data with multiple conditions. It may be possible to use Lookup
function for your task but wasn't sure from the description. Lookup has
different mode of operation (see help in excel).

If you had a table with the following numbers

..3
..5
..6
..8


and you look up 4 Lookup will return 3. but the numbers have to be in
increasing order. If the numbers weren't in order like

..6
..5
..8
..3

and you lookup .4. lookup will return .6

If you organize your state machine carefully you may be able to use the
excel spreadsheet. You can always use the VBA Macro to solve your problem.

I used the VBA language in a probabilty course to model shuffling cards. It
worked real well. I was able to output the results of the model into the
excel spreadsheet and then plot the result of the model.

I also used VBA to produce a seven year model of profits using a Monte Carlo
simulation. In nieither of these cases was I able to use just a spreadsheet.
I had to write VBA code.

 

[ntnnj26]

Thank you so much for your help, I think I may be approaching the problem
with the wrong tools based on your experience. I am trying to model rolling
a die using serial correlation instead of the natural probability of 1/6. I
will take the info you provided back to the drawing table. Thanks again!

 

[Joel]

You need to use Match and get a cumulative sum of you percentages.

If your percentages for 6 items are .2 .4. .3 .1 0 0 in columns a - f.

enter the sums in columns h - n. You need to 0 in the 1stcolumn and a dummy
7th colum that contains 1. the sums would look like this

0 .2 .6 .9 1 1 1

The formul for calculating the sums would be
cell H2 =0
cell I2 =A2+H2
Cell J2 =B2+I2
Cell K2 =C2+J2
Cell L2 =D2+K2
Cell M2 =E2+L2
Cell N2 =F2+M2 - will always be 1. I would just say =1

generate a random number

=Match(rand,H2:N2,1)
The match statement will return a number between 1 - 6.