Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula confusion !!!!
I would like a formula that would give rise to a different result depending on the number occurrences in a particular cell.
I am currently using the following formula; =IF(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"MATCH" ,"D") would like a result of "ONE" if SP or HA or PA occurs in cell a result of "TWO" if any combination of two matches occur and "THREE" for any combination of three. Please help ! Many thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula confusion !!!!
Hi Stu,
Am Sat, 10 Mar 2012 17:06:36 +0000 schrieb stu28: would like a result of "ONE" if SP or HA or PA occurs in cell a result of "TWO" if any combination of two matches occur and "THREE" for any combination of three. try: =CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2)),"ONE"," TWO","THREE") Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Claus, Thank you for a solution to my previous problem. If i may ask a further question - if i were to want an empty cell to return a result of 'an empty cell" would this be possible ?
Also does excel allow a number of formulae in a single cell ? for example ; =ISNUMBER(SEARCH("X",D2:D50)) [if true then] =ISNUMBER(SEARCH("AF",B2:B50)) [if true then] =CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ON E"," TWO","THREE") Again thankyou Stu |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula confusion !!!!
Hi Stu,
Am Mon, 12 Mar 2012 02:18:49 +0000 schrieb stu28: Also does excel allow a number of formulae in a single cell ? for example ; =ISNUMBER(SEARCH("X",D2:D50)) [if true then] =ISNUMBER(SEARCH("AF",B2:B50)) [if true then] =CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ON E"," TWO","THREE") what do you want to do? You could work with nested IF formulas, but in your case you will only get the result of the first true condition. The other conditions will not be shown. Or you have to combine the IF-statements with OR or AND. But if all your conditions have to be true, you can try: =IF((ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X" ,D2))+(COUNT(SEARCH({"SP","HA","PA"},C2))0))=3,"O K","") Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
|
|||
|
|||
[quote=Claus Busch;1599708]Hi Stu,
Am Mon, 12 Mar 2012 02:18:49 +0000 schrieb stu28: Also does excel allow a number of formulae in a single cell ? for example ; =ISNUMBER(SEARCH("X",D2:D50)) [if true then] =ISNUMBER(SEARCH("AF",B2:B50)) [if true then] =CHOOSE(COUNT(SEARCH({"SP","HA","PA"},C2:C50)),"ON E"," TWO","THREE") what do you want to do? You could work with nested IF formulas, but in your case you will only get the result of the first true condition. The other conditions will not be shown. Or you have to combine the IF-statements with OR or AND. But if all your conditions have to be true, you can try: =IF((ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X" ,D2))+(COUNT(SEARCH({"SP","HA","PA"},C2))0))=3,"O K","") Regards Claus Busch -- Thank you for your time claus, what i am trying to do is search my inventory, i currently have three working formulae , but in separate boxes and wished to combine these. I tried your new formula but it returns a blank cell always. I've tried to modify ; =IF((ISNUMBER(SEARCH("AF",B2:B50))+ISNUMBER(SEARCH ("X",D2: D50))+(COUNT(SEARCH({"SP","HA","PA"},C2:C50))0))= "ONE","TWO","THREE") but "THREE'' is always returned. I need both first conditions to be true and the third condition to return a result of "ONE" for one match, "TWO" for two, "THREE" for three and a blank cell for none. Ideally i would also like a blank cell if either of the first conditions are false. Again i appreciate your help. Stu Last edited by stu28 : March 15th 12 at 12:34 AM |
#6
|
|||
|
|||
[quote=stu28;1599817]
Quote:
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula confusion !!!!
Hi Stu,
Am Thu, 15 Mar 2012 00:31:58 +0000 schrieb stu28: what i am trying to do is search my inventory, i currently have three working formulae , but in separate boxes and wished to combine these. I tried your new formula but it returns a blank cell always. I've tried to modify ; =IF((ISNUMBER(SEARCH("AF",B2:B50))+ISNUMBER(SEARCH ("X",D2: D50))+(COUNT(SEARCH({"SP","HA","PA"},C2:C50))0))= "ONE","TWO","THREE") but "THREE'' is always returned. I need both first conditions to be true and the third condition to return a result of "ONE" for one match, "TWO" for two, "THREE" for three and a blank cell for none. Ideally i would also like a blank cell if either of the first conditions are false. I hope, that I understood your problem. Try: =IF(ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X", D2))<2,"","true "&(IF(COUNT(SEARCH({"SP";"HA";"PA"},C2))0,CHOOSE( COUNT(SEARCH({"SP";"HA";"PA"},C2)),"ONE","TWO","TH REE"),""))) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula confusion !!!!
Hi Stu,
Am Thu, 15 Mar 2012 08:19:38 +0100 schrieb Claus Busch: =IF(ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X", D2))<2,"","true "&(IF(COUNT(SEARCH({"SP";"HA";"PA"},C2))0,CHOOSE( COUNT(SEARCH({"SP";"HA";"PA"},C2)),"ONE","TWO","TH REE"),""))) it is a typo in the formula above. Try: =IF(ISNUMBER(SEARCH("AF",B2))+ISNUMBER(SEARCH("X", D2))<2,"","true "&(IF(COUNT(SEARCH({"SP","HA","PA"},C2))0,CHOOSE( COUNT(SEARCH({"SP","HA","PA"},C2)),"ONE","TWO","TH REE"),""))) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
|
|||
|
|||
Claus, thank you very much, the formula not only works perfectly but has given me a far greater understanding of excel and building formulae.
I appreciate your time, thanks again Stu |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Confusion | Excel Programming | |||
If Formula confusion | Excel Discussion (Misc queries) | |||
MDI Confusion | Excel Discussion (Misc queries) | |||
Formula confusion | Excel Worksheet Functions | |||
Formula Confusion | Excel Discussion (Misc queries) |