 |
 | 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 / Worksheet Functions / June 2006IF'S Blah
=IF(ISERROR(F16-K16),"",IF(SUM(F16-K16>0),"",SUM(F16-K16))) Cells F16 & K16 can either contain a "Null String" ("") or a number created from another IF statement , from these 2 Cells I need another calculation, if the error "#Value" is made I want it to be blank, If the number is >0, I want it to be blank, but if it's a number I want it to appear. Any help is greatly appreciated Hi phuser, Think you nearly had it. Should be =IF(ISERROR(F16-K16),"",IF(SUM(F16-K16<=0),"",SUM(F16-K16))) < = less than. I've added = so it also shows as blank. Thanks VBA Noob  SignatureVBA Noob Hi phuser, Think you nearly had it. Should be =IF(ISERROR(F16-K16),"",IF(SUM(F16-K16<=0),"",SUM(F16-K16))) < = less than. I've added = so it also shows as blank. Thanks VBA Noob SignatureVBA Noob Hi phuser, Think you nearly had it. Should be =IF(ISERROR(F16-K16),"",IF(SUM(F16-K16<=0),"",SUM(F16-K16))) < = less than. I've added = so it also shows as blank. Thanks VBA Noob SignatureVBA Noob Nel post news:129is539t60l35c@corp.supernews.com *phuser* ha scritto: > =IF(ISERROR(F16-K16),"",IF(SUM(F16-K16>0),"",SUM(F16-K16))) > [quoted text clipped - 4 lines] > it to appear. > Any help is greatly appreciated I don't know I have understood what you mean... Try with this one: =IF(and(F16="",K16=""),"",IF((F16-K16)>0,"",F16-K16)) Signature(I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Ciao Franz Verga from Italy Franz I'm just trying to learn by following this group. You all have been so much help already. Could you please explain why this formula does not work as a solution for the the OP's problem =IF(OR(ISERROR(F16-K16),((F16-K16)<0)),"",F16-K16) Dave > Nel post news:129is539t60l35c@corp.supernews.com > *phuser* ha scritto: [quoted text clipped - 13 lines] > > =IF(and(F16="",K16=""),"",IF((F16-K16)>0,"",F16-K16)) Nel post news:%23$RqnMVlGHA.4468@TK2MSFTNGP05.phx.gbl *Dave* ha scritto: > Franz > [quoted text clipped - 5 lines] > > Dave I think because he wants to see a null string ("") also if (F16-K16)>0, so you could modify your formula in this way: =IF(OR(ISERROR(F16-K16),((F16-K16)>0)),"",F16-K16) Signature(I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Ciao Franz Verga from Italy Franz You are correct I used less than 0 instead of greater than 0 but if text is entered in either cell F16 or cell K16 it should generate an error an therefore display a null string but it does not it displays #value. What am I missing? Dave > Nel post news:%23$RqnMVlGHA.4468@TK2MSFTNGP05.phx.gbl > *Dave* ha scritto: [quoted text clipped - 13 lines] > > =IF(OR(ISERROR(F16-K16),((F16-K16)>0)),"",F16-K16) Well Guys, I so much appreciate all the help, but something is missing when I try, VBA Noob's only worked if the both cells were blank or both contained numbers but if 1 of the cells contained a whole number and 1 was blank it returned a blank cell. Need it to be like 1-0=1 right, not 1-0=0 Franz formula returned a #Value, when both cells were blank as did David's > Franz > [quoted text clipped - 23 lines] >> >> =IF(and(F16="",K16=""),"",IF((F16-K16)>0,"",F16-K16)) Nel post news:129iv2p39nkfr7f@corp.supernews.com *phuser* ha scritto: > Well Guys, I so much appreciate all the help, but something is > missing when I try, VBA Noob's only worked if the both cells were [quoted text clipped - 3 lines] > cells were blank as did > David's Maybe it should be better if could explain again, in a clearer way, what are the expected results and the conditions... Signature(I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Ciao Franz Verga from Italy Well I may have sabatoged this thread from the beginning I wanted less than not greater than, and have put that in but the results are the same. VBA Noob got it right, it's like he knew what I wanted before I did ;-) Ok I'll try to explain it best I can F7 = IF('062306'!L20>0,('062306'!L20),"") returns a value of (e.g. 6) or "" K7 = IF(SUM(I20/D19)>0,SUM(I20/D19),"") also returns value of (whole number) or "" G7 contains the calculation of those 2 cells, if 1 of the values in F7 or K7 is blank the value will be returned #Value, so Im trying to eliminate that error by putting in the ISERROR statement, but I also want it to be blank if less than 0 amount is calculated, in the end, all I want to see in G7 is a whole number, anything else should return a blank cell. > =IF(ISERROR(F16-K16),"",IF(SUM(F16-K16>0),"",SUM(F16-K16))) > [quoted text clipped - 5 lines] > > Any help is greatly appreciated Nel post news:129j0lfnafqvd0d@corp.supernews.com *phuser* ha scritto: > Well I may have sabatoged this thread from the beginning I wanted > less than not greater than, and have put that in [quoted text clipped - 12 lines] > calculated, in the end, all I want to see in G7 is a whole number, > anything else should return a blank cell. So now, maybe (maybe) I have understood... Try this one, fitting the ranges: =IF(OR(F16="",K16=""),"",IF((F16-K16)<=0,"",F16-K16)) or also =IF(OR(OR(F16="",K16=""),(F16-K16)<=0),"",F16-K16) Signature(I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Ciao Franz Verga from Italy Something just isnt adding up right, if there is a number in F7 and K7 is blank, G7 still returns a blank cell, I dont get it. > Nel post news:129j0lfnafqvd0d@corp.supernews.com > *phuser* ha scritto: [quoted text clipped - 25 lines] > > =IF(OR(OR(F16="",K16=""),(F16-K16)<=0),"",F16-K16) Nel post news:129j46gi50gq797@corp.supernews.com *phuser* ha scritto: [cut] >>> G7 contains the calculation of those 2 cells, if 1 of the values in >>> F7 or K7 is blank the value will be returned #Value, [quoted text clipped - 12 lines] >> >> =IF(OR(OR(F16="",K16=""),(F16-K16)<=0),"",F16-K16) > Something just isnt adding up right, if there is a number in F7 and > K7 is blank, G7 still returns a blank cell, I dont get it. Let me understand... In the above formulas, F16 stands for your real F7 and K16 for your real K7? Which one of the two formulas did you try? I wrote the formulas directly in OE, without trying, but now I tried and the first, i.e.: =IF(OR(F16="",K16=""),"",IF((F16-K16)<=0,"",F16-K16)) works for me... Maybe you can post an example file on www.savefile.com Signature(I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Ciao Franz Verga from Italy http://www.savefile.com/files/8774554 Once you open the file you will see I created some hidden columns with a value to get the formula to work, I would most like to get rid of those. BTW G16 is using exactly the same formula, it had a value in the "WA" column but nothing in the "In" so I started using that one, instead of the 1st row which is G7 > Nel post news:129j46gi50gq797@corp.supernews.com > *phuser* ha scritto: [quoted text clipped - 36 lines] > > Maybe you can post an example file on www.savefile.com |
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
|
©2009 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.