STDEV(IF(... with Contains ["*"&A1&"*"] condition returns #DIV/0!

[chrisjwmartin]

Hello all, and thanks in advance for your help.

I have an STDEV array formula in cells D12:J16 to calculate the standard deviation of a column of the separate RawData sheet. The RawData sheet contains a row of variable names, and then 1000 rows of data. The particular variable column that I am looking at contains in each cell a string of various words, e.g. "Word1", "Word2,Word3", "Word6,Word4,Word5", etc. The STDEV array formula refers to: cells D10:J10, which contain the variable name; cells D11:J11, which contain the word being looked up ("Word1" to "Word6"); and cells A12:A16, which contain the variable name of the column to be calculated.

I am using Excel 2010. I know to CSE.

{=STDEV(IF(OFFSET(INDIRECT(ADDRESS(2,MATCH(D$10,RawData!$1:$1,0),,,"RawData")),0,0,1000,1)="*"&D$11&"*",
OFFSET(INDIRECT(ADDRESS(2,MATCH($A12,RawData!$1:$1,0),,,"RawData")),0,0,1000,1)))}

When I use just D$11 as the condition instead of "*"&D$11&"*", the formula works and provides me with 1.34, but this is only examining the cases where "Word1" is the only thing in the string. I want to include cases such as "Word2,Word1,Word4", i.e. a Contains condition, but the "*" approach, which works for other formulae, returns #DIV/0! for me.

Have I made a mistake somewhere? Is there something I need to do to get this to work?

I cannot provide my worksheet for confidentiality reasons, but if you have any other questions to which you need the answer, please let me know.

 

[chrisjwmartin]

Anyone got any thoughts?

 

[chrisjwmartin]

Any thoughts?

 

[Jonmo1]

Welcome to the board.

You can't use wildcards in a standard comparison like A=B

So instead of
IF(OFFSET(INDIRECT(...))="*"&D$11&"*",...

You have to do
IF(ISNUMBER(SEARCH(D$11,OFFSET(INDIRECT(...)))),...