Cell referenced must have certain result

Microsoft Office 2007 home and student
August 13, 2010 at 13:52:58
Specs: Windows Vista
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.


See More: Cell referenced must have certain result

Report •

#1
August 13, 2010 at 14:51:51
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.


Report •

#2
August 13, 2010 at 18:00:50
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


Report •
Related Solutions


Ask Question