HELP with crazy formula

Y

YEIDIN

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
 
G

Gaurav

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","")))
 
S

Sandy Mann

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?


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

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","")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Y

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?
 
Y

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?
 
S

Sandy Mann

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"))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

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

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
G

Gaurav

Great observation. :)


Sandy Mann said:
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

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top