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

October
2

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!

    Business Software, Code


0 Comments zu “Excel tip: How to not show zero values for forumla result”

  1. Wissam Idrissi
    03.10.07 5:03 pm

    I never used excel in my life lol .

    #1

  2. Aschode
    03.10.07 5:05 pm

    thanks for the tips

    #2

  3. Reztar
    03.10.07 5:17 pm

    Very simple, yet.. i often forget how to use IF for my Excel. thanks mate.

    Reztar’s last blog post..Blogger in real life

    #3

  4. Andy Bailey
    03.10.07 5:26 pm

    wissam, you don’t know what you’re missing! hehe, spreadsheets are a bain of my life.

    Ashcode, Reztar ; you’re welcome!

    #4

  5. Wilson
    04.10.07 11:22 am

    Hey!…Great tutorial… Seems your online now…I’m using your plug-in… Great!… Thanks…

    Wilson’s last blog post..Always ask for coupons?

    #5

  6. Andy Bailey
    04.10.07 11:23 am

    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..

    #6

  7. Jalaj
    12.10.07 9:36 am

    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

    #7

  8. Andy Bailey
    12.10.07 11:03 am

    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

    #8

  9. Lynne Kempton
    25.02.08 3:12 pm

    Pretty damn good tips there. I like the way you took advantage of If statements there. I think I will do a video tutorial of this at http://www.learntouse.org and link back here if that’s ok with you.

    Lynne Kempton’s last blog post..Absolute & Relative Cell References

    #9

  10. Lynne Kempton
    25.02.08 3:13 pm

    Pretty damn good tips there. I like the way you took advantage of If statements there. I think I will do an Excel video tutorial of this at http://www.learntouse.org and link back here, if that’s ok with you.

    Lynne Kempton’s last blog post..Absolute & Relative Cell References

    #10

  11. Anna
    09.06.08 4:11 am

    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.

    #11

  12. Andy Bailey
    09.06.08 10:03 am

    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

    #12

  13. Dan
    12.06.08 3:54 pm

    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?

    #13

  14. Dan
    12.06.08 4:09 pm

    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?

    #14

  15. Andy Bailey
    12.06.08 4:21 pm

    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!

    #15

  16. Andy Bailey
    12.06.08 4:25 pm

    i must have replied same time as your update, try the OR formula . if you get stuck, let me know

    #16

  17. Dan
    12.06.08 6:38 pm

    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.

    #17

  18. Andy Bailey
    12.06.08 8:17 pm

    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

    #18

  19. Dan
    12.06.08 8:54 pm

    Wow! I wasn’t even in the ball park! Thank you!!! Bon Apetite!

    #19

  20. Dan
    12.06.08 10:16 pm

    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.

    #20

  21. Dan
    16.06.08 4:30 pm

    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!!

    #21

  22. Andy Bailey
    16.06.08 8:33 pm

    =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

    #22

  23. Steve
    01.07.08 6:03 pm

    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?

    #23

  24. Andy Bailey
    01.07.08 6:07 pm

    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

    #24

  25. Steve
    02.07.08 7:01 pm

    Hi Andy.
    Painful… I’ts always the obvious. Thanks Andy.

    #25

  26. Andy Bailey
    02.07.08 7:20 pm

    :-)

    #26

  27. Marcus Eriksson
    17.08.08 8:37 pm

    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.

    #27

  28. N Bibi
    14.09.08 12:03 pm

    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. ;)

    #28

  29. Andy Bailey
    14.09.08 12:49 pm

    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

    #29

  30. N Bibi
    15.09.08 6:13 pm

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

    #30

  31. Andy Bailey
    15.09.08 6:25 pm

    try typing it out by hand, if you copy and paste from here the quotes will be different to those that you use in excel

    #31

  32. TJ
    17.03.09 2:52 am

    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?

    #32

  33. Mike
    15.06.09 2:25 pm

    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?

    #33