I have a problem using the Formula Builder, I have to sum grid column B and C based on the content of another grid column A, the sum should be B minus C. Lets take your Formula Builder At Run-Time sample, where I put the following formulas to [Unbound 1]
1. Problem: Summing based on the content of a column:
sum (if ( 1=1, [Int32 1], [Int32 2] ) , [Int32 2] )
if I replace 1=1 by [Key] < 5 I get a VALUE error (this should sum only for those rows, where key < 5)
sum (if ( [Key] < 5, [Int32 1], [Int32 2] ))
2. Problem: Summing with calculated column
As in the example above, but I want to subtract [Int32_2] instead of adding it, therefor multiply by -1 produces a VALUE error
sum (if ( 1=1, [Int32 1], [Int32 2] ) , (-1) * [Int32 2] )
What is wrong in these formulas? Is there any detailed formular builder documentation available ?
Thankyou for your help.
Andreas
Hi Andreas,
There's no way to specify a condition sum where each value in the list is evaluated against a condition. I assume that you are applying these formulas to a Summary in the grid. In that case then the reference to "[Key]" represents an entire column, not a single cell. The formula gets evaluated only once, not one for each row. So you get a value error because you are comparing a column to a number and that doesn't make sense.
What you could do is add an unbound column to the grid whose value is the value you want in the formula. So you could add an unbound (probably hidden) column whose Formula is something like this: "if ( [Key] < 5, [Int32 1], [Int32 2])". This will work on a column because a formula on a column is applied to a cell and the cell knows that the references are references to other cells in the same row. Then your summary would be the sum of the unbound column.
Hi Mike,
Thankyou for the fast answer. Unbound column works great. Another problem i do have is the if syntax:
This works: if( [Key] >= 5 , [Int32_1] , log([Int32 2]) )
This results in a formula syntax error: if( ([Key] >= 5 and [Key] <= 7) , [Int32_1] , log([Int32 2]) )
How do I define a condition like where [Key] is
between a range, 5 and 7
or Key is lt 5 or ge 7
or Key ge 7 and Key <> 8 ?
I don't think there is an "and" operator, only an "and" function.
if ( ( and([Key] >= 5, [Key] <= 7) ), [Int32_1] , log([Int32 2]) )