CORREL formula with criteria
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 |
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",EDUCAT ION_LEVEL)) |
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 |
All times are GMT +1. The time now is 04:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com