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

 

[Aladin Akyurek]

I don't completely understand what you mean by "Excel readable small sample". […]

Book1
	A	B	C
1	ORDER NO.	TASK	CREATED ON
2	TT1234567	SHIP	1/1/2019
3	TT1234567	RPCK	1/1/2019
4	TT1234567	PSHP	1/2/2019
5	(BLANK)	CYCC	1/1/2019
6	TT1234567	RPCK	1/3/2019
7	TT7654321	RPCK	1/10/2019
8	TT9999999	RPCK	1/20/2019
9	TT7654321	SHIP	1/25/2019
10	TT8888888	PSHP	1/25/2019
Sheet2

This is an "excel readable small sample." Just copy and paste t in a sheet on your Excel system. You see you can use it immediately, that is, process it by means of formulas in order to generate a desired output.

 

[rmbmst1972]

	A	B	C
	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.	=IF(B1="","",IF(AND(ISNUMBER(SEARCH("minimum",B1)),ISNUMBER(SEARCH("maximum",B1)),ISNUMBER(SEARCH("range",B1)),NOT(ISNUMBER(SEARCH({"mean","median","mean absolute deviation","interquartile range"},B1)))),2,1))

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet2​

This is an "excel readable small sample." Just copy and paste t in a sheet on your Excel system. You see you can use it immediately, that is, process it by means of formulas in order to generate a desired output.

Aladin,

I think I am about to call it quitting time soon because I am finding it more difficult to understand what you need from me. I don't know what else to do. Therefore, I hope I provided you with something that is productive. I tried to my table neat like yours, but I didn't have success.

Robert

 

[rmbmst1972]

	A	B	C
ORDER NO.	TASK	CREATED ON
TT1234567	SHIP
TT1234567	RPCK
TT1234567	PSHP
(BLANK)	CYCC
TT1234567	RPCK
TT7654321	RPCK
TT9999999	RPCK
TT7654321	SHIP
TT8888888	PSHP

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1/1/2019[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1/1/2019[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/2/2019[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/1/2019[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/3/2019[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1/10/2019[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1/20/2019[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1/25/2019[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1/25/2019[/TD]

</tbody>

Sheet2​

This is an "excel readable small sample." Just copy and paste t in a sheet on your Excel system. You see you can use it immediately, that is, process it by means of formulas in order to generate a desired output.

Okay...I have an idea. I have created this formula. If you see understand what I am trying to do with it, I am hoping you can fill-in the missing pieces to make it work. It is now 3:00 am EST, and I need to go to bed. I will check my email again after I wake up.

=IF(L4="","",IF(AND(ISNUMBER(SEARCH("minimum",L4)),ISNUMBER(SEARCH("maximum",L4)),ISNUMBER(SEARCH("range",L4)),ISNUMBER(SEARCH({"mean","median","mean absolute deviation","interquartile range"},L4)),1,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,0))

 

[Aladin Akyurek]

Aladin,

I think I am about to call it quitting time soon because I am finding it more difficult to understand what you need from me. I don't know what else to do. Therefore, I hope I provided you with something that is productive. I tried to my table neat like yours, but I didn't have success.

Robert

Okay...I have an idea. I have created this formula. If you see understand what I am trying to do with it, I am hoping you can fill-in the missing pieces to make it work. It is now 3:00 am EST, and I need to go to bed. I will check my email again after I wake up.

=IF(L4="","",IF(AND(ISNUMBER(SEARCH("minimum",L4)),ISNUMBER(SEARCH("maximum",L4)),ISNUMBER(SEARCH("range",L4)),ISNUMBER(SEARCH({"mean","median","mean absolute deviation","interquartile range"},L4)),1,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,0))

I just want a sample from you with which can work, not formulas. That's all. (By the way, I tried to show you what posting a sample means.)

 

[Eric W]

This kind of formula really works best if you make an external table with the conditions you want. That way it's MUCH easier to change, read, and understand. For example, if I understand your request:

	B	C	D	E	F	G
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. The range would increase.< /td>
Score:	Points:
Condition:	Word list	0=include, 1 = exclude	Word list	0=include, 1 = exclude
minimum	minimum
	maximum	maximum
range	range
mean
median
mean absolute deviation
interquartile range

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet20​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulasble>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]{=IF(PRODUCT(ISNUMBER(SEARCH($D$4:$D$11,B1))-$E$4:$E$11),$D$2,IF((PRODUCT(ISNUMBER(SEARCH($F$4:$F$11,B1))-$G$4:$G$11)),$F$2,0))}[/TD]
[/TR]
</tbody>[/TABLE]



In D2 put the number of points you want. In D4:D11 put your word list, and in the corresponding row in column E, put a 0 if the word must be included, or a 1 if it must be excluded. Repeat in columns F:G. Then the B3 formula should do what you want.

I've been playing around with a shorter formula that allows adding more columns easily, but it requires a pretty awkward table in its current incarnation.[/TD]
[/TR]
</tbody>[/TABLE]