I am trying to add exceptions to a formula

[rmbmst1972]

Dear Sir or Madam:


My name is Robert, and I have just one more Excel formula issue before I fall asleep. The following formula works perfectly, but I want to add exceptions to it. I need Excel to find a cell that contains "minimum", "maximum", and range" with any other words except the following: "mean", "median", "mean absolute deviation", "interquartile range". If a cell contains "minimum", "maximum", and range" with any other words EXCLUDING the exceptions, I want to assign the result to a value of 2. If a cell contains "minimum", "maximum", and range" with any other words INCLUDING any of the exceptions, I want to assign the result to a value of 1.


=IF(L4="","",IF(AND(ISNUMBER(SEARCH("minimum",L4)),ISNUMBER(SEARCH("maximum",L4)),ISNUMBER(SEARCH("range",L4))),"2","0"))


Sincerely,
Robert

 

[jtakw]

Hi,

Same here...about the falling asleep part...

If I understand correctly, use this:

=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))

BTW, Don't put quote marks around the numbers ( "2", "0", etc. ), if they are meant to be Numbers, quotes turn them into Text.

 

[jtakw]

Just noticed a potential problem.

Since "mean" is part of "mean absolute deviation", we don't really need the latter, BUT, are there Any other variations of Text within L4 that IS allowed (Not in the Exceptions list) that may contain the word "mean" (i.e. "meaning", "mean something", etc.) ???

 

[rmbmst1972]

The following formula you gave me works, but I previously forgot to mention I also need to a assign a value of zero for a response not meeting the full or partial credit criteria. I have a strange feeling the zero will not be placed next to the "2" and "1".

=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))

 

[jtakw]

I previously forgot to mention I also need to a assign a value of zero for a response not meeting the full or partial credit criteria.

You'll need to explain in detail, understand what circumstances is "response not meeting the full or partial credit criteria".

 

[rmbmst1972]

(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'.

 

[Aladin Akyurek]

Care to post a sample we can experiment with, using the conditions you stipulate?

 

[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'.

 

[Aladin Akyurek]

Try to post an Excel readable small sample including all possible occurrences.

 

[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'.

I don't completely understand what you mean by "Excel readable small sample". If you mean a student sample response, then I provided you with the following:

The minimum would most likely stay the same because unless the hit was less than 10 then it shouldnt change. Its the same with the maximum. Unless his hit is higher than 50 then it shouldnt change either. All of the other things would change because a new number was added in. The mean would change.The median would change. The mean absolute deviation would change. The would change. And finally, the interequality would change as well.