Help with Incrementing in Formula Editor

[#1]

First of all... I'm very new to scripting and right now I'm working in the formula editor. I am trying to do something I thought would be fairly simple...

I have two columns of data. The first column has status values that are the same for a period of time and then will change and again be constant for a period of time. For example...

OFF

OFF

OFF

OFF

OFF

ON

ON

ON

etc.

In the second column I would like to increment a number whenever the status changes. So in the example above, the OFFs would be 1, the ONs would be 2, the next set of OFFs would be 3, etc.

Right now I am setting up a conditional statement something like this...

If( :C1 Status[Row()] == :C1 Status[Row() + 1],

:x,

:x = :x + 1

)

Where C1 Status is the OFF/ON row. I set up x as a Table Variable initially equal to 1,

But I'm getting some kind of cycle warning and it's not incrementing.

Any help would be appreciated.

[#2 · Accepted solution]

Hi Scott,

I tend to use the LAG function for this type of operation as shown below:

If( Row() == 1,
	1,
	If( :C1== Lag( :C1, 1 ),
		Lag( :PERIOD, 1 ),
		Lag( :PERIOD, 1 ) + 1
	)
)

It is not as elegant but it is reliable.

Best,

TS

[#3]

Brilliant! Worked like a charm. Thanks!