Multiple IF Statements

[primagic]

I am trying to write an IF statement in a cell which will return 3 values

If cell A1 is between 11/10/2008 And 31/10/2008 return value 140, if between 01/11/2008 And 15/11/2008 return 130, if between 16/11/2008 And 30/11/2008 return 120.

Any ideas please

 

[primagic]

sorry this is the statement I have currently but it gives me an error

=IF(AND(C2>=DATEVALUE("13/10/2008")),C2<=DATEVALUE("27/10/2008")140,IF(AND(C2>=DATEVALUE("28/10/2008")),C2<=DATEVALUE("03/11/2008")130,IF(C2<=DATEVALUE("04/11/2008"),120)))

 

[xlbo]

Your syntax is wrong:
=IF(AND(C2>=DATEVALUE("13/10/2008"),C2<=DATEVALUE("27/10/2008")),140,IF(AND(C2>=DATEVALUE("28/10/2008"),C2<=DATEVALUE("03/11/2008")),130,IF(C2<=DATEVALUE("04/11/2008"),120)))

However, no real need to use AND as you can logically deduce the bucket based on using the smallest date 1st:

=if(C2<=Datevalue("31/10/2008"),140,if(C2<=datevalue("15/11/2008"),130,120)

But neither formula gives a result if dates are outside of your date bands

Aside from that, what happens when your dates change? Do you really want to change lots of formulae?

Might be better off looking at a lookup table which would be set up like:

01/01/1900 Too Early
11/10/2008 140
01/11/2008 130
16/11/2008 120
31/12/9999 Too Late

Name the Range "DateLookup" and use a vlookup formula withe the 4th argument set to TRUE for the nearest match that is less than or = to the value being sought:

=vlookup(C2,DateLookup,2,TRUE)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question