chrisjwmartin
New Member
- Joined
- Jun 25, 2013
- Messages
- 3
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.
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.
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.
Code:
{=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.