Nexel Formula - MATCH/INDEX

Fabio Giordano
Fabio Giordano Active Partner
Fourth Anniversary 25 Up Votes Level 200: Building A Planning Solution in Board Level 100: Foundations of Building in Board
in Platform

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

Answers

  • Previous Member
    Previous Member
    500 Up Votes 500 Likes 500 Comments Second Anniversary

    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
    Dietmar Jeschkeit Active Partner
    Fourth Anniversary 100 Comments 25 Up Votes 25 Likes
    edited March 2020

    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)

     

    image

     

    Best regards

    Dietmar

  • Fabio Giordano
    Fabio Giordano Active Partner
    Fourth Anniversary 25 Up Votes Level 200: Building A Planning Solution in Board Level 100: Foundations of Building in Board

    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
    Dietmar Jeschkeit Active Partner
    Fourth Anniversary 100 Comments 25 Up Votes 25 Likes

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

  • Fabio Giordano
    Fabio Giordano Active Partner
    Fourth Anniversary 25 Up Votes Level 200: Building A Planning Solution in Board Level 100: Foundations of Building in Board

    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

Categories