Nested IF OR not functioning the way I want

Excel 2007, I have the following formula in cell F21:

=IF(I17<=8,"INVALID",IF(I17=9," ",IF(OR(I9<=28,I15=1),"ERROR")))

What I want is for the cell to remain blank if I17 is equal to 9 AND if I15 does not = 1, for the cell to read "Invalid" if I17 is less than 9 (I17 will only be 0-9), OR to read "ERROR" if either I17 is anything between 10 and 28 (28 is the max the cell could be) OR if I15 is equal to 1.

The formula works fine for returning either a blank, "INVALID" or "ERROR" based on the values or I17 and I9, but does NOT work (does not return "ERROR) based on the value of I15.

If both I15 = 1 and if I17 <= 8. then "INVALID" should return instead of "ERROR."


If anyone just wants to be nice, they can look at the uploaded sheet and my formulas and make suggestions on how to make it all cleaner. It is an assessment that uses the answers to 9 questions and assigns a weighted value to one or more of 3 different scales (S1, S2, S3). Scales 1 and 2 are combined for an overall score, while scale 3 is used simply to flag an elevated condition. Some questions are worth just a point, some are worth more or no points for each scale. The formulas for columns E-G through Rows 8-16 are correct. And the scores must be converted to a 6 point scale and the formulas in row 20 for each scale are correct, but, yes answers to Q4 and Q5 are worth 1 point on S1-S3, but for S1 and S3 (not on S2), they are only worth 1 point even if both questions are yes. That's where my formulas in E18 & G18 come in. The end user only completes D8-D16, but each answer must be given. They start with a blank cell but if its left blank, it'll read error. Also, Q1 and Q2 can be yes/yes, no/no, or yes/no, but can not be no/yes - that's where H9 comes in. Also, if D8 has a numeric value other than 0, then either Q4 or Q5 must be yes. See formulas in columns H for those questions.

I have removed the specific questions because the instrument is proprietary and not yet published, not that anyone on here would care, but it protects me. In the final version, rows 17-19 are hidden, fonts columns H and I are changed to white (to hide them) except for where "ERROR" appears, and the whole sheet is protected except the 9 answer spaces in column D and a couple of identifying cells at the top.

try this code...
I added the asterisk to asses a 4th posibility if any...*it can be left blank!! ""

Please Login or Register  to view this content.

Awesome. Both seemed to work fine. I'm learning... thanks much!!