# Formula Builder

#### This post has 3 Replies | 2 Followers

PV85281
Points 795
Replied On: Mon, Aug 18 2008 7:32 AM

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:

The following syntax works:

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

• Post Points: 20
Mike Saltzman
Points 440,660
Replied On: Mon, Aug 18 2008 10:43 AM

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.

Mike Saltzman

Manager - Windows Forms Development

Infragistics, Inc.

• Post Points: 40
PV85281
Points 795
Replied On: Tue, Aug 19 2008 4:27 AM

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 ?

• Post Points: 20
Mike Saltzman
Points 440,660
Replied On: Tue, Aug 19 2008 10:52 AM

I don't think there is an "and" operator, only an "and" function.

if ( ( and([Key] >= 5, [Key] <= 7) ), [Int32_1] , log([Int32 2]) )

Mike Saltzman

Manager - Windows Forms Development

Infragistics, Inc.

• Post Points: 25
Page 1 of 1 (4 items) | RSS