Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
Hi Guys,
Im trying to create a formula that would give different outcomes to determine if a product was new or not. Im new to Excel, so Im not sure how to go about it. Can anyone help me please? This is what Im trying to figure out. If A2= 0, B2=0, but C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
I dont understand the last condition in which you want BASE.
for first 4... =IF(AND(A2=0,B2=0,C20),"NEW",IF(AND(B2=0,C20,D2 0),"2NEW",IF(OR(AND(C20,D20,E20),AND(C20,D2=0, E20)),"3NEW",""))) "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
IF(AND(B2=0,C20,D20),"2NEW",
is not actioned because: AND(A2=0,B2=0,C20), is satisfied by C20 Better to reverse the order: =IF(OR(AND(C20,D20,E20),AND(C20,D2=0,E20)),"3 NEW",IF(AND(B2=0,C20,D20),"2NEW",IF(AND(A2=0,B2= 0,C20),"NEW",""))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... I dont understand the last condition in which you want BASE. for first 4... =IF(AND(A2=0,B2=0,C20),"NEW",IF(AND(B2=0,C20,D2 0),"2NEW",IF(OR(AND(C20,D20,E20),AND(C20,D2=0, E20)),"3NEW",""))) "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
Thank you! You save me sooooo much time.
When I say A2 and B2=0 but C20, 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? "Sandy Mann" wrote: When you say: If A2= 0, B2=0, but C20 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? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
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? "Gaurav" wrote: I dont understand the last condition in which you want BASE. for first 4... =IF(AND(A2=0,B2=0,C20),"NEW",IF(AND(B2=0,C20,D2 0),"2NEW",IF(OR(AND(C20,D20,E20),AND(C20,D2=0, E20)),"3NEW",""))) "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy 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 on try: =IF(AND(COUNT(C2:E2)0,COUNT(F2:Z2)0),"Base",IF(( AND(C20,E20)),"3 New",IF(AND(B2=0,C20,D20),"2 New",IF(AND(A2=0,B2=0,C20),"New")))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... Thank you! You save me sooooo much time. When I say A2 and B2=0 but C20, 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? "Sandy Mann" wrote: When you say: If A2= 0, B2=0, but C20 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? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
Thanks Sandy. I just got a little tied up.
and moreover, it is an 'Excel' formula. :) "Sandy Mann" wrote in message ... 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(C20,E20)),"3 New",IF(AND(B2=0,C20,D20),"2 New",IF(AND(A2=0,B2=0,C20),"New")))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... Thank you! You save me sooooo much time. When I say A2 and B2=0 but C20, 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? "Sandy Mann" wrote: When you say: If A2= 0, B2=0, but C20 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? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
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(C20,D20,E20),AND(C20,D2=0,E20)) because D2 *must* either be 0 or 0 so the test resolves down to if both C2 & E2 are 0 -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Thanks Sandy. I just got a little tied up. and moreover, it is an 'Excel' formula. :) "Sandy Mann" wrote in message ... 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(C20,E20)),"3 New",IF(AND(B2=0,C20,D20),"2 New",IF(AND(A2=0,B2=0,C20),"New")))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... Thank you! You save me sooooo much time. When I say A2 and B2=0 but C20, 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? "Sandy Mann" wrote: When you say: If A2= 0, B2=0, but C20 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? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP with crazy formula
Great observation. :-)
"Sandy Mann" wrote in message ... 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(C20,D20,E20),AND(C20,D2=0,E20)) because D2 *must* either be 0 or 0 so the test resolves down to if both C2 & E2 are 0 -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Thanks Sandy. I just got a little tied up. and moreover, it is an 'Excel' formula. :) "Sandy Mann" wrote in message ... 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(C20,E20)),"3 New",IF(AND(B2=0,C20,D20),"2 New",IF(AND(A2=0,B2=0,C20),"New")))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... Thank you! You save me sooooo much time. When I say A2 and B2=0 but C20, 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? "Sandy Mann" wrote: When you say: If A2= 0, B2=0, but C20 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? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "YEIDIN" wrote in message ... 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 C20 --- Give me NEW If B2= 0, C20, and D20 --- Give me 2NEW If C2 0, D20 and E20 --- Give me 3NEW But if C2 0, D2= 0, E20 --- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula driving me crazy | Excel Discussion (Misc queries) | |||
Crazy Excel 2003 Formula Question | Excel Discussion (Misc queries) | |||
crazy triple array formula | Excel Worksheet Functions | |||
Formula is driving me crazy????? | Excel Worksheet Functions | |||
Help I am going crazy with this formula. | Excel Worksheet Functions |