NeoGen   10 #1 Posted August 10, 2008 I'm just uing the simple =SUM(E22*F22) equation, but I want it left blank if nothing is entered in to the E22 and F22 boxes, anyone got a suggestion?  Many thanks  N. Share this post Link to post Share on other sites Share this content via...
fnkysknky   10 #2 Posted August 10, 2008 Not got access to Excel at the moment but I'm sure there's an IsEmpty or IsBlank (there's a few IsXxx functions) function that could be used in IF statements... Share this post Link to post Share on other sites Share this content via...
NeoGen   10 #3 Posted August 10, 2008 I've looked at all the formulas and nothing is jumping out to me that will allow a blank cell if the result is 0. Share this post Link to post Share on other sites Share this content via...
fnkysknky   10 #4 Posted August 10, 2008 What about  =IF((AND(ISBLANK(E22), ISBLANK(F22)), SUM(E22*F22), "")  I don't actually have a way of checking it at the moment so there may be a syntax error somewhere... Share this post Link to post Share on other sites Share this content via...
cgksheff   44 #5 Posted August 10, 2008 From top menu:  >Tools > Option > View > Uncheck "Zero values" Share this post Link to post Share on other sites Share this content via...
max   13 #6 Posted August 10, 2008 Pretty much there  =IF(OR(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))  will give a blank if either e22 or f22 is blank  =IF(AND(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))  will give a blank if both e22 and f22 is blank but a zero if one or other has a value Share this post Link to post Share on other sites Share this content via...
fnkysknky   10 #7 Posted August 10, 2008 Oops, wasn't thinking properly when I wrote that it would seem Share this post Link to post Share on other sites Share this content via...
NeoGen   10 #8 Posted August 10, 2008 Thanks guys for your input, what I did in the end was:  Go to 'Format'  Then 'Conditional Formatting'  In Condition 1 I selected 'Cell Value Is'  Then set it to 'Equal To'  And entered '0'  Then clicked on format and set the font colour to white and clicked on 'OK then 'OK'  Might not be the best way but it does exactly what I want, when there is a 0 it does not show anything.  Thanks  N Share this post Link to post Share on other sites Share this content via...
cgksheff   44 #9 Posted August 10, 2008 What's wrong with my way?  Too simple? Share this post Link to post Share on other sites Share this content via...
NeoGen   10 #10 Posted August 10, 2008 What's wrong with my way? Too simple?  Not sure if it's because I'm using the latest version on a Mac, but if I go to 'Tools' there is no 'Options' there, so I got tripped at the first hurdle. Share this post Link to post Share on other sites Share this content via...
cgksheff   44 #11 Posted August 10, 2008 I'm still on 2003!  Excel 2007 advice is here:  http://office.microsoft.com/en-us/excel/HP100704611033.aspx Share this post Link to post Share on other sites Share this content via...
barleycorn   10 #12 Posted August 16, 2013 I'm just uing the simple =SUM(E22*F22) equation, but I want it left blank if nothing is entered in to the E22 and F22 boxes, anyone got a suggestion? Many thanks  N. A colleague has just asked me a very similar question. After playing about with IF functions I just Googled it and this was top hit... Pretty much there =IF(OR(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))  will give a blank if either e22 or f22 is blank  =IF(AND(ISBLANK(E22),ISBLANK(F22)),"",SUM(E22*F22))  will give a blank if both e22 and f22 is blank but a zero if one or other has a value  ... and this works perfectly, thank you. Saved me the hassle of working it out myself.  jb Share this post Link to post Share on other sites Share this content via...