R
rtilghman
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
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