Excel tip: How to not show zero values for forumla result

Here’s a handy little formula to add up a column or row of figures and only show the result if it’s a non-zero.

=IF(SUM(D4:E4)>=1,SUM(D4:E4),"")

I find it hard to pick out positive numbers in a whole list of values if there are a lot of zero numbers. Look at the two following examples..

excel grab 1

if there are a whole lot of numbers in use like when you’re looking at a stock list that goes on for pages and you’re looking for what is left amongst a lot of zero quantity items, it’s really easy to miss the numbers. But, by using the IF function of Excel you can test the normal formula and only output a result for whatever value you want. ie.

=IF(logical test,if true,if not true)

the example below uses this

  • logical test : = SUM(D4:E4)>=1
    the sum of the numbers between D4 and E4 is greater than equal to 1
  • if true: sum(D4:E4)
    if the logical test is true then output the sum of D4 to E4
  • if not true: “”
    output nothing, you could use whatever you want for the “if true” and “if not true”. Text would go in quotes and formulas go without.

    Here’s what it looks like:
    excel grab 2

    Loads better!

0 thoughts on “Excel tip: How to not show zero values for forumla result

  1. Andy Bailey

    I just happened to see your click through to here from your plugins page. Awesome wordless wednesday photo you have on your blog!

    thanks for visiting and using CommentLuv, let me know if there’s any problems with it..

     
  2. Jalaj

    You can also use another way… Menubar–> Tools –> Options –> “View” page –> Under Window Options uncheck “Zero Values”… however effect would be global (within the workbook, all cells with value 0 wuld be shown blank)

    Jalaj’s last blog post..Drive Slowly : Google Ahead

     
  3. Andy Bailey

    great tip jalaj, thanks for that! it’s a good way to declare it throughout the whole sheet. do you know if this is saved with the worksheet or will another user have to set it also so they don’t see the zeros?

    Andy Bailey’s last blog post..New Blog at FiddyP

     
  4. Anna

    Hi, I used your ‘if’ statement to hide zeros in column ‘E’ but another column has an equation that is dependant on the value in column ‘E’. Now that column shows #Value! where before it would have showed a zero. Is there anyway around this?

    Thanks for your help.

     
  5. Andy Bailey

    Anna: you could try putting an IF statement in the cell that shows #Value!

    ie. =IF(E1=””,”nothing there”,SUM(E1*2))

    if no value is there it will display “nothing here” in the cell you put the formula, if there is a value in E1 then it will multiply it by 2

     
  6. Dan

    I am trying to get #DIV/O! to show a blank cell if the formula returns a zero. The formula i’m entering into R6 is =SUM(Q6/O6) and right now q6 has =SUM(P6-O6)in it and o6 is blank. how do i make both Q column and R return blank cells if the value in each is zero?

     
  7. Dan

    Ok. update: i got Column Q to display blank cells if the formula returned a zero value. But when i try to use the same type of if statement for Column R, i get #value!.

    Help please. currently column Q has the formula =IF(P6-O6=0,””,SUM(P6-O6)) in it. I tried to enter =IF(Q6/O6=0,”0″,SUM(Q6/O6)) but it’s bringing back #value!. how do i solve this with a blank cell if the quotient is zero? and with the quotient if it is anything other than zero?

     
  8. Andy Bailey

    for q6 put =if(o6=0,””,SUM(P6-O6))
    that will only try the formula if o6 has a value other than zero, else it will blank

    that will give you an error on R6 if one of them has no value so you can try an OR statement for R6
    =IF(OR(Q6=””,O6=””),””,SUM(Q6/O6))

    this says if q6 OR o6 have nothing then put nothing, else do the sum.

    if you want to safegaurd q6 you could put
    =IF(SUM(P6-O6)=0,””,SUM(P6-O6))
    that says as long as the sum of p6-o6 is zero don’t put anything

    hope this helps!

     
  9. Dan

    ok i have a tough one if you don’t mind. Column A has the date i entered a trade, say 2 days ago. Column B can either be blank or have the same date, or any day after that. I need a column that has the total # of days in the trade, or else blank. If the dates in column A and B are both the same, that’s zero days in the trade and needs to show zero. But if column B is empty, the total # of days column needs to be empty. and of course, if column B – column A is 1 or more days then the totals column needs to reflect that. Is this possible???

    Thanks!

    PS – I sent you an email with a copy of the spreadsheet i’m working on.

     
  10. Andy Bailey

    I sent you the spreadsheet back, for anyone else that’s watching.. use nested IF with AND

    =IF(AND(A2<>“”,B2<>“”),IF(SUM(B2-A2)=0,”0″,SUM(B2-A2)),””)
    check a2 and b2 for values
    if they both have values – check if value is zero and put 0 if it is or otherwise show the difference
    if either one is blank – don’t display anything

     
  11. Dan

    I had to change the formula after you sent it back because what i asked for fell short of what i needed. See if i enter a trade today and exit today, it’s considered one day. So i added +1 and this was the formula in the end: =IF(AND(A2″”,B2″”),IF(SUM(B2-A2)=0,”1″,SUM((B2-A2)+1)),””).

    But i still have a little trouble with the little green box to the lower left. I was able to accomplish most of it, but i’m having trouble figuring out how to perform # of losers. I cannot explain it here in a fashion that would make any sense, so i saved my changes and forwarded it to you again by email. You’ve been great! I hope you don’t mind.

     
  12. Dan

    Hi Andy, me again

    There are a few issues that have arisen. Are you still willing to help me with them, or have I exceeded my quota of requests?

    I am forwarding to you the most current copy of the worksheet. I worked all weekend long trying to resolve a couple of them, but i could not. One i think the only resolution is to enter the data manually. But I’ll put the question to you just in case you know a way it can be done.

    It is: L3 which is Total Days. The formula we are using is giving us an incorrect answer. Not to mention, i was looking for the answer to the wrong question besides. Total Days is supposed to tell us what number “Trading Day” of the Trade (Calendar) year the current day is. There are approximately 250 trading days in the year. Since January 1st, what trading day is the current day (today). The formula =Sum(u5:u3218) we currently have in L3 adds up the totals from the column “No of days in a trade”. It is an attempt to answer the wrong question, and it answers the wrong question incorrectly. My bad!

    Can you figure out how to come to a correct answer of what formula to use to get the result I am looking for? Or should we just resolve to entering it manually each day?

    The OTHER issue I worked on the most this weekend. The thing is, i think i’m using the correct formula. I searched and searched the internet and everything seemed to keep saying the same thing. Use the formula i currently have. But it is bringing back an incorrect answer. It is Cell G3, # of Winners. I counted manually and the answer should be 82, but it keeps saying 217. The formula entered is =COUNTIF(T5:T2318,”>=0″) which should be adding only the positive %s in the column. But it’s pulling more than just the positives from somewhere and i cannot figure out why, or how to resolve it? I believe Winning %age to be incorrect also, but should correct itself when # of Winners is corrected.

    Can you please help me with these two issues?

    Thanks for your consideration and all you’ve done so far!!

     
  13. Andy Bailey

    =NETWORKDAYS(“1/1/2008″,P2) will give amount of business days between 1/1/8 and the date in p2 (date updated)

    =COUNTIF(T5:T312,”>0″) will give number of winners, before you were counting zero values too

     
  14. Steve

    I want to use the result of a formula in another formula field.
    i.e.,
    Cell B3 -> =IF(ISNA(MATCH(C3,’Dates’!B$3:B$27,0)),,”8″)
    Cell B9 -> =SUM(B3:B8)

    Cell B9, returns no value because it’s reading the formula of B3 instead of the resulting value. How do I get it to read the resulting value?

     
  15. Andy Bailey

    if the contents of b3 are showing 8 then b9 will see that as a text value and not a number so it wont be able to do sum on it.

    for an if statment, it needs to output a numeric value if you want to use the contents as values in other formulas

     
  16. Marcus Eriksson

    Another, perhaps more useful way, is to use custom formatting (right click, choose Format Cell…). If you input for example 0;[Red]-0; you will get black positive number, red negative and nothing if it is zero.

     
  17. N Bibi

    Hi Andy

    I think you’ve actually tackled my question within the thread but I’m not quite sure how to extrapolate your answer into my sheet… so a little help please ;)

    I have a simple spreadsheet which I am updating on a week to week basis.
    In column B I have a target, column C, the actual value (this is the column I update) and column D contains a formula to work out the deficit. The problem is that whilst Column C is empty, the formula in column D takes the C values as zero and produces an answer. What I would like, if it is at all possible, is to work out how to ignore row C if it contains no value (to give no result in column D) and only use the deficit formula if there are values in C. I have similar cells across the spreadsheet and would really appreciate an understanding of what it is that each part of your equation actually means so that I can translate it into other cells too.

    Thank you so much in advance. ;)

     
  18. Andy Bailey

    n bibi: just use an if statement in column d:
    =IF(c1=””,””,sum(b1-c1))

    this says if column C is empty dont do anything, if there is something there then do the formula to work out the result

     
  19. N Bibi

    Hey again Andy
    Tried your formula out but Excel doesn’t seem to like the quotation marks at all.
    Any alternative methods?

     
  20. TJ

    Lets say I want to multiply a cell by 10% and if the value is nil I want to keep the cell blank, how do I do that?

     
  21. Mike

    Thanks for the solution, same found elsewhere as well. BUT, I have a total calculated at the bottom of the column (in which the zeros are excluded by the if) and this just hands out VALUE! errors even if some of the cells are non zero, it just doesn’t like them. total formula is =SUM(E32+E34:E40)+(E33*2)/10
    Any ideas?