Hi Franz,

I took a look at this KB article, and I'm afraid it's completely wrong. The CalcManager will already ignore null values in a cell when calculating the average. The code here in this article does nothing that would change this behavior.

This is easily demonstrated with the simplest of formulas: "average[columnName]"

You can see that any cell that has a 0 is counted as part of the average, but any cell with a null in it is not. You can even change a cell from 0 to null and back again and see that the average changes.

The formula syntax used by this article is meaningless.

"average(if(isblank( [Column 1]) , 0 , [Column 1] ))"

In this summary formula, a column is being passed into the isBlank function. Formulas are context-sensitive. So if you refer to [Column 1] in a grid column formula, then this refers to the cell in Column 1 for the same row for which the formula is being calculated. But in a summary, [Column 1] refers to all of the cells in the entire column and will therefore never be blank.

So this formula might as well be:

"average(if(FALSE()) , 0 , [Column 1] ))"

or

"average([Column 1])"

I haven't got any idea what the NamedReference that is added by this sample is supposed to do. It serves no function at all that I can see.

Anyway, I will see what I can do about getting this KB article removed so that it doesn't confuse anyone in the future.

The code you have here will not work, either, for the same reason.

IsBlank will never be false because a column cannot be blank.

To answer your question: no, you cannot do this with a single formula. Since nulls are ignored and 0's are not, you need an unbound column to convert a 0 into a null and then you can average the unbound column.

Here is what the correct code should look like:

        private void ultraGrid1_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
        {
            UltraGridLayout layout = e.Layout;
            UltraGridBand band = layout.Bands[0];

            UltraGridColumn myUnboundColumn = band.Columns.Add("My Unbound Column");
            myUnboundColumn.DataType = typeof(int);
            myUnboundColumn.Formula = "IF ( OR( isblank([Column 1]), [Column 1] = 0 ), null(), [Column 1])";
            myUnboundColumn.Hidden = true;

            SummarySettings summarySettings = band.Summaries.Add("average([My Unbound Column])");
            summarySettings.SummaryPositionColumn = band.Columns["Column 1"];

            //band.Summaries.Add("average([Column 1])");
        }

Notice that I added a plain average of Column 1 here and commented it out. I did that just as a comparison so I could make sure that my unbound column average is, in fact, different from the plain average when you enter a 0 in a cell.