Statistics - trying to figure out how much one variable affects another variable

[NathanielPrime]

I've done several seemingly more advanced tasks in excel, but I wanted to help a local charity out by analyzing the effect of one variable (generally between 0 and 30) on another variable (between 0 and 4). I -know- this should be easy to do. I have tens of thousands of samples, and I have used the CORREL function to show that there is indeed a moderately strong relationship between the two variables.

What I'd really think would help them, though, is being able to say "for every 1 point the first variable increases, the second variable will be .05 lower on average". This has to be possible, yes? I just can't for the life of me figure out/remember how to do it. Anyone know what formula or technique I'm looking for?

 

[mirabeau]

I've done several seemingly more advanced tasks in excel, but I wanted to help a local charity out by analyzing the effect of one variable (generally between 0 and 30) on another variable (between 0 and 4). I -know- this should be easy to do. I have tens of thousands of samples, and I have used the CORREL function to show that there is indeed a moderately strong relationship between the two variables.

What I'd really think would help them, though, is being able to say "for every 1 point the first variable increases, the second variable will be .05 lower on average". This has to be possible, yes? I just can't for the life of me figure out/remember how to do it. Anyone know what formula or technique I'm looking for?

Regression.

Can use Excel's Linest function or the data analysis regression add-in or various ad-hoc regression codes on this forum and elsewhere on the internet.

If you want to make more out of the limited variation in your variables, although I suspect it won't make much difference to you, there are specialized extensions such as probit or logit models. In fact there's a large literature on the topic.

I suspect a straight linear regression would be your best approach.

 

[NathanielPrime]

Thank you very much!