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

Posted by Andy Bailey at 2 October, 2007, 5:27 pm

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.

HTML:

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

PLAIN TEXT

HTML:

=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:

Loads better!

Popularity: 7% [?]

Cool little excel trick for showing simple bar graphs

How to extract names and email addresses from your WP database

My MYSQL cherry has been popped! rok on CommentLuv options site

Stumbled - Stumbleupon Widget for Wordpress 2.2

About

How to hide a .rar in a jpeg file

How to make a Wordpress plugin with less than 20 lines of code

Comments

Wissam Idrissi (1 comments.) October 3, 2007

I never used excel in my life lol .

Aschode October 3, 2007

thanks for the tips

Reztar (1 comments.) October 3, 2007

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

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

Andy Bailey October 3, 2007

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

Ashcode, Reztar ; you’re welcome!

Wilson (1 comments.) October 4, 2007

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

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

Andy Bailey October 4, 2007

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

Jalaj (20 comments.) October 12, 2007

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

Andy Bailey October 12, 2007

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

Lynne Kempton (3 comments.) February 25, 2008

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

Lynne Kempton (3 comments.) February 25, 2008

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

Anna June 9, 2008

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.

Andy Bailey June 9, 2008

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

Dan June 12, 2008

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?

Dan June 12, 2008

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?

Andy Bailey June 12, 2008

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!

Andy Bailey June 12, 2008

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

Dan June 12, 2008

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.

Andy Bailey June 12, 2008

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

Dan June 12, 2008

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

Dan June 12, 2008

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.

Dan June 16, 2008

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

Andy Bailey June 16, 2008

=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

Steve July 1, 2008

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?

Andy Bailey July 1, 2008

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

Steve July 2, 2008

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

Andy Bailey July 2, 2008

Marcus Eriksson August 17, 2008

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.

8 online now
the most online was 176