CODEWITHSUNDEEP
excelexcel-formula
Sartorialist
Sartorialist

Reputation: 301

Excel Regression with Multiple Data Sets

I have a dataset of job titles, salaries, and experience levels. I want to regress salary on experience BY job title. (I.E. several different regression analyses, one for each job title). Any way to do this without separating or sorting the data? I think an array formula plus INTERCEPT and SLOPE might do it?

Upvotes: 0

Views: 256

Answers (2)

QuadB
QuadB

Reputation: 55

You could also use the data analysis toolpak, built-in regression tool.

Upvotes: 0

jblood94
jblood94

Reputation: 16981

You are correct. For single-term models, SLOPE, INTERCEPT, and RSQ as array formulas will do it:

=SLOPE(IF(Table1[Job Title]=E2,Table1[Salary]),IF(Table1[Job Title]=E2,Table1[Experience]))

Just change SLOPE to INTERCEPT or RSQ to get those values.

enter image description here

Upvotes: 1

Related Questions