I Need a formula to show full, partial and zero credit results

[rmbmst1972]

Dear Sir or Madam:




My name is Robert, and I have just one Excel formula issue. The following formula works perfectly, but I want to add criteria for "0" credit as well. Any assistance you can provide will be greatly appreciated.


Sincerely,
Robert




=IF(L4="","",IF(AND(ISNUMBER(SEARCH("minimum",L4)),ISNUMBER(SEARCH("maximum",L4)),ISNUMBER(SEARCH("range",L4)),NOT(ISNUMBER(SEARCH({"mean","median","mean absolute deviation","interquartile range"},L4)))),2,1))




(Full Credit):




If a cell contains "minimum", "maximum", and "range" with any other words EXCLUDING "mean", "median", "mean absolute deviation", or "interquartile range", I want to assign the result to a value of '2'.




(Partial Credit):




If a cell contains "minimum", "maximum", and range" with any other words INCLUDING "mean", "median", "mean absolute deviation", or "interquartile range", I want to assign the result to a value of '1'.




(Zero Credit):




If a cell DOES NOT contain "minimum", "maximum", and "range" with any other words EXCLUDING "mean", "median", "mean absolute deviation", or "interquartile range", I want to assign the result to a value of '0'.




OR




If a cell DOES NOT contain "minimum", "maximum", and "range" with any other words INCLUDING "mean", "median", "mean absolute deviation", or "interquartile range", I want to assign the result to a value of '0'.

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1086032-i-am-trying-add-exceptions-formula.html

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).