Posted 8 September 2017, 1:41 am ESTI have a number cell at the bottom of my sheetview that sums the number cells above it. As soon as I entered the sumation formula, the cell displayed a "0". When I delete a numeric entry in the cells above, the edited cell remains blank, but the non-editable cell at the bottom continues to show "0". If I enter "0" in the editable cells above, the cells display "0". How do I eliminate the "0" in a formula cell and editable cells, but show numbers that are non-zero?
How do I not display zeros in number cells that sum?
Posted by: canis on 8 September 2017, 1:41 am EST
Replied 8 September 2017, 1:41 am ESTKen,
You can use a formula like the following.
FpSpread1.Sheets(0).Cells(3, 0).Formula = "IF(SUM(A1:A3)=0, """", SUM(A1:A3))"
Replied 8 September 2017, 1:41 am ESTThis worked for me:
Replied 8 September 2017, 1:41 am ESTThe following formula did not work for editable cells that are included in the sumation ranges above. The formula caused the sumation cells to be blank whether the sumation value was zero or not.
However, I found a work around with the Spread_LeaveCell Event. The following got rid of the zeros in the editable cells and the sumation cells displayed a blank cell only if the value was zero.
Private Sub spTime_LeaveCell(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.LeaveCellEventArgs) Handles spTime.LeaveCell
Dim Cell As FarPoint.Win.Spread.Cell
Cell = spTime.ActiveSheet.Cells(e.Row, e.Column)
If Cell.Locked = False And Cell.Text = "0" Then Cell.Text = vbNullString
Replied 8 September 2017, 1:41 am ESTSome spreadsheet applications [ex. Excel, OpenOffice] provide an option for displaying zero values as empty cells [ex. Tools | Options | View | ZeroValues in Excel 2000, Tools | Options | Spreadsheet | View | ZeroValues in OpenOffice 1.0]. FarPoint's Spread for WinForms product does not provide this option.
The above mentioned work arounds are worth considering, but be careful of a few things.
An empty cell value [null in C# or Nothing in VB], an empty string value [""], and a zero number value [0.0] are three distinct values. While most built-in operators and functions [ex. SUM] will treat the three values the same, a few buit-in functions [ex. ISBLANK, ISNUMBER] will treat the three values differently. Be aware of these small differences.
Spread uses the same formula syntax as Excel. This syntax does not provide a means of representing an empty cell value [null].
Scotts's solution for caluclated values [if(SUM(...)=0,"",SUM(...))] replaces a zero number value [0.0] with an empty string value [""]. Be aware that an empty string value [""] is different that an empty cell value [null].
Canis's solution for caluclated values [if(SUM(...)=0,DBNull.Value,SUM(...))] replaces a zero number [0.0] with a custom name [DBNull.Value]. Unless the custom name is defined [see AddCustomName method in SheetView class], the custom name will evaluate to a #NAME? error value. This is most likely not the intended result.
Canis's attempted solution for user entered values [if(RC=0,...,RC)] results in a circular reference. This is most likely not the intended result. If iterations are off [see Iterataion property in SheetView class] then the formulas will never be evaluated. If iterations are on then the formulas may not be evaluated in the desired order.
Another solution is to use custom cell types which render zero number values as empty cell values. This would more closely mimic Excel's feature. The code for a custom cell type might look something like...
public class MyGeneralCellType : GeneralCellType
public override void PaintCell(System.Drawing.Graphics g, System.Drawing.Rectangle r, FarPoint.Win.Spread.Appearance appearance, object value, bool isSelected, bool isLocked, float zoomFactor)
if (value is double && (double)value == 0.0)
value = null;
base.PaintCell(g, r, appearance, value, isSelected, isLocked, zoomFactor);
Replied 8 September 2017, 1:41 am ESTAlthough I placed "DBNull.Value" within a Spread formula, this does NOT evaluate to a #NAME? error value. I end up with a blank cell if the sumation value is "0". "DBNull.Value" is a Visual Basic keyword. It appears to me that Spread uses the Windows NET framework to place null in the cell and not its own internal "custom name" convention.
Replied 8 September 2017, 1:41 am ESTSpread uses the same formula syntax as Excel. If you enter a formula in Spread or Excel containing the token "DBNull.Value" then the token "DBNull.Value" will be parsed as a custom name. If "DBNull.Value" has not been previous defined [using AddCustomName method in Spread or using Insert | Name | Define in Excel] then the token "DBNull.Value" will evaluate to a "#NAME?" error. In Excel, the cell will be painted using the text "#NAME?". In Spread, how the cell is painted depends on the cell type. Some cell types [ex. General] will paint the cell using the text "#NAME?" while other cell types [ex. Number] will paint the cell the same as an empty cell.
Note: Excel essentially has a single cell type that allows editing/rendering of all supported value types [number, string, boolean, error]. Spread has multi cell types. The GeneralCellType allows editing/rendering of all supported value types including errors. The NumberCellType only allows editing/rendering of numeric values. For non-numeric values, the NumberCellType will paint the cell the same as an empty cell.
Replied 8 September 2017, 1:41 am ESTbobbyo:
Clearly you know more about this than I do. However, what I have done is working, and unitl it doesn't, I'm not changing a thing. If the sumation cell's value is "0", I get a blank cell. If the sumation cell's value is a number other than "0", I get that number, not a "#NAME?" error. Take my hacker code for what it's worth, but until I blow this up somehow, it stays the way it is.
Your information may be useful to me in another situation, so thanks for the comments.
Replied 15 January 2021, 4:06 pm ESTI know this is resurrecting a thread long after it was active but I found this trying to make my zero's disappear and thus improve readability and also highlight missing values.
So far I appear to have had success using conditional formatting - if a cell = 0 then set both font colour and back colour to white (or both to whatever colour you are want). You need to specifically select white (bottom right of the custom colour grid) as the normal white doesn't seem to work. I'm using white for some columns and grey for others and the only problem I have is a couple of columns come up the other colour when you use addrow. Can anyone see any flaws in this approach?
Replied 17 January 2021, 11:16 pm ESTHi,
You can use conditional formatting to highlight 0 values, I don't see any issues with this approach.
>> the only problem I have is a couple of columns come up the other colour when you use addrow
Can you show this issue?
Although it's not applicable to your scenario, it's also possible to hide the 0 values at the sheet level using the 'DisplayZero' property:
fpSpread1.ActiveSheet.DisplayZero = false;