Excel,z-score,standard deviations

I would like to know if it is possible to calculate the # of standard deviations there is between 2 average salaries.

I have two groups of people and I know the total number in both groups and the total salary of both groups.
I also have the average salary for each group and the difference between the 2 average salaries.
Would I be able to get the number of standard deviations it is between the 2 average salaries?

For example:
Group 1 has 10 persons and their total salary is 100K.
Group 2 has 10 persons and their total salary is 200K.
Group 1's average salary is 10K.
Group 2's average salary is 20K.

Is there a way to calculate the number of standard deviations of the 10K average difference?

Thanks for any help.

MarvinP,

Thanks for your response.

I do have the salary data.

What I would like is Excel to calculate the spread between the average male and female salaries by department.

For example, in my data base:

Column "A" has the department number.
Column "B" has the salary
Column "C" has male or female

Each department may have 10 to 200 rows of data. So I want excel to calculate the spread (# of standard deviations) between male and female average salaries for each department automatically.

Can that be done.

Thanks.

MrShorty,

Thank you for the valuable information. I believe though, after using the different tool pak utilities that regression will work bet for what I need.

I recorded a macro to do that. In column "A" is the "Y" data and column "B" has the "X" data.

The problem is I recorded the macro using 50 rows of data and I need the macro to run however many rows of data I have when pasting new data.

I have posted my code below in case you can help me so that the macro will run with any number of rows of data.

Sub Macro8()
'
' Macro8 Macro
'
' Keyboard Shortcut: Ctrl+m
'
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$A$2:$A$50"), _
ActiveSheet.Range("$B$2:$B$50"), False, False, , "", False, False, False _
, False, , False
End Sub


In addition, although I don't think excel will do this, is it possible to have the code recognize the department number and run the regression by department?

Column A would be the department number.
Column B would be Y data
Column C would be the X data.

So if I had 5 different departments and each had from 10 to 100 rows of data the macro regression would run each department data separately.

Thank you