Nested if /and formula

N

Neeko

Here's the formula i've been trying to get to return a number:

IF(DATE_VALUE(G2)>2003/11/30,0.041,IF(AND(DATE_VALUE(G2)<2003/1201,AU="trust"),0.045,0.0735))

this value can be one of three numbers:

..041
..045
..0735

I'm trying to get .041 if ApprovalDate(G2) is greater than 11/30/03.
If (G2) is less than 12/1/03 then I need to look to Bank/Trust(AU) and
see if it = "trust". If both of these are true then .045, else .0735.
I've tried it this way and it returns a value, but it's incorrect.

IF(G2>11/30/03,0.041,IF(AND(G2<12/1/03,AU="trust"),0.045,0.0735))

This returns .041, but in this row, G2 = 03-Sep-02.

Any ideas?
 
B

Bob Phillips

Neeko,

That's because the value passes the first test met, and so it exits there.
Turn it round.

IF(AND(G2<12/1/03,AU="trust"),0.045,IF(G2>11/30/03,0.041,0.0735))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Ron Rosenfeld

Here's the formula i've been trying to get to return a number:

IF(DATE_VALUE(G2)>2003/11/30,0.041,IF(AND(DATE_VALUE(G2)<2003/1201,AU="trust"),0.045,0.0735))

this value can be one of three numbers:

.041
.045
.0735

I'm trying to get .041 if ApprovalDate(G2) is greater than 11/30/03.
If (G2) is less than 12/1/03 then I need to look to Bank/Trust(AU) and
see if it = "trust". If both of these are true then .045, else .0735.
I've tried it this way and it returns a value, but it's incorrect.

IF(G2>11/30/03,0.041,IF(AND(G2<12/1/03,AU="trust"),0.045,0.0735))

This returns .041, but in this row, G2 = 03-Sep-02.

Any ideas?
Click to expand...

I assume AU is a "named" cell. If it is a typo and should be, for example AU2,
you'll have to change the formula.

If G2 contains an Excel date, then:

=IF(G2 > DATE(2003,11,3), 0.041, IF(AND(G2 < DATE(2003,12,1), AU="trust"),
0.045, 0.0735))

If G2 contains text and not a real date, and it's in the format you show above,
then use DATEVALUE(G2) in place of G2 in the formula.



--ron
 
Top