if statement help

  • Thread starter Thread starter RichardO
  • Start date Start date
R

RichardO

Hello all:

I have an if statement problem as follows:

My data includes whether or not an account is automatic (column E).
The individual assigned (column H) is based on the dollar value
(column J) of the account and whether or not it is automatic.
I want to know if the correct individual is assigned for accounts tha
are not automatic AND have a dollar amount greater than $4,999. I wan
excel to return a "Yes" if the individual assigned is not in m
authorized list of individuals (peter, shawn, zakay, bob, tina, janet
monica, peterS and Susan. My formula is:
=IF(E2<>"Automatic",IF(J2>4999,IF(H2<>"peter",IF(H2<>"shawn",IF(H2<>"zakay",IF(H2<>"bob",IF(H2<>"tina",IF(H2<>"janet",IF(H2<>"monica",IF(H2<>"peterS",IF(H2<>"susan,"Yes","No")))))))))))

But I am getting a "FALSE" answer.

Also, is there a way that I don' t have to list all these authorize
names? Can you please suggested how else I could write the if statemen
without having to type all these names just in case the list grow
longer.

Thank you very much for helping out.


RichardO
 
Hi Richard,

Your immediate problem is that you're nesting too many IF statements - the
maximum is 8. You could get around that with something like:
=IF(AND(E2<>"Automatic",J2>4999,H2<>"peter",H2<>"shawn",H2<>"zakay",H2<>"bob
",H2<>"tina",H2<>"janet",H2<>"monica",H2<>"peters",H2<>"susan"),"Yes","No")

Beyond that, I'd suggest putting the list of names in a lookup table with a
named range that you can change as people are added/deleted.

Cheers


RichardO > said:
Hello all:

I have an if statement problem as follows:

My data includes whether or not an account is automatic (column E).
The individual assigned (column H) is based on the dollar value
(column J) of the account and whether or not it is automatic.
I want to know if the correct individual is assigned for accounts that
are not automatic AND have a dollar amount greater than $4,999. I want
excel to return a "Yes" if the individual assigned is not in my
authorized list of individuals (peter, shawn, zakay, bob, tina, janet,
monica, peterS and Susan. My formula is:
Click to expand...
=IF(E2<>"Automatic",IF(J2>4999,IF(H2<>"peter",IF(H2<>"shawn",IF(H2<>"zakay",
 
Hi Del,
Thanks for replying. How do I reference the named range. Say I pu
all the authorized list in a range named "authorized", How would
reference the list in my If statement?

Thank you.


RichardO
 
Hi
try the following
- put your allowed names in a range in your spreadsheet (e.g. in
X1:X20)
- use the following formula
=IF(AND(E2<>"Automatic",J2>4999,COUNTIF(X1:X20,H2)=0),"Yes","No")

--
Regards
Frank Kabel
Frankfurt, Germany

Hello all:

I have an if statement problem as follows:

My data includes whether or not an account is automatic (column E).
The individual assigned (column H) is based on the dollar value
(column J) of the account and whether or not it is automatic.
I want to know if the correct individual is assigned for accounts that
are not automatic AND have a dollar amount greater than $4,999. I
want excel to return a "Yes" if the individual assigned is not in my
authorized list of individuals (peter, shawn, zakay, bob, tina, janet,
monica, peterS and Susan. My formula is:
Click to expand...
=IF(E2<>"Automatic",IF(J2>4999,IF(H2<>"peter",IF(H2<>"shawn",IF(H2<>"za
 
Hello everyone, Hello Frank, thanks for replying. That worked fine
thanks.
I want to add another condition in the statement saying that i
K3>250000, and e3 <>"Automatic", if h3 is not in the list with rang
name "nonapsd", it should return a "No", otherwise, a "yes".
incorporated this in the later part of the formula below (from th
second if statement) but I am getting a FALSE.

=IF(AND(TRIM(E3)<>"Automatic",K3>4999,K3<250000,COUNTIF(nonapsa,TRIM(H3))<>1),IF(AND(TRIM(E3)<>"Automatic",K3>250000,COUNTIF(nonapsd,TRIM(H3))<>1),"No","Yes"))

Thanks for your invaluable assistance!

RichardO
 
Hi
does this give you your desired result:
=IF(AND(TRIM(E3)<>"Automatic",J3>4999,COUNTIF(nonapsa,TRIM(H3))=0),"Yes
",IF(AND(K3>250000,TRIM(E3)<>"Automatic",COUNTIF(nonapsa,TRIM(H3))=0),"
No","Yes"))

--
Regards
Frank Kabel
Frankfurt, Germany

Hello everyone, Hello Frank, thanks for replying. That worked fine,
thanks.
I want to add another condition in the statement saying that if
K3>250000, and e3 <>"Automatic", if h3 is not in the list with range
name "nonapsd", it should return a "No", otherwise, a "yes". I
incorporated this in the later part of the formula below (from the
second if statement) but I am getting a FALSE.
Click to expand...
=IF(AND(TRIM(E3)<>"Automatic",K3>4999,K3<250000,COUNTIF(nonapsa,TRIM(H3
 
Back
Top