I have a pretty simple formula in several columns of a grid that I am exporting to Excel. it's Average Dollars Per Sale (Net Sales$/# of Sales Transactions). Originally I actually populated each cell with the actual number, but I wanted to use a formula instead so that they can subtotal the average properly in Excel.
Problem is, where there's no data, I am getting a divide by zero error. Ugly, but I wouldn't care if it didn't screw up subtotaling, but it does.
I need some way of NOT putting the formula in a cell (or overwriting it with nulls) when the result of that formula is going to be a problem.
Problem is, it appears that your formulas work at the Column, not cell level via the InitializeLayout event.
Is there any way to make this 'smarter', or to massage 'problem cells' after the fact?
Thanks.
I see you can do this in Excel:
=IF(denominator=0,"",numerator/denominator)
Is this possible within the grid formula? And if so, what would the syntax be?
Found the answer here: http://www.infragistics.com/community/forums/p/64060/324374.aspx#324374
e.Layout.Bands[0].Columns["int1"].Formula = "if( [int]=0 , 0 , 100/[int] )";
The only other question I have is: can a formula reference column indices instead of column names, and if so, what's the syntax?
Hello Samuel,
Thank you for your response.
It's not possible to reference columns by index, and this is actually a good thing, because if a column was hidden or a column was added, the calculation would be incorrect.
Please let me know if you have any other questions about this.
There are so many other things that reference columns by index, I don't see this as an issue. If a column is hidden, it still has an index value. We format by column indices - yet columns can be added. I don't see why formula s/b treated so differently from all of the other things you can do on a column via its index.
When you're working with the UltraGrid's collection properties, you're using the standard .NET way of accessing collection elements. You can use reference the elements by index, but you have to make sure the elements don't move within the collection or get removed or added or you'll have errors because of accessing the wrong object.
When you're creating a formula, you're not using the standard .NET way of accessing collection elements. The UltraGrid basically exposes a named reference for each cell in the row that's currently being processed. The "[name]" syntax is not accessing a collection element; it's accessing a named reference.
Thanks for that clarification. Very helpful.