Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find a "9" within a range using a function (T/F)
I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within
a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA |
#2
|
|||
|
|||
Find a "9" within a range using a function (T/F)
Hi!
Try this: =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0 Biff "R. Choate" wrote in message ... I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA |
#3
|
|||
|
|||
Find a "9" within a range using a function (T/F)
Assuming your range of cells is A1:B5...
=IF(SUMPRODUCT(LEN(A1:B5)-LEN(SUBSTITUTE(A1:B5,"9","")))<0,FALSE,TRUE) HTH, -- Gary Brown If this post was helpful, please click the ''''Yes'''' button next to ''''Was this Post Helpfull to you?". "R. Choate" wrote: I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA |
#4
|
|||
|
|||
Find a "9" within a range using a function (T/F)
Hi,
First - thank you. Your solution DOES work. My only comment is that it throws an error if no 9. You probably just assumed I could take it from there; you're right, I can, and I appreciate it. Yours is a nice, short solution that does work. Richard -- RMC,CPA "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0 Biff "R. Choate" wrote in message ... I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA |
#6
|
|||
|
|||
Find a "9" within a range using a function (T/F)
It does not throw an error, it returns TRUE of FALSE
-- Regards, Peo Sjoblom "R. Choate" wrote in message ... Hi, First - thank you. Your solution DOES work. My only comment is that it throws an error if no 9. You probably just assumed I could take it from there; you're right, I can, and I appreciate it. Yours is a nice, short solution that does work. Richard -- RMC,CPA "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0 Biff "R. Choate" wrote in message ... I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA |
#7
|
|||
|
|||
Find a "9" within a range using a function (T/F)
My apologies. It was having the wrong range in it that caused the error. Your formula works great, and as I said earlier, thank you
for your help and a working solution to my situation. Richard -- RMC,CPA "R. Choate" wrote in message ... Hi, First - thank you. Your solution DOES work. My only comment is that it throws an error if no 9. You probably just assumed I could take it from there; you're right, I can, and I appreciate it. Yours is a nice, short solution that does work. Richard -- RMC,CPA "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0 Biff "R. Choate" wrote in message ... I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA |
#8
|
|||
|
|||
Find a "9" within a range using a function (T/F)
Yes, you are right. The error was my fault and I posted an apology for incorrectly stating that it caused an error with no 9
present. The error came from "user mistake". Thanks for checking it and letting me know so I could try it again. -- RMC,CPA "Peo Sjoblom" wrote in message ... It does not throw an error, it returns TRUE of FALSE -- Regards, Peo Sjoblom "R. Choate" wrote in message ... Hi, First - thank you. Your solution DOES work. My only comment is that it throws an error if no 9. You probably just assumed I could take it from there; you're right, I can, and I appreciate it. Yours is a nice, short solution that does work. Richard -- RMC,CPA "Biff" wrote in message ... Hi! Try this: =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0 Biff "R. Choate" wrote in message ... I need to try to cobble together a function nest or whatever I have to do, which will tell me if the number 9 exists anywhere within a range of cells. The 9 could be just one character of a larger number (i.e. 1496), or it could be just a nine. I know that I could use filtering or use the find feature with the dialog box, but I need a formula instead. Also, I just need to know a true or false; I don't need to know where the nine is or any other info. I just need to know if it is there. I'm stumped. Any ideas? Thanks in advance! -- RMC,CPA |
#9
|
|||
|
|||
Find a "9" within a range using a function (T/F)
Biff wrote...
.... =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0 .... If you're willing to put up with an array formula, =COUNT(FIND(9,range))0 |
#10
|
|||
|
|||
Find a "9" within a range using a function (T/F)
Hi Harlan,
You know, I was trying to cobble together an array formula on my own before I decided I was not on the right track. At least I thought that an array formula would be the path of least resistance...just couldn't make it work. Thanks, Harlan !! Richard -- RMC,CPA "Harlan Grove" wrote in message oups.com... Biff wrote... .... =SUMPRODUCT(--(ISNUMBER(FIND("9",A1:B12))))0 .... If you're willing to put up with an array formula, =COUNT(FIND(9,range))0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
creating function (vba) with range arguments | Excel Worksheet Functions | |||
dynamic named range function | Excel Worksheet Functions | |||
Function to determine if any cell in a range is contained in a given cell | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |