If 0 then leave the cell blank

Question

I am trying to create a simple ledger and on the far right of the  Book  it totals any debit credit that I input  But instead of leaving the unused rows blank  it keeps repeating the latest total to the bottom of the page    How Can I make that cell blank if the equation equals 0     H15 G16-F16    is the formula I am currently using

User · Answer

You can change the number format of the column to this custom format   0 -0      which will hide all 0 values   To do this  select the column  right-click   Format Cells   Custom

User · Answer

Use this   IFERROR  H15 G16-F16  2  H15 G16-F16

User · Answer

An example of an IF Statement that can be used to add a calculation into the cell you wish to hide if value   0 but displayed upon another cell value reference    IF  Your reference cell  0    SUM  Here you put your SUM

User · Answer

iferror 1   1  H15 G16-F16     quot  quot   this way avoids repeating the central calculation  which can often be much longer or more processor hungry than the one you have here    enjoy

User · Answer

Use  IF H15 G16-F16 0    H15 G16-F16

User · Answer

An accrual ledger should note zeroes  even if that is the hyphen displayed with an Accounting style number format  However  if you want to leave the line blank when there are no values to calculate use a formula like the following     IF COUNT F16 G16   SUM G16  INDEX H 1 H15  MATCH 1e99  H 1 H15    -F16         That formula is a little tricky because you seem to have provided your sample formula from somewhere down into the entries of the ledger s item rows without showing any layout or sample data  The formula I provided should be able to be put into H16 and then copied or filled to other locations in column H but I offer no guarantees without seeing the layout   If you post some sample data or a publicly available link to a screenshot showing your data layout more specific assistance could be offered  http   imgur com  is a good place to host a screenshot and it is likely that someone with more reputation will insert the image into your question for you

User · Answer

Your question is missing most of the necessary information  so I m going to make some assumptions    Column H is your total summation You re putting this formula into H16 Column G is additions to your summation Column F is deductions from your summation You want to leave the summation cell blank if there isn t a debit or credit entered   The answer would be    IF COUNTBLANK F16 G16  lt  gt 2 H15 G16-F16       COUNTBLANK tells you how many cells are unfilled or set to       IF lets you conditionally do one of two things based on whether the first statement is true or false   The second comma separated argument is what to do if it s true  the third comma separated argument is what to do if it s false   lt  gt  means  not equal to    The equation says that if the number of blank cells in the range F16 G16  your credit and debit cells  is not 2  which means both aren t blank  then calculate the equation you provided in your question   Otherwise set the cell to blank      When you copy this equation to new cells in column H other than H16  it will update the row references so the proper rows for the credit and debit amounts are looked at    CAVEAT  This equation is useful if you are just adding entries for credits and debits to the end of a list and want the running total to update automatically   You d fill this equation down to some arbitrary long length well past the end of actual data   You wouldn t see the running total past the end of the credit debit entries then  it would just be blank until you filled in a new credit debit entry   If you left a blank row in your credit debit entries though  the reference to the previous total  H15  would report blank  which is treated like a 0 in this case

[excel] If "0" then leave the cell blank

Examples related to excel

Examples related to if-statement