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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.) ???
 
Upvote 0
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))
 
Upvote 0
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".
 
Upvote 0
(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'.
 
Upvote 0
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'.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top