Help with Incrementing in Formula Editor

scott1588 — Sat, 10 Jun 2023 23:35:00 GMT

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

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.

Re: Help with Incrementing in Formula Editor

Thierry_S — Mon, 09 Aug 2021 20:34:54 GMT

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,

Re: Help with Incrementing in Formula Editor

scott1588 — Mon, 09 Aug 2021 21:05:06 GMT

Brilliant! Worked like a charm. Thanks!

topic Help with Incrementing in Formula Editor in Discussions

Help with Incrementing in Formula Editor

Re: Help with Incrementing in Formula Editor

Re: Help with Incrementing in Formula Editor