Geek Answers Handbook

If "0" then leave the cell empty

I am trying to create a simple ledger and in the far right corner of the "Book" it contains any debit / credit that I entered. But instead of leaving unused lines blank, he continues to repeat the latest summary information at the bottom of the page.

How can I make this cell empty if the equation is 0?

=H15+G16-F16

is the formula that I am currently using.

+6
source share
5 answers

You can change the format of the column number in this custom format:

 0;-0;;@

which will hide all values ​​0.

To do this, select a column, right-click> Cell Format> Custom.

+16
source

Use =IF(H15+G16-F16=0,"",H15+G16-F16)

+6
source

Zero should be noted in the accrual book, even if it is a hyphen displayed in the format of the accounting style number. However, if you want to leave the string empty when there are no values ​​to calculate, use a formula similar to the following,

  =IF(COUNT(F16:G16), SUM(G16, INDEX(H$1:H15, MATCH(1e99, H$1:H15)), -F16), "")

This formula is a bit complicated because you seem to have provided your sample formula somewhere down in the row record in a column without displaying any layouts or sample data. The formula I provided should be placed in H16 and then copied or filled in elsewhere in column H, but I cannot guarantee any guarantees without seeing the layout.

If you post some sample data or a public link to a screenshot showing your data layout, more specific help may be offered. http://imgur.com/ is a good place to post a screenshot, and most likely someone with a greater reputation is inserting an image into your question for you.

+1
source

An example of an IF statement that you can use to add a calculation to the cell that you want to hide if value = 0, but is displayed on another link to the cell value.

= IF (/ Your control cell / = 0, "", SUM (/ Here you put your SUM /))

0
source

Your question is missing most of the necessary information, so I will make some assumptions:

  • Column H - Your Total Sum
  • You put this formula in H16
  • Column G is in addition to your total.
  • Column F is the deductions from your total
  • Do you want to leave the summation cell empty if no debit or credit is entered

The answer will be as follows:

 =IF(COUNTBLANK(F16:G16)<>2,H15+G16-F16,"")

COUNTBLANK indicates how many cells are full or set to "".
IF allows you to conditionally perform one of two actions, based on whether the first statement is true or false. The second argument, separated by a comma, is what should be done if it is true, the third argument, separated by a comma, is what should be done if it is false.
<> means not equal.

The equation says that if the number of empty cells in the range of F16:G16 (your credit and debit cells) is not 2, it means that both values ​​are not empty, and then calculate the equation that you indicated in your question. Otherwise, the cell must be empty ( "" ).
When you copy this equation to new cells in column H other than H16 , it updates the row references to see the correct rows for credit and debit amounts.

CAVEAT: This equation is useful if you simply add entries for credits and debits to the end of the list and want the current total amount to be updated automatically. You fill this equation to some arbitrary long length and end the actual data. You would not see the current amount for the end of the credit / debit entries, then it would be empty until you filled out a new credit / debit entry. If you left an empty line in your credit debit entries, a link to the previous total amount of H15 would indicate an empty value, which in this case is treated as 0.

0
source

More articles:


All Articles