Error Within IF Statement

[John]

Could anyone assist me in assessing what is wrong with the formula below. I
am getting a message "the formula you typed contains and error". What is
highlighted is the last AND shown below

Thanks


=IF(F3="DOB Missing","Can't Calculate
Rate",IF(D3="Country","",(IF(AND(F3<18),Rates!D16,(IF(AND(F3<19,F3>=18),Rates!D10,(IF(AND(F3>=19,F3<20,J3<1),Rates!D10,(IF(AND(F3>=19,F3<20,J3>1),Rates!D12,(IF(AND(F3>=20,J3<1),Rates!D10,(IF(AND(F3>=20,J3>1,J3<2),Rates!D12,Rates!G31)))))))))))))

 

[Bob Phillips]

John,

You have too many nested IFs in there. You also tend to do unnecessary tests
(AND(F3<18), and IF(AND(F3<18),Rates!D16,(IF(AND(F3<19,F3>=18), for
example).

Luckily, I think re-organising the data allows it to be reduced to a lower
number, such as

=IF(F3="DOB Missing","Can't Calculate
Rate",IF(D3="Country","",IF(F3<18,Rates!D16,IF(F3=18,Rates!D10,IF(OR(AND(F3=
19,J3<1),AND(F3>=20,J3<1)),Rates!D10,IF(OR(AND(F3=19,J3>1),AND(F3>=20,J3>1,J
3<2)),Rates!D12,Rates!G31))))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

Could anyone assist me in assessing what is wrong with the formula below. I
am getting a message "the formula you typed contains and error". What is
highlighted is the last AND shown below

Thanks


=IF(F3="DOB Missing","Can't Calculate

Rate",IF(D3="Country","",(IF(AND(F3<18),Rates!D16,(IF(AND(F3<19,F3>=18),Rate
s!D10,(IF(AND(F3>=19,F3<20,J3<1),Rates!D10,(IF(AND(F3>=19,F3<20,J3>1),Rates!

 

[John]

Thanks Bob, I did remove one IF and it worked, my eyes were fuzzy looking at
the logic within

 

[Bob Phillips]

There is nothing wrong with breaking it down you know, for easier
maintenance, like putting the complex bits in separate cells, say

K3: =OR(AND(F3=19,J2>1),AND(F3>=20,J2>1,J2<2))
K2: =OR(AND(F3=19,J2<1),AND(F3>=20,J2<1))
K1: =IF(F3="DOB Missing","Can't Calculate
Rate",IF(D3="Country","",IF(F3<18,Rates!D16,IF(F3=18,Rates!D10,IF(K2,Rates!D
10,IF(K3,Rates!D12,Rates!G31))))))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

This also shows that it can be reduced further, which I missed in the
complex version, to

K1: =IF(F3="DOB Missing","Can't Calculate
Rate",IF(D3="Country","",IF(F3<18,Rates!D16,IF(OR(F3=18,K2),Rates!D10,IF(K3,
Rates!D12,Rates!G31)))))

or even

K3: =OR(AND(F3=19,J2>1),AND(F3>=20,J2>1,J2<2))
K2: =OR(F3=18,AND(F3=19,J2<1),AND(F3>=20,J2<1))
K1: =IF(F3="DOB Missing","Can't Calculate
Rate",IF(D3="Country","",IF(F3<18,Rates!D16,IF(K2,Rates!D10,IF(K3,Rates!D12,
Rates!G31)))))

it's getting simpler all the time <g>

--

HTH

RP
(remove nothere from the email address if mailing direct)