State Machine in Excel

[Alex Gildenberg]

Hi,
I need to implement state machine in Excel as follows:
There are 6 valid states: S1,S2,S3 and B1,B2,B3
Valid moves between states:
S1=>S2=>S3=>
B1=>B2=>B3
All current states appear in one column(Say, Column A)
Each move between states triggers some action to be written in another
column (B) and new state to be written in A column
Example:
A B
S1 Started: State S1
N/A N/A
N/A N/A
S2 State S2 reached
S3 State S3 reached
......
B1 Started: State B1
N/A N/A
B2 State B2 reached
and so on


Straightforward approach is to use IF function in order to know which state
was the last one,
but I afraid that 7 nested IFs won't be enough for this. Moreover, if
tomorrow another states will be added,
IF definitely won't be enough.

Any ideas about implementation of this would be greatly appreciated.

Thanks in advance
Alex

 

[macropod]

Hi Alex,

You may be better off putting the various states into a lookup table that
you could reference via a LOOKUP function or a combined INDEX & MATCH
function pair.

In any event, you may be able to use IF tests without nesting, eg:
=IF(A1="S1","Started: State S1","")&IF(A1="S2","State S2
reached","")&IF(A1="S3","State S3 reached","")& .....

Cheers