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),”")[/html]
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..
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.
[html]=IF(logical test,if true,if not true)[/html]
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!
October 3rd, 2007 on 5:03 pm
I never used excel in my life lol .
October 3rd, 2007 on 5:05 pm
thanks for the tips
October 3rd, 2007 on 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
October 3rd, 2007 on 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!
October 4th, 2007 on 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?
October 4th, 2007 on 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..
October 12th, 2007 on 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
October 12th, 2007 on 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
February 25th, 2008 on 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
February 25th, 2008 on 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
June 9th, 2008 on 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.
June 9th, 2008 on 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
June 12th, 2008 on 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?
June 12th, 2008 on 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?
June 12th, 2008 on 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!
June 12th, 2008 on 4:25 pm
i must have replied same time as your update, try the OR formula . if you get stuck, let me know
June 12th, 2008 on 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.
June 12th, 2008 on 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
June 12th, 2008 on 8:54 pm
Wow! I wasn’t even in the ball park! Thank you!!! Bon Apetite!
June 12th, 2008 on 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.
June 16th, 2008 on 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!!
June 16th, 2008 on 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
July 1st, 2008 on 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?
July 1st, 2008 on 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
July 2nd, 2008 on 7:01 pm
Hi Andy.
Painful… I’ts always the obvious. Thanks Andy.
July 2nd, 2008 on 7:20 pm
August 17th, 2008 on 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.
September 14th, 2008 on 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.
September 14th, 2008 on 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
September 15th, 2008 on 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?
September 15th, 2008 on 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
March 17th, 2009 on 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?
June 15th, 2009 on 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?