If and or statement

[TL30N]

Hi,

I have multiple conditions that I want to satisfy in an IF statement and am having a spot of bother.

In order to classify as new I want AD ABOVE 300, AE TO BE C, AF to be above 90%, AK to be "Stable Price" and AL to be between 4 weeks 201712,201713,201714 and 201715. I have the following below, but I'm having a problem

IF(((AND(AD7>300,AE7="C",AF7>90%,AK7="Stable Price",AL7="201712,201713,201714,2015","NEW","NOT NEW")))))

But it's not working, will somebody help please?

I think I need to add an OR in the dates, but not sure where.

Thanks

 

[BarryL]

without seeing your data set its hard to tell if this will work but try

=IF(AND(AD7>300,AE7="C",AF7>90%,AK7="Stable Price"),IF(OR(AK7="201712",AK7="201713",AK7="201714",AK7="201715"),"NEW","NOT NEW"),"NOT NEW")

 

[pjmorris]

Hi,

Not quite sure what you mean by dates expressed as 201712 or 201715, but assuming they are dates then try this:

=IF(((AND(AD7>300,AE7="C",AF7>90%,AK7="Stable Price",AL7=>"201712",AL7<"201715","NEW","NOT NEW")))))

I can improve this if you can describe how you have arrived at these dates.

Regards

 

[gaz_chops]

Try

=IF(AND(AD7>300,AE7="C",AF7>90%,AK7="Stable Price",OR(AL7={201712,201713,201714,2015})),"NEW","NOT NEW")

 

[TL30N]

Try

=IF(AND(AD7>300,AE7="C",AF7>90%,AK7="Stable Price",OR(AL7={201712,201713,201714,2015})),"NEW","NOT NEW")

Hi both,
AD AE AF AG AH AI AK AL
Number of Stores	PKCS (max)	Branch Availability	£ Price Wk-4	£ Price Wk-3	£ Price Wk-2	£ Price Wk-1	Stable Price	F'cast Start Date
S > P > K > C	(Stock-in days %, Last Wk)	(Thur, UK)	(Thur, UK)	(Thur, UK)	(Thur, UK)	()
						(Fncl Wk)
363	K	93%	£ 134.00	£ 134.00	£ 134.00	£ 134.00	Stable Price	201651
362	P	29%	£ 77.00	£ 77.00	£ 77.00	£ 77.00	Stable Price	201645
365	K	25%	£ 79.00	£ 79.00	£ 79.00	£ 79.00	Stable Price	201446
Does the data below help? Peter, I couldn't get your formula to work and Barry, I didn't get the right answer with yours. Really appreciate the help.

<colgroup><col span="15"></colgroup><tbody>
</tbody>

 

[gaz_chops]

Did mine work? It appears to!

Are the Year/Week No.s text or numbers?

[TABLE="width: 650"]
<!--StartFragment--> <colgroup><col width="65" span="10" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
 [/TR]
 [TR]
  [TD]NEW[/TD]
  [TD="align: right"]363[/TD]
  [TD]C[/TD]
  [TD="class: xl63, align: right"]93%[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]Stable Price[/TD]
  [TD="align: right"]201712[/TD]
 [/TR]
 [TR]
  [TD]NOT NEW[/TD]
  [TD="align: right"]400[/TD]
  [TD]C[/TD]
  [TD="align: right"]200[/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD]Stable Price[/TD]
  [TD="align: right"]201651[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[BarryL]

the below works for me

=IF(AND(AD6>300,AE6="C",AF6>90%,AK6="Stable Price"),IF(AND(AL6>=201712,AL6<=201715),"NEW","NOT NEW"),"NOT NEW")

 

[pjmorris]

Hi TL30N,

The key, I think, will be in your answer to Gaz's question about the format of the year/week. If I were to assume it is a number then this should work:

=IF(((AND(AD7>300,AE7="C",AF7>90%,AK7="Stable Price",AL7=>201712,AL7<201715,"NEW","NOT NEW")))))

where the quote marks have been removed from the dates.

HTH.

 

[TL30N]

Thank you all very much, I managed to crack it with your help!

 

[gaz_chops]

You're welcome, care to share what formula you used?