 |
 | 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 / March 2008HELP with crazy formula
Hi Guys, I’m trying to create a formula that would give different outcomes to determine if a product was new or not. I’m new to Excel, so I’m not sure how to go about it. Can anyone help me please? This is what I’m trying to figure out. If A2= 0, B2=0, but C2>0 --- Give me NEW If B2= 0, C2>0, and D2>0 --- Give me 2NEW If C2> 0, D2>0 and E2>0 --- Give me 3NEW But if C2> 0, D2= 0, E2>0 --- Give me 3NEW still And if anything was sold OR not after those 3 years (C2,D2,E2)… Give me BASE. If you can solve this… THANK YOU! In advance…lol.. Yeidin I dont understand the last condition in which you want BASE. for first 4... =IF(AND(A2=0,B2=0,C2>0),"NEW",IF(AND(B2=0,C2>0,D2>0),"2NEW",IF(OR(AND(C2>0,D2>0,E2>0),AND(C2>0,D2=0,E2>0)),"3NEW",""))) > Hi Guys, > I'm trying to create a formula that would give different outcomes to [quoted text clipped - 14 lines] > > Yeidin IF(AND(B2=0,C2>0,D2>0),"2NEW", is not actioned because: AND(A2=0,B2=0,C2>0), is satisfied by C2>0 Better to reverse the order: =IF(OR(AND(C2>0,D2>0,E2>0),AND(C2>0,D2=0,E2>0)),"3NEW",IF(AND(B2=0,C2>0,D2>0),"2NEW",IF(AND(A2=0,B2=0,C2>0),"NEW","")))  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I dont understand the last condition in which you want BASE. > [quoted text clipped - 20 lines] >> >> Yeidin Hi Gaurav, Thank you so much for your help. When I speak of anything after C2,D2,and E2.... Meaning... F2:Z2... I'm looking to see if it that product had any other sales during that year so I can call it BASE. Does that help? > I dont understand the last condition in which you want BASE. > [quoted text clipped - 20 lines] > > > > Yeidin When you say: > If A2= 0, B2=0, but C2>0 do you mean that A2 would have an actual zero in it or that A2 would be empty? And does: > And if anything was sold OR not after those 3 years (C2,D2,E2). Give me > BASE. mean that C2, D2 & E2 are dates? SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Hi Guys, > I'm trying to create a formula that would give different outcomes to [quoted text clipped - 14 lines] > > Yeidin Thank you! You save me sooooo much time. When I say A2 and B2=0 but C2>0, I just mean that C2 had sales that year but in the previous years it didnt. So I'm looking at it from a sales point of view. Now, for any sales after C2,D2,and E2.... Meaning... F2:Z2... I'm looking to see if it that product had any other sales during that year so I can call it BASE. Does that help? > When you say: > [quoted text clipped - 26 lines] > > > > Yeidin I have been waiting for Gaurav to reply because it is really his formula not mine but assuming that if you have any sals in A2:E2 plus sales later on try: =IF(AND(COUNT(C2:E2)>0,COUNT(F2:Z2)>0),"Base",IF((AND(C2>0,E2>0)),"3 New",IF(AND(B2=0,C2>0,D2>0),"2 New",IF(AND(A2=0,B2=0,C2>0),"New")))) SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Thank you! You save me sooooo much time. > [quoted text clipped - 40 lines] >> > >> > Yeidin Thanks Sandy. I just got a little tied up. and moreover, it is an 'Excel' formula. :) >I have been waiting for Gaurav to reply because it is really his formula >not mine but assuming that if you have any sals in A2:E2 plus sales later [quoted text clipped - 50 lines] >>> > >>> > Yeidin I always think that it is a bit impertinent to alter other people's formulas so I usually try not to. I dropped the double check, (the OR), in the *3 New* test: OR(AND(C2>0,D2>0,E2>0),AND(C2>0,D2=0,E2>0)) because D2 *must* either be 0 or >0 so the test resolves down to if both C2 & E2 are >0 SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > Thanks Sandy. I just got a little tied up. > [quoted text clipped - 54 lines] >>>> > >>>> > Yeidin Great observation. :-) >I always think that it is a bit impertinent to alter other people's >formulas so I usually try not to. [quoted text clipped - 65 lines] >>>>> > >>>>> > Yeidin |
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.