CORREL formula with criteria

[waxwing]

I need a simple way to create a set of correlations based on a
criteria. For instance, lets say column A is STATE, column B is INCOME
and column C is EDUCATION LEVEL. There are thousands of records but
only 50 states. To calculate the overall correlation between INCOME
and EDUCATION LEVEL, I'd use the formula CORREL(INCOME, EDUCATION
LEVEL). But I need to calculate the correlation for each state in a
table. How could I incorporate this into my formula?

Thanks.

- John

 

[Harlan Grove]

waxwing wrote...

I need a simple way to create a set of correlations based on a
criteria. For instance, lets say column A is STATE, column B is INCOME
and column C is EDUCATION LEVEL. There are thousands of records but
only 50 states. To calculate the overall correlation between INCOME
and EDUCATION LEVEL, I'd use the formula CORREL(INCOME, EDUCATION
LEVEL). But I need to calculate the correlation for each state in a
table. How could I incorporate this into my formula?

Try the array formula

=CORREL(IF(STATE="XY",INCOME),IF(STATE="XY",EDUCATION_LEVEL))

 

[waxwing]

Awesome, works like a charm. Are you aware of any good resources on
array formulas? It seems like they are frequently the solution to my
problems but I don't quite understand the logic. For instance, I never
would have thought of putting an IF statement in this formula.

- John