Microsoft Office 2007 home and student

I have the following formula in a cell: =IF(AND(IF($E$2<L23,$E$2-K23,L23-K23)/7>=0.1,IF($E$2<L23,$E$2-K23,L23-K23)/7<=1),1,IF(IF($E$2<L23,$E$2-K23,L23-K23)/7<=0.1,0,IF($E$2<L23,$E$2-K23,L23-K23)/7))

Now I need to add another rule to it. If A23 says "ACZ", "BZ" or "DZ Diploma then this formula can be used. If it says anything else, then I need this result to equal 1.

FYI. The cell A23 has a data validation set up so a specific "name" is chosen from a list.

I have the words "ACZ", "BZ" and "DZ Diploma" in cells A152, A153 and A157 (same order).

I have tried both of the following:

=IF(AND(IF($E$2<L23,$E$2-K23,L23-K23)/7>=0.1,IF(A23=“ACZ”),IF(A23=“BZ”),IF(A23=“DZ Diploma”,IF($E$2<L23,$E$2-K23,L23-K23)/7<=1),1,IF(IF($E$2<L23,$E$2-K23,L23-K23)/7<=0.1,0,IF($E$2<L23,$E$2-K23,L23-K23)/7))and

=IF(AND(IF($E$2<L23,$E$2-K23,L23-K23)/7>=0.1,IF(A23=$A$152),IF(A23=$A$153),IF(A23=$A$157),IF($E$2<L23,$E$2-K23,L23-K23)/7<=1),1,IF(IF($E$2<L23,$E$2-K23,L23-K23)/7<=0.1,0,IF($E$2<L23,$E$2-K23,L23-K23)/7))

I'm not sure what my formula is missing or what I have done incorrectly but neither are working. Could you please take a look at it?

Thank you in advance for your time with this.

Your post either has too much information or not enough. Why are you telling this?

"I have the words "ACZ", "BZ" and "DZ Diploma" in cells A152, A153 and A157 (same order).In any event, have you tried something like this:

=IF(OR(A23="ACZ", A23="BZ",A23="DZ Diploma"),

your original IF statement,1)You IF will only be used if A23 contains one of those 3 items. Otherwise, it will return 1.

Hi, This should do it:

=IF(OR(A23="ACZ", A23="BZ",A23= "DZ Diploma"),IF(AND(IF($E$2<L23,$E$2-K23,L23-K23)/7>=0.1,IF($E$2<L23,$E$2-K23,L23-K23)/7<=1),1,IF(IF($E$2<L23,$E$2-K23,L23-K23)/7<=0.1,0,IF($E$2<L23,$E$2-K23,L23-K23)/7)),1)Regards

Ask Your Question

Weekly Poll