# Setting a Number Cell Learn how to format and calculate number cells using the NumberCellType and CurrencyCellType classes in C#, with precision and formatting options for decimal numbers and fractions. ## Content You can use a number cell for entering double-precision floating point numbers as well as fractions. You can display decimal numbers, integers, or fractions. The topics below discuss the various aspects of number cell formatting and calculation. You use the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) class to set the number cell and its properties. Use the [CurrencyCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.CurrencyCellType.html) class to set the currency cell and its properties. ## Setting Precision Numbers are typically calculated and stored using the Double data type which provides an accuracy of about 15 digits. The cell can be formatted to display as many or as few digits as you want. For example, the following code would sum the values in the cell range A1:A5 and place the result in cell A6. The value stored in cell A6 would have full accuracy (up to the limits of the Double data type), but the text displayed in cell A6 would show the value rounded to the nearest tenths place (one decimal place). ```csharp NumberCellType ct = new NumberCellType(); ct.DecimalPlaces = 1; spread.Sheets[0].Cells[5,0].CellType = ct; spread.Sheets[0].Cells[5,0].Formula = "SUM(A1:A5)"; ``` Number cells supports 15 significant digits of precision. This is a total of all digits, integral and fractional. For example, when you have 10 fractional digits, you limit the number of integer digits to the left of the decimal to 5 digits. Also, there is the possibility of floating point errors with the Double data type. For more accurate precision of large numbers or numbers with large fractional portions, consider using a currency cell which uses the Decimal data type and is not prone to floating point errors. ## Formatting Numbers You can customize the number cell to display the number as an integer or decimal with several formatting features as summarized in this table of properties. An example of the use of these properties is provided after the table. | **Property** | **Description** | | -------- | ----------- | | [DecimalPlaces](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.DecimalPlaces.html) | Sets the number of decimal places in the display of the number, for a decimal number. | | [DecimalSeparator](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.DecimalSeparator.html) | Sets the decimal character for the display of a decimal number. | | [FixedPoint](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FixedPoint.html) | Sets whether to display zeros as placeholders in the decimal portion of the number for a fixed-point numeric display. | | [LeadingZero](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.LeadingZero.html) | Sets whether leading zeros are displayed. | | [MaximumValue](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.MaximumValue.html) | Sets the maximum value allowed for user input. | | [MinimumValue](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.MinimumValue.html) | Sets the minimum value allowed for user input. | | [NegativeFormat](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.NegativeFormat.html) | Sets how the value is formatted for negative values. | | [NegativeRed](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.NegativeRed.html) | Sets whether negative numeric values are displayed in red. | | [OverflowCharacter](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.OverflowCharacter.html) | Sets the character to use to replace the value if it does not fit the width of the display. | | [Separator](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.Separator.html) | Sets the string used to separate thousands in a numeric value. | | [ShowSeparator](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.ShowSeparator.html) | Sets whether to display the thousands separator string. | A complete list of formatting properties can be found in the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) class. You can use code, the Properties Window, or the Spread Designer to set these properties. ## Displaying Fractions The number cell can display values in a fraction format, so 0.01 can be displayed as 1/100. Set the [FractionMode](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionMode.html) property of the number cell to display values in the fraction format. You can type values in the cell as 0.01 or you can type 1/100 in the cell; both display as 1/100. The precision of the fraction can be set using the [FractionDenominatorPrecision](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.FractionDenominatorPrecision.html) enumeration (such as to display fractions as quarters, 1/4, etc.) or the [FractionDenominatorDigits](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionDenominatorDigits.html) to set the number of digits in the denominator, for 10s, 100s or 1000s or more. This table lists the fraction-related properties of the number cell. | **Property** | **Description** | | -------- | ----------- | | [FractionMode](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionMode.html) | Sets whether values are represented as fractions. | | [FractionConvertWholeNumbers](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionConvertWholeNumbers.html) | Sets whether to convert whole numbers to fractions when values are displayed as fractions. | | [FractionCustomFormat](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionCustomFormat.html) | Sets how values are displayed as fractions with custom formatting. To use the custom format, set the [FractionDenominatorPrecision](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionDenominatorPrecision.html) property to **Custom**. | | [FractionDenominatorDigits](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionDenominatorDigits.html) | Sets the number of digits when values are displayed as fractions. | | [FractionDenominatorPrecision](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionDenominatorPrecision.html) | Sets the precision when values are displayed as fractions. | | [FractionRenderOnly](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionRenderOnly.html) | Sets whether to allow fractions in edit mode when values are displayed as fractions. | Another way to set the fraction display is to set a value for the fraction custom format (using the [FractionCustomFormat](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionCustomFormat.html) property). The default value is "# ???/???" which formats the number as an integer (#) followed by a three-digit fraction (???/???). The question marks after the slash determine the number of digits of denominator precision of which there can be from one to fifteen (because 15-digit precision is the maximum). With the custom format, you can also specify the denominator, such as "# ???/100" or "# ??/64". If [FractionConvertWholeNumbers](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.FractionConvertWholeNumbers.html) is set to true, then there is no integer to display and the entire number is displayed as a fraction. The alignment of the display is determined by the alignment properties that are set for the cell. The number is not aligned based on the fraction display. (In the example below, the numbers are right aligned regardless of whether there is a fractional part or not.) A complete list of fraction properties can be found in the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) class. You can use code, the Properties Window, or the Spread Designer to set these properties. ## Using Spin Buttons By default, no spin buttons are shown, but you can display spin buttons on the side of the cell when the cell is in edit mode. You can set various spin functions using the properties of the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) class that begin with the word Spin. Refer to [Displaying Spin Buttons](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-celltypecustomize/spwin-setspinbuttons). ## Using the Pop-Up Calculator By default, in a number cell, if you double-click on the cell in edit mode at run-time, a pop-up calculator appears. You can specify the text that displays in the **OK** and **Cancel** buttons. For more information, refer to [Customizing the Pop-Up Calculator Control](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-celltypecustomize/spwin-setpopupcalculator). To prohibit the popping up of the calculator, cancel the FpSpread [SubEditorOpening](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.FpSpread.SubEditorOpening.html) event. Handle this event and set the *Cancel* argument of the [SubEditorOpeningEventArgs](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SubEditorOpeningEventArgs.html) to True. For more information on the properties and methods of the number cell type, refer to the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) class. For more information on the currency cell type, refer to the [Setting a Currency Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setcurrencycell). ## Using the Properties Window 1. At design time, in the **Properties** window, select the Spread component. 2. Select the **Sheets** property. 3. Click the button to display the **SheetView Collection Editor**. 4. In the **Members** list, select the sheet in which the cells appear. 5. In the property list, select the **Cells** property and then click the button to display the **Cell, Column, and Row Editor**. 6. Select the cells for which you want to set the cell type. 7. In the property list, select the **CellType** property and choose the **Number** cell type. 8. Expand the list of properties under the **CellType** property. Select and set these specific properties as needed. 9. Click **OK** to close the **Cell, Column, and Row Editor**. 10. Click **OK** to close the **SheetView Collection Editor**. ## Using Code for Formatting Numbers 1. Define the number cell by creating an instance of the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) class. 2. Set properties for the class. 3. Assign the number cell type to a cell or range of cells by setting the **CellType** property for a cell, column, row, or style to the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) object. ## Example This example sets a cell to be a numeric cell with certain formatting by assigning the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) object with defined formatting properties. ```csharp FarPoint.Win.Spread.CellType.NumberCellType nmbrcell = new FarPoint.Win.Spread.CellType.NumberCellType(); nmbrcell.DecimalSeparator = ","; nmbrcell.DecimalPlaces = 5; nmbrcell.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.UseRegional; nmbrcell.MaximumValue = 500.000; nmbrcell.MinimumValue = -10.000; fpSpread1.ActiveSheet.Cells[1, 1].CellType = nmbrcell; ``` ```vbnet Dim nmbrcell As New FarPoint.Win.Spread.CellType.NumberCellType() nmbrcell.DecimalSeparator = "," nmbrcell.DecimalPlaces = 5 nmbrcell.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.UseRegional nmbrcell.MaximumValue = 500.000 nmbrcell.MinimumValue = -10.000 fpSpread1.ActiveSheet.Cells(1, 1).CellType = nmbrcell ``` ## Using Code for Formatting Fractions 1. Define the number cell by creating an instance of the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) class. 2. Set the **FractionMode** property to true and other fraction properties as needed. 3. Assign the number cell type to a cell or range of cells by setting the **CellType** property for a cell, column, row, or style to the [NumberCellType](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CellType.NumberCellType.html) object. ## Example This example sets a cell to display numbers as fractions. ```csharp fpSpread1.ActiveSheet.Columns[0, 9].Width = 120; FarPoint.Win.Spread.CellType.NumberCellType frac = new FarPoint.Win.Spread.CellType.NumberCellType(); frac.FractionMode = true; frac.FractionConvertWholeNumbers = false; frac.FractionDenominatorPrecision = FarPoint.Win.Spread.CellType.FractionDenominatorPrecision.Custom; frac.FractionCustomFormat = "## ???/???"; frac.FractionDenominatorDigits = 3; fpSpread1.ActiveSheet.Columns[0].CellType = frac; fpSpread1.ActiveSheet.Columns[1].CellType = frac; fpSpread1.ActiveSheet.Cells[0, 0].Value = 5.00; fpSpread1.ActiveSheet.Cells[1, 0].Value = 5.01; fpSpread1.ActiveSheet.Cells[2, 0].Value = 5.02; fpSpread1.ActiveSheet.Cells[3, 0].Value = 5.03; fpSpread1.ActiveSheet.Cells[4, 0].Value = 5.04; fpSpread1.ActiveSheet.Cells[5, 0].Value = 5.05; fpSpread1.ActiveSheet.Cells[6, 0].Value = 5.06; fpSpread1.ActiveSheet.Cells[7, 0].Value = 5.07; fpSpread1.ActiveSheet.Cells[8, 0].Value = 5.08; fpSpread1.ActiveSheet.Cells[9, 0].Value = 5.09; fpSpread1.ActiveSheet.Cells[0, 1].Value = 25.000; fpSpread1.ActiveSheet.Cells[1, 1].Value = 25.011; fpSpread1.ActiveSheet.Cells[2, 1].Value = 25.021; fpSpread1.ActiveSheet.Cells[3, 1].Value = 25.031; fpSpread1.ActiveSheet.Cells[4, 1].Value = 25.041; fpSpread1.ActiveSheet.Cells[5, 1].Value = 25.051; fpSpread1.ActiveSheet.Cells[6, 1].Value = 25.061; fpSpread1.ActiveSheet.Cells[7, 1].Value = 25.071; fpSpread1.ActiveSheet.Cells[8, 1].Value = 25.081; fpSpread1.ActiveSheet.Cells[9, 1].Value = 25.091; ``` ```vbnet FpSpread1.ActiveSheet.Columns(0, 9).Width = 120 Dim frac As New FarPoint.Win.Spread.CellType.NumberCellType frac.FractionMode = True frac.FractionConvertWholeNumbers = False frac.FractionDenominatorPrecision = FarPoint.Win.Spread.CellType.FractionDenominatorPrecision.Custom frac.FractionCustomFormat = "# ???/???" frac.FractionDenominatorDigits = 3 FpSpread1.ActiveSheet.Columns(0).CellType = frac FpSpread1.ActiveSheet.Columns(1).CellType = frac FpSpread1.ActiveSheet.Cells(0, 0).CellType = frac FpSpread1.ActiveSheet.Cells(0, 0).Value = 5.00 FpSpread1.ActiveSheet.Cells(1, 0).Value = 5.01 FpSpread1.ActiveSheet.Cells(2, 0).Value = 5.02 FpSpread1.ActiveSheet.Cells(3, 0).Value = 5.03 FpSpread1.ActiveSheet.Cells(4, 0).Value = 5.04 FpSpread1.ActiveSheet.Cells(5, 0).Value = 5.05 FpSpread1.ActiveSheet.Cells(6, 0).Value = 5.06 FpSpread1.ActiveSheet.Cells(7, 0).Value = 5.07 FpSpread1.ActiveSheet.Cells(8, 0).Value = 5.08 FpSpread1.ActiveSheet.Cells(9, 0).Value = 5.09 FpSpread1.ActiveSheet.Cells(0, 1).Value = 25.000 FpSpread1.ActiveSheet.Cells(1, 1).Value = 25.011 FpSpread1.ActiveSheet.Cells(2, 1).Value = 25.021 FpSpread1.ActiveSheet.Cells(3, 1).Value = 25.031 FpSpread1.ActiveSheet.Cells(4, 1).Value = 25.041 FpSpread1.ActiveSheet.Cells(5, 1).Value = 25.051 FpSpread1.ActiveSheet.Cells(6, 1).Value = 25.061 FpSpread1.ActiveSheet.Cells(7, 1).Value = 25.071 FpSpread1.ActiveSheet.Cells(8, 1).Value = 25.081 FpSpread1.ActiveSheet.Cells(9, 1).Value = 25.091 ``` This is what the result looks like in Spread. ![Spread WinForms uses NumberCellType fraction properties to display decimal values as mixed and improper fractions across worksheet rows.](https://cdn.mescius.io/document-site-files/images/2238e618-a1fb-45a6-9ce5-9a4157bd2931/images/celltype-number-fraction.png) ## Using the Spread Designer 1. Select the cell or cells in the work area. 2. In the property list, in the **Misc** category, select **CellType**. From the drop-down list, choose the **Number** cell type. Now expand the **CellType** property and various properties are available that are specific to this cell type. Select and set those properties as needed. The fraction properties are under the fraction tab. Or right-click on the cell or cells and select **Cell Type**. From the list, select **Number**. In the **CellType** editor, set the properties you need. Click **Apply**. 3. From the **File** menu choose **Apply and Exit** to apply your changes to the component and exit Spread Designer. ## See Also [Setting a General Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setgencell) [Setting a Text Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-settextcell) [Setting a Date-Time Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setdatetimecell) [Setting a Currency Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setcurrencycell) [Setting a Mask Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setmaskcell) [Setting a Percent Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setpercentcell) [Setting a Regular Expression Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setregexpcell) [Setting a Rich Text Cell](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-setcelltypes/spwin-seteditcelltypes/spwin-setrichtextcell)