Nested if function using both "and" and "or"

[ifu06416]

Hi there,

I have four 1 year long periods were a "special offer" was available and im trying to build an if function in B2 that identifies whether the offer was available on a random date (B1).

	A	B
1	Random date	Dec 04
2	Offer	available
3
4	nov 03	nov 04
5	apr 06	apr 07
6	aug 09	aug 10
7	feb 12	feb 13
8

<tbody>
</tbody>

I'm working with the formula below and it works for all other dates except those between Nov 04 and Apr 06 where the offer was not available. As can be seen in the example above the random date is Dec 04 and the formula in B2 calculated the offer as being available, incorrectly.

As I mentioned other dates eg between April 07 and August 09 work fine so I'm not sure what's wrong.

<a4,and(b1><a5),and(b1><a6),and(b1><a7),b1><a4,and(b1><a5),and(b1><a6),and(b1><a7),b1><a4,and(b1><a5),and(b1><a6),and(b1><a7),b1><a4,and(b1><a5),and(b1><a6),and(b1><a7),b1><a4,and(b1><a5),and(b1><a6),and(b1><a7),b1>IF(OR(B1<A4,AND(B1>B4, B1<A5),AND(B1>B5,B1<A6),AND(B1>B6,B1<A7),B1>B7),"not available","available")

Thank you in advance,

John Carlin.</a7),b1></a6),and(b1></a5),and(b1></a4,and(b1></a7),b1></a6),and(b1></a5),and(b1></a4,and(b1></a7),b1></a6),and(b1></a5),and(b1></a4,and(b1></a7),b1></a6),and(b1></a5),and(b1></a4,and(b1></a7),b1></a6),and(b1></a5),and(b1></a4,and(b1>

 

[njimack]

I'm surprised you're existing function works for you since it doesn't refer to B1. Try this:
Sheet2

*	A	B
1	Random date	Dec-04
2	Offer	unavailable
3	*	*
4	Nov-03	Nov-04
5	Apr-06	Apr-07
6	Aug-09	Aug-10
7	Feb-12	Feb-13

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:89px;"><col style="width:63px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(SUMPRODUCT(--(A4:A7>=B1),--(B4:B7<=B1)),"","un")&"available" <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ifu06416]

Any thoughts as to why every time i copy and paste the formula from excel to the forum it removes all the greater than and less than symbols? My 10 min editing window expired before i could correct it.

 

[njimack]

Any thoughts as to why every time i copy and paste the formula from excel to the forum it removes all the greater than and less than symbols? My 10 min editing window expired before i could correct it.

It's a long standing issue. The workaround is to insert a space on either side of the greater than/less than symbols.

 

[ifu06416]

ah, good to know, thank you.