Nested if /and formula

  • Thread starter Thread starter Neeko
  • Start date Start date
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?
 
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)
 
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
 
Back
Top