Formula error - IF(AND...


G

Guest

HI!
Please I need help for this...
I tried to put more conditions, but excel says that formula contains an error.
You know an alternative for this ?
=IF(AND(G2>=C2;G2<=D2);E2;IF(AND(G2>=C3;G2<=D3);E3;IF(AND(G2>=C4;G2<=D4);E4;IF(AND(G2>=C5;G2<=D5);E5;IF(AND(G2>=C6;G2<=D6);E6;IF(AND(G2>=C7;G2<=D7);E7;IF(AND(G2>=C8;G2<=D8);E8;0))))))

Thanks for yor help.
 
Ad

Advertisements

B

Bob Phillips

You are hitting Excel's 7 nested functions limit. You will need to break it
down.

--

HTH

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


Pasmatos said:
HI!
Please I need help for this...
I tried to put more conditions, but excel says that formula contains an error.
You know an alternative for this ??
=IF(AND(G2>=C2;G2<=D2);E2;IF(AND(G2>=C3;G2<=D3);E3;IF(AND(G2>=C4;G2<=D4);E4;
 
Ad

Advertisements

G

Guest

Hi,

Try this array formula and confirm with CTRL-SHIFT-ENTER. Modify the ranges
appropriately (note that the ranges in columns C, D, and E should be equal in
length). The formula assumes that E63000 is empty, which I am sure, will be
the case.

=INDIRECT("E"&MIN(IF(($G$2>=$C$2:$C$101)*($G$2<=$D$2:$D$101)*ROW($C$2:$C$101)<>0,ROW($C$2:$C$101),63000)))

Regards,
B. R. Ramachandran
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top