 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008reading a blank cell as zero
I'm a first time poster so please be patient if I don't give enough detail. I'm trying to put an if stmt in one cell and conditionally format that cell. My current formula is =IF(D10>=0,D10,NA()). But when D10 is blank it puts a zero in E10 and thus my conditional format colors the cell. What I want is #NA to appear in E10 which blocks the conditional formatting. This seems easy enough but won’t work. I've tried different formulas with no luck. Thanks for any help. Dana If you want the text "#NA" to appear in cell E10, try this modification to your formula =IF(D10>=0,D10,"#NA"). hth > I'm a first time poster so please be patient if I don't give enough detail. > I'm trying to put an if stmt in one cell and conditionally format that cell. [quoted text clipped - 4 lines] > Thanks for any help. > Dana Try =IF(AND(LEN(D10)>0,D10>=0),D10,NA()) Hope this helps, Hutch > I'm a first time poster so please be patient if I don't give enough detail. > I'm trying to put an if stmt in one cell and conditionally format that cell. [quoted text clipped - 4 lines] > Thanks for any help. > Dana Hutch - you're my new hero. Works perfect. However, new problem. I have an autosum of column E which is now giving me #NA unless all cells are filled. Do you know of another formula that reads only cells with a value of zero or greater? I'm trying =IF(E6:E22>0,SUM(E6:E22)) but getting #VALUE! > Try > [quoted text clipped - 12 lines] > > Thanks for any help. > > Dana Your other formula may cause some cells in column E to contain #N/A. That will interfere with any SUM function unless we work around it. The other thing is, to test a range of cells with an IF function you have to enter it as an array formula. I think the following array formula will work: =SUM(IF(ISERROR(E6:E22),0,E6:E22)) This is an array formula. Hit Ctrl-Shift-Enter instead of Enter. If you do it correctly, Excel will wrap curly brackets { } around your formula (don't add them yourself.) You can copy/move/paste array formulas like regular formulas, but if you edit it, you always have to press Ctrl-Shift-Enter instead of Enter. Hutch > Hutch - you're my new hero. Works perfect. However, new problem. I have an > autosum of column E which is now giving me #NA unless all cells are filled. [quoted text clipped - 18 lines] > > > Thanks for any help. > > > Dana Hutch - once again it works perfectly! thank you so much for your time and help. Dana > I'm a first time poster so please be patient if I don't give enough detail. > I'm trying to put an if stmt in one cell and conditionally format that cell. [quoted text clipped - 4 lines] > Thanks for any help. > Dana |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.