Nexel Formula - MATCH/INDEX

Hi Everyone,

i'm trying to replicate in Nexel the following excel formula:

=MATCH(TRUE;INDEX((B2:B8>A2);0);0)

The objective is to obtain the row number of a range where a condition is met for the first time.
In the example attached i'm looking to find in the range (B2:B6) the first row where the value is ">1000" (A2), so the 4th.

I can't find the right syntax and unfortunately the "Nexel formulae" doesn't help.

I'm working with Board version 10.1.2

Thank you,

Fabio

Find more posts tagged with

Accepted answers

All comments

Previous Member

I couldnt get the MATCH or INDEX formulas to work, but i got what i think you are after in a different way.

3 Blocks in a layout.

Block A is the "Value"

Block B and C are algorithms with nexel.

Block B Nexel: =If([@a;*;*]>200,1,0)

Block C Nexel: =if(and(sum([@b;*;*;Range.Up])=1,[@b;*;*]=1),1,0)

Block C will put a "1" in the first row to meet criteria based on block B logic.

Unfortunately, while you can sort on the nexel columns by clicking on the block headers, you cannot sort/filter on them in the layout unless you write to a cube (which you can do if needed.)

HTH.

Dietmar Jeschkeit

Hi Fabio Giordano,

in addition to J C you can achive what you want without nexel, only using algorithms.

Block a is your value

Block b is algorithm with the condition e.g. if(a>1000,1,0)

Block c is ranking function cumulated value

Block d is ranking funtion counter

Block e is algorithm to Show the counter e.g. if(and(b=1,c=1),d,0)

Best regards

Dietmar

Fabio Giordano

Hi guys,

thank you for your feedback.

Unfortunately this calculation must be done for each row of the A column (imagine the A column to be populated till A7 cell) in a single rule format, using range.down on the second column B.
With your solution in order to achieve this result i should create one column for each row and repeat the calculation changing the cell reference, unfortunately the number of rows is a dynamic variable.
This is why i wanted to use something like the MATCH/INDEX formula that gives me the results on the same row of the condition D2, D3 , D4 and so on..

Thank you

Dietmar Jeschkeit

I don´t understand. Both Solutions are working on a layout with multiple rows.

Fabio Giordano

Hi,

I'll try to make the complete example. As you can see in my print, for all the rows of my table I am looking for the first data in all the B column (cube) where the value is higher then A (cube). Then I'd like to write the row position of the range where the condition is first met in C.

In the example in cell D4 i am looking for the first value where B range is > 1000 (the content of A4 cell). It's important to use dynamic references of the cube. Since 1100 (B5) met the condition in the vertical range the results is 2 (the second raw of the range B4:B8 is > than the content of A. This calculation must be repeated in the same way for each raw of the table.

Your solution would work only if the results value is written on the 1st row of the table.

Hope this clarify the request

Thanks,

Fabio