How to specify row range for a function like median??

[Byron Tate]

I have a griddataboundgrid, and the GridFormulaEngine going for functioning. I have Engine.CurrentRowNotationEnabled = true. I have 845 rows in the DataTable which is the source of the grid. I have numbers in column ''B''. I want the cells in column ''C'' to have this function:

=if(B0 > (median(B4:B845) + 100), -800000, B0)

Meaning: if the current cell in column B is > 100 away from the median of all the cells in column B, then put -800000 in this ''C'' column cell, otherwise copy current row''s ''B'' cell to column ''C''.

Question is, is there a way to notate ''last row'' in the functioning (instead of having ''B845'' hard-coded) without knowing what it is?

[Byron Tate]

In thinking further about this, I realize there are several situations where I want to know what the actual row number is in a formula that I''m using the CurrentRowNotation for a cell, like ''B0''. I need a function (and can write it if I know what to get from the Engine) that will give me the integer row number for the current row.

Can you give me some ideas/help? Thanks.

[Administrator]

The FormulaTag object for the cell has ParsedRow and ParsedCol properties in it. I think these properties should point to the row and col provided the cell''s FormulaTag object is not null.

As far as knowing the last row/col index, there is currently no notation for that. You could add a namged range to represent columns. So column 1 could be:

engine.AddNamedRange("ColRange1", string.Format("A1:A{0}", this.gridControl1.Model.RowCount));

Then you could write formulas like =Sum(ColRange1). But this would require you to add a named range for each column you want to use in this manner. You would also have to adjust th evalue of this named range when the row count in the grid was changed.

[Byron Tate]

thanks Clay. I am thinking about having a named range that is the entire column''s range, that is set every time my ''Calculate'' button is pressed to recalculate the grid.

[Byron Tate]

I''ve looked at the documentation on GridFormulaEngine for a way to access the FormulaTag object from an Engine function and can''t see how to do that.

I need to be able to add an engine library function that could get at the tag, and have this available in other functions.

[Administrator]

I do not think you will be able to get at the formulatag through the engine.

Since you are using a GridataBoundGrid and maintain the tag collection yourself, is it possible to get at the tag through your own collection?

[Byron Tate]

Yes I can. My question is, will the tags array be populated when the functions get called. I''ll test that out right now. It gets populated from the SaveCellInfo handler, when the ForceSaveCellInfo flag is true and RecalculateRange is called. But isn''t that too late for the functions? I am thinking they have all been called already to generate the values that get stored in SaveCellInfo handler.

[Administrator]

We have modified the code to the engine.RowIndex method so that if CurrentRowNotation is true and the row index in the passed string is zero, then the method will return the rowindex from the engine''s internal calculation context state. This should return what the engine thinks is the current row for the calculation at the time engine.RowIndex is called in this situation.

[Byron Tate]

Good. I also discovered yesterday that if I have an added function in the library, and call it with a current row notation cell ref, that when the function is called , the cell ref has been changed to an absolute row number.

I just had not seen that before, but it provides a way for me to know the current row without having to look at the GridFormulaTag. I''m going to write a simple function that just returns the argument string, and call it when I need to get the absolute row num into a function. I still think it''s important for engine functions to honor the current row notation flag. VLOOKUP will need special attention and testing since it requires it''s internal table argument to be defined with absolute cell references.

Anyway, this opens up for me the ability to define a function meta language so users can call an added function, GetRowValue(ColumnName,-4) that will be able to look up 4 rows and get the cell value of the column with name ''ColumnName'' and return it. This should solve my ''averaging window'' scenario where the function at the current row must average the 10 values above and below current row to obtain the value.