Nested IF AND statement producing #NAME?

The following Nested IF AND is producing a #NAME? in the cell:

=IF(AND($F$4=1,(A42+4*365).GE.AC38),AL46*(AI46-$A$55)*(1-0.01),AL46*(AI46-$A$55)*(1-$A$51))

The variables A42 and AC38 are dates. A42 is input and AC38 is calculated as all other dates in column AC. I want the program to switch calculation after the calculated date AC38 is four (4) years after start of project, i.e. when A42 + (4 * 365) greater than the calculated date AC38.

The following formula works fine which does not include the AND statement:

=IF($F$4=1,AL47*(AI47-$A$55)*(1-0.01),AL47*(AI47-$A$55)*(1-$A$51))

I don't understand what this means

(A42+4*365).GE.AC38)

Please explain what this means.

Alan

When A42 (a date) + (4 years x 365 days/year) is greater than AC38 (future date) then switch to secondary calculation.

Should be like this:

AC38.GE.(A42+(4*365)).

When the calculated date is greater than or equal to the starting date plus 4 years switch to secondary calculation.

I think what Alan was asking about was the bolded part below...
=IF(AND($F$4=1,(A42+4*365).GE.AC38),AL46*(AI46-$A$55)*(1-0.01),AL46*(AI46-$A$55)*(1-$A$51))

excel is trying to use that as a function - which doesnt exist

Also, an AND() statement is usually a test of a series of conditions that must be met for something to happen...=and(A1=1, B1=2, C1=3) etc
But looking at your formula, I only see 1 test, and then a bunch of calcs?

=IF(AND($F$4=1,(A42+4*365).GE.AC38)and then what?,AL46*(AI46-$A$55)*(1-0.01)and then what?,AL46*(AI46-$A$55)*(1-$A$51)and then what?)

The objective of the IF AND is to test two (2) criteria of which both must be positive to calculate using first equation. In other words, if these 2 criteria are true, use equation 1. In not, use equation 2.

=IF(AND($F$4=1,AC46.GT.($A$42+(4*365))),AZ46*(AI46*0.01+$A$55),AZ46*(AI46*$A$51+$A$55))

Explained:

If $F$4=1 and AC46.GT.($A$42+(4*365))) are true, then AZ46*(AI46*0.01+$A$55). If one or both are not true, then AZ46*(AI46*$A$51+$A$55)).

If $F$4 = 1 then calculations will continue until column AC value (successive monthly periods) exceeds 4 *365 (4 years or 48 months) from starting date $A$42. As soon as 48 months elapses, the two IF AND conditions are not met and calculation reverts to 2nd equation.

AC38.GE.(A42+(4*365)) there is no such thing as .GE. in excel you need

AC38>=(A42+(4*365))
> is greater than
< is less than
>= greater or equal
<=less than or equal
<> does not equal

forum is double posting again lol

Thanks Martin, I was trying to figure out what the GE. meant...hopfullly thats what the OP meant too?

Martin. Thanks from me, too. I was totally baffled by the GE.

In Fortran, operator .GE. means "greater than or equal to

well then it now makes perfect sense (not lol)