620516_en-US

liliconcom

Formula Column omits last row

Hey all,

a customer asked me about an issue with formula evaluation and sent me these screenshots.

(They created this formula for a formula column, since they want the table to grow continuously.)

The issue they see with this is that the cell of the formula column of the last row remains empty.

 

Is there any obvious reason for this? And a way to solve it?

 

(the formula is supposed to find the related reference in column 1 ("Spalte 1"), then divide the value from column 2 ("Spalte") of the current row by the respective value from the row with the match. Column 3 ("Spalte 3") is the formula column.

Thanks and best regards,

Florian

Re: Formula Column omits last row

is the nested If() returning the third argument because the first argument evaluates to false? It returns the string "no match," but it is a numeric column, so the result is missing (".").

 

Also, they formula is evaluated row-wise automatically. The formula expression further iterates over they rows and for each row, iterates over all the rows. Is this computation valid?

Re: Formula Column omits last row

I agree with Mark. The formula is doing a lot of iterating in each row. What exactly are they trying to compute?

Furthermore, they are assigning a value, perhaps repeatedly, to Spalte 3[i] in the middle If() condition. This is unconventional and difficult to follow and perhaps not likely what they want.

Re: Formula Column omits last row

Hi Florian,

I would suggest the customer use something like the formula below instead of a nested loop. This formula divides by the value in the row, containing "0%", that has the same reference as the present row.

dt = :Spalte 1 << get data table;
Eval(
 Eval Expr(
  :Spalte 2 / :Spalte 2[ dt << get rows where(
   Contains( :Spalte 1, "0%" ) & Contains(
    :Spalte 1,
    Expr(
     Word( 3, dt:Spalte 1[Row()], "-" )
    )
   )
  ) ]
 )
);

If, as looks to be the case, the customer has "perfect" data, in the sense that the rows appear in predictable order and there are no missing rows, then this far easier formula will work just as well:

:Spalte 2 / :Spalte 2[ Mod ( Row ( )  - 1, 3 ) + 1 ]

These are columns :spalte 3 and :spalte 3a in the screenshot below. I've modified the column values so the ratios are trivial to check.

 

Cheers,

Brady