I am trying to add exceptions to a formula

rmbmst1972

New Member
Joined
Sep 29, 2018
Messages
14
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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

New Member
Joined
Sep 29, 2018
Messages
14
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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

New Member
Joined
Sep 29, 2018
Messages
14
(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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Care to post a sample we can experiment with, using the conditions you stipulate?
 

rmbmst1972

New Member
Joined
Sep 29, 2018
Messages
14
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'.
 

rmbmst1972

New Member
Joined
Sep 29, 2018
Messages
14
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.
 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top