Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with wildcard in complex formula... help?
Having a problem with using wildcards in a formula I wrote. The formula is: =IF(AND($A$79<"",E$99<""),IF($E$94<0,SUMPRODUCT ((($K$8:$K$74="*"&$A $79&"*")*($C$8:$C$74="*"&E$99&"*"))/$E$94),0),"") A79 is a reference value that has an actual text value in it, E99 is a reference value that has a text value indirectly pulled into it (its another formula). K8-74 is a range of responses, C8-74 is a separate range of responses. E94 is the total responses overall. What I'm doing here is trying to find the matches between two variables in a bunch of interview responses. Because of how the responses work I need to be able to wildcard them for the value (some responses can have multiple entries, and I just need to search for the term). The problem is taht when I put in the wild card *s or the values I'm testing have multiple entries (so "a, b, c" and not just "a") the formula above ceases to function properly. Thoughts? Based on other formulas I'm using this SHOULD work, and all the references are solid (I've checked them about 20 times). Very frustrating... Am I missing something? I have other places where I do this same function without wildcards and it works, so I have to think it isn't that the comparison itself is invalid (i.e., it is comparing the text in A79 with the formula in E99, not the text value that gets pulled into E99). Any help is greatly appreciated. Thanks, Rick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with wildcard in complex formula... help?
=IF(AND($A$79<"",E$99<""),IF($E$94<0,
SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))* (ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... Having a problem with using wildcards in a formula I wrote. The formula is: =IF(AND($A$79<"",E$99<""),IF($E$94<0,SUMPRODUCT ((($K$8:$K$74="*"&$A $79&"*")*($C$8:$C$74="*"&E$99&"*"))/$E$94),0),"") A79 is a reference value that has an actual text value in it, E99 is a reference value that has a text value indirectly pulled into it (its another formula). K8-74 is a range of responses, C8-74 is a separate range of responses. E94 is the total responses overall. What I'm doing here is trying to find the matches between two variables in a bunch of interview responses. Because of how the responses work I need to be able to wildcard them for the value (some responses can have multiple entries, and I just need to search for the term). The problem is taht when I put in the wild card *s or the values I'm testing have multiple entries (so "a, b, c" and not just "a") the formula above ceases to function properly. Thoughts? Based on other formulas I'm using this SHOULD work, and all the references are solid (I've checked them about 20 times). Very frustrating... Am I missing something? I have other places where I do this same function without wildcards and it works, so I have to think it isn't that the comparison itself is invalid (i.e., it is comparing the text in A79 with the formula in E99, not the text value that gets pulled into E99). Any help is greatly appreciated. Thanks, Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with wildcard in complex formula... help?
On Mar 12, 7:06 pm, "Bob Phillips" wrote:
=IF(AND($A$79<"",E$99<""),IF($E$94<0, SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))* (ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"") Thanks for the corrected formula man, really appreciate it. Found another couple of threads in the meantime that discussed how wildcards cannot be used with SumProduct arrays... so frigging annoying. Okay, so this introduces another wrinkle, maybe you can help? My original formula was geared for two dimensions, but I had planned on trying to make it a three dimensional formula for some other calculations I need to do. The introduction of Find (which I believe only works in 2D) would appear to put a wrinkle in this plan... So the question: is there a way to make this formula work in three dimensions? Thanks again for the help. -Rick |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with wildcard in complex formula... help?
On Mar 12, 7:57 pm, wrote:
On Mar 12, 7:06 pm, "Bob Phillips" wrote: =IF(AND($A$79<"",E$99<""),IF($E$94<0, SUMPRODUCT((ISNUMBER(FIND($A$79,$K$8:$K$74)))* (ISNUMBER(FIND(E$99,$C$8:$C$74)))/$E$94),0),"") Thanks for the corrected formula man, really appreciate it. Found another couple of threads in the meantime that discussed how wildcards cannot be used with SumProduct arrays... so frigging annoying. Okay, so this introduces another wrinkle, maybe you can help? My original formula was geared for two dimensions, but I had planned on trying to make it a three dimensional formula for some other calculations I need to do. The introduction of Find (which I believe only works in 2D) would appear to put a wrinkle in this plan... So the question: is there a way to make this formula work in three dimensions? Thanks again for the help. -Rick Anyone? A little help? -rt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Look Up Problem | Excel Worksheet Functions | |||
Somewhat complex timesheet problem. | Excel Worksheet Functions | |||
COMPLEX PROBLEM | Excel Discussion (Misc queries) | |||
Complex Problem | Excel Worksheet Functions | |||
Wildcard Problem.... | Excel Discussion (Misc queries) |