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.

Reconnaissance

Answer 1

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.

Tervalent

This works, but if the spreadsheet isn't read-only, would that not make people reading it wonder where the numbers (or lack thereof) are coming from? – Nectar

Answer 2

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

Cytology

Photo of result – Reconnaissance

Hi @Reconnaissance if this or any answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – Tervalent

This is a drawback if you want to plot a chart and ignore these values… they will always be considered as 0… :( – Fdic

Answer 3

=iferror(1/ (1/ H15+G16-F16 ), "")

this way avoids repeating the central calculation (which can often be much longer or more processor hungry than the one you have here...

enjoy

Natty

I have been thinking that there should be a way to construct something like this, but it did not hit me until I saw this post. Thank you. I have so many If(gibberish=0,"",gibberish) formulas that I now want to fix. I think it may work better as =iferror(1/ (1/ (H15+G16-F16) ), "") – Obloquy

Answer 4

Your question is missing most of the necessary information, so I'm going to make some assumptions:

1. Column H is your total summation
2. You're putting this formula into H16
3. Column G is additions to your summation
4. Column F is deductions from your summation
5. 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)<>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.
<> 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.

Ephod

Notably, it's not possible to actually leave a cell truly blank, a forumula must choose between 0, "", and NA(), none of which necessarily show up as "blank". Google around and you'll find Excel is notoriously bad about what "blank" means, e.g. ISBLANK() and COUNTBLANK() can produce different answers for the same cell. – Ephod

Answer 5

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.

Plasmodium

Answer 6

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/))

Lasser

Answer 7

Use this

=IFERROR((H15+G16-F16)^2/(H15+G16-F16),"")

Drifter

if it's zero, just divide by itself will return an error; if not, square it then divide itself will just return what it is. iferror will return the second value if the first one's error. This hopefully should be a bit faster – Drifter

Please add all explanation to your answer by editing it – Czardom