Formula with cell definitions

Posted by: michal-fp on 8 September 2017, 2:05 pm EST

  • Posted 8 September 2017, 2:05 pm EST

    <SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Arial','sans-serif';">Hi,<o:p></o:p></SPAN>

    <SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Arial','sans-serif';">I use a number cell type with integer number (DecimalPleaces= 0 and maxValue without decimalplaces) maxValue limit and everything works great. but when I add formula to this cell the value that greater than the max value or value that is not a integer value can be added <o:p></o:p></SPAN>

    <SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Arial','sans-serif';"> can you tell me how I can prevent it.<o:p></o:p></SPAN>

    <SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Arial','sans-serif';">TIA,<o:p></o:p></SPAN>

    <SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Arial','sans-serif';">Michal <o:p></o:p></SPAN>


  • Replied 8 September 2017, 2:05 pm EST


    Here is the sample code to prevent the value from being adding in a cell which are greater than maximum value or lesser than the minimum value set in the number cell type.

    fpSpread1.ActiveSheet.Cells[0, 0].Value = 22.2;
    FarPoint.Win.Spread.CellType.NumberCellType numCellType = new FarPoint.Win.Spread.CellType.NumberCellType();
    fpSpread1.ActiveSheet.Cells[0, 2].CellType = numCellType;
    numCellType.MinimumValue = 10;
    numCellType.MaximumValue = 100;
    numCellType.DecimalPlaces = 0;
    fpSpread1.ActiveSheet.Cells[0, 2].Formula = "PRODUCT(A1,4)"; 

    Here if the calculated value obtained by applying the formula is greater than the maximum value or less than the minimum value specified, then no result is shown in that cell & neither the value which is not an integer can be added.

    The version of the spread used is 4.0.2012. Which version is installed on your machine? Can you please modify the above code and post it back to us for review.


  • Replied 8 September 2017, 2:05 pm EST

    I do the same on on my code but i set the decimalPlaces property of the column on the designer (all others I set like you) i set the fourmla before i set the max abd Min value. when i tried to use this without formula every thing work correclty but now when i use the Round fourmla i can add value that greater then the Max value and i can add decimal nuber(after i leave the cell the number is rounded but idon't want to let the user to add decimal number to this cell )

    I use the 4.0.2000.2005 Version and  v2.0.50727 RunTimeVersion


  • Replied 8 September 2017, 2:05 pm EST


    The cell type determines how the user interacts with the cell (e.g. what values user can enter, how values are displayed to user, how values are converted to/from text).  The cell type does not affect values set by application code.  The cell type does not affect values set by formulas.  If you need a formula result restricted to a maximum or minimum value than you can use the MAX or MIN function in the formula.  If you need a formula restricted to a given number of decimal places then you can use one of the rounding functions in the formula (e.g. ROUND, ROUNDUP, ROUNDDOWN, MROUND, TRUNC).

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels