need help with formula generation for week 2-4 stats homework.

Data
    ID    Salary    Compa-ratio    Midpoint     Age    Performance Rating    Service    Gender    Raise    Degree    Gender1    Grade        Do not manipuilate Data set on this page, copy to another page to make changes
    1    57.7    1.012    57    34    85    8    0    5.7    0    M    E        The ongoing question that the weekly assignments will focus on is: Are males and females paid the same for equal work (under the Equal Pay Act)?
    2    27.9    0.899    31    52    80    7    0    3.9    0    M    B        Note: to simplfy the analysis, we will assume that jobs within each grade comprise equal work.
    3    36.2    1.168    31    30    75    5    1    3.6    1    F    B
    4    63.7    1.117    57    42    100    16    0    5.5    1    M    E        The column labels in the table mean:
    5    45.5    0.947    48    36    90    16    0    5.7    1    M    D        ID – Employee sample number             Salary – Salary in thousands
    6    74.4    1.110    67    36    70    12    0    4.5    1    M    F        Age – Age in years            Performance Rating - Appraisal rating (employee evaluation score)
    7    42.1    1.052    40    32    100    8    1    5.7    1    F    C        Service – Years of service (rounded)            Gender – 0 = male, 1 = female
    8    22.8    0.992    23    32    90    9    1    5.8    1    F    A        Midpoint – salary grade midpoint             Raise – percent of last raise
    9    77.7    1.159    67    49    100    10    0    4    1    M    F        Grade – jo
pay grade            Degree (0= BS\BA 1 = MS)
    10    23    0.998    23    30    80    7    1    4.7    1    F    A        Gender1 (Male or Female)            Compa-ratio - salary divided by midpoint
    11    21.8    0.949    23    41    100    19    1    4.8    1    F    A
    12    55.5    0.973    57    52    95    22    0    4.5    0    M    E
    13    40.5    1.012    40    30    100    2    1    4.7    0    F    C
    14    23.6    1.028    23    32    90    12    1    6    1    F    A
    15    23.4    1.016    23    32    80    8    1    4.9    1    F    A
    16    42.3    1.058    40    44    90    4    0    5.7    0    M    C
    17    67.5    1.184    57    27    55    3    1    3    1    F    E
    18    34.1    1.101    31    31    80    11    1    5.6    0    F    B
    19    24.3    1.056    23    32    85    1    0    4.6    1    M    A
    20    35.2    1.135    31    44    70    16    1    4.8    0    F    B
    21    79.1    1.181    67    43    95    13    0    6.3    1    M    F
    22    51.1    1.065    48    48    65    6    1    3.8    1    F    D
    23    25.3    1.101    23    36    65    6    1    3.3    0    F    A
    24    51    1.063    48    30    75    9    1    3.8    0    F    D
    25    23    1.000    23    41    70    4    0    4    0    M    A
    26    24.4    1.060    23    22    95    2    1    6.2    0    F    A
    27    42.9    1.073    40    35    80    7    0    3.9    1    M    C
    28    77.2    1.152    67    44    95    9    1    4.4    0    F    F
    29    77.5    1.157    67    52    95    5    0    5.4    0    M    F
    30    46.2    0.963    48    45    90    18    0    4.3    0    M    D
    31    22.7    0.985    23    29    60    4    1    3.9    1    F    A
    32    27.2    0.876    31    25    95    4    0    5.6    0    M    B
    33    62.9    1.104    57    35    90    9    0    5.5    1    M    E
    34    28.1    0.905    31    26    80    2    0    4.9    1    M    B
    35    23.9    1.038    23    23    90    4    1    5.3    0    F    A
    36    22.8    0.993    23    27    75    3    1    4.3    0    F    A
    37    22.6    0.984    23    22    95    2    1    6.2    0    F    A
    38    60    1.052    57    45    95    11    0    4.5    0    M    E
    39    33.5    1.081    31    27    90    6    1    5.5    0    F    B
    40    23    1.002    23    24    90    2    0    6.3    0    M    A
    41    42.1    1.052    40    25    80    5    0    4.3    0    M    C
    42    24.1    1.049    23    32    100    8    1    5.7    1    F    A
    43    74.5    1.112    67    42    95    20    1    5.5    0    F    F
    44    63.8    1.120    57    45    90    16    0    5.2    1    M    E
    45    48.9    1.018    48    36    95    8    1    5.2    1    F    D
    46    61.8    1.085    57    39    75    20    0    3.9    1    M    E
    47    61.5    1.079    57    37    95    5    0    5.5    1    M    E
    48    65.6    1.150    57    34    90    11    1    5.3    1    F    E
    49    54.9    0.963    57    41    95    21    0    6.6    0    M    E
    50    61.9    1.086    57    38    80    12    0    4.6    0    M    E
    ID    Salary    Compa-ratio    Midpoint     Age    Performance Rating    Service    Gender    Raise    Degree    Gender1    Grade
    1    57.7    1.012    57    34    85    8    0    5.7    0    M    E
    2    27.9    0.899    31    52    80    7    0    3.9    0    M    B
    4    63.7    1.117    57    42    100    16    0    5.5    1    M    E
    5    45.5    0.947    48    36    90    16    0    5.7    1    M    D
    6    74.4    1.110    67    36    70    12    0    4.5    1    M    F
    9    77.7    1.159    67    49    100    10    0    4    1    M    F
    12    55.5    0.973    57    52    95    22    0    4.5    0    M    E
Week 1
    Week 1: Descriptive Statistics, including Probability                                                                            ID    Salary    Compa-ratio    Midpoint     Age    Performance Rating    Service    Gender    Raise    Degree    Gender1    Grade
    While the lectures will examine our equal pay question from the compa-ratio viewpoint, our weekly assignments will focus on                                                                            3    36.2    1.168    31    30    75    5    1    3.6    1    F    B
    examining the issue using the salary measure.                                                                            7    42.1    1.052    40    32    100    8    1    5.7    1    F    C
                                                                                8    22.8    0.992    23    32    90    9    1    5.8    1    F    A
    The purpose of this assignmnent is two fold:                                                                            10    23    0.998    23    30    80    7    1    4.7    1    F    A
    1. Demonstrate mastery with Excel tools.                                                                            11    21.8    0.949    23    41    100    19    1    4.8    1    F    A
    2. Develop descriptive statistics to help examine the question.                                                                            13    40.5    1.012    40    30    100    2    1    4.7    0    F    C
    3. Interpret descriptive outcomes                                                                            14    23.6    1.028    23    32    90    12    1    6    1    F    A
                                                                                15    23.4    1.016    23    32    80    8    1    4.9    1    F    A
    The first issue in examining salary data to determine if we - as a company - are paying males and females equally for doing equal work is to develop some                                                                            17    67.5    1.184    57    27    55    3    1    3    1    F    E
    descriptive statistics to give us something to make a preliminary decision on whether we have an issue or not.                                                                            18    34.1    1.101    31    31    80    11    1    5.6    0    F    B
                                                                                20    35.2    1.135    31    44    70    16    1    4.8    0    F    B
    1    Descriptive Statistics: Develop basic descriptive statistics for Salary                                                                        22    51.1    1.065    48    48    65    6    1    3.8    1    F    D
        The first step in analyzing data sets is to find some summary descriptive statistics for key variables.                                                                         23    25.3    1.101    23    36    65    6    1    3.3    0    F    A
        Suggestion: Copy the gender1 and salary columns from the Data tab to columns T and U at the right.                                                                        24    51    1.063    48    30    75    9    1    3.8    0    F    D
        Then use Data Sort (by gender1) to get all the male and female salary values grouped together.                                                                        26    24.4    1.060    23    22    95    2    1    6.2    0    F    A
                                                                                28    77.2    1.152    67    44    95    9    1    4.4    0    F    F
        a.     Use the Descriptive Statistics function in the Data Analysis tab                                 Place Excel outcome in Cell K19                                    31    22.7    0.985    23    29    60    4    1    3.9    1    F    A
            to develop the descriptive statistics summary for the overall                                 Compa-ratio                                    35    23.9    1.038    23    23    90    4    1    5.3    0    F    A
            group's overall salary. (Place K19 in output range.)                                                                    36    22.8    0.993    23    27    75    3    1    4.3    0    F    A
            Highlight the mean, sample standard deviation, and range.                                Mean    1.0503                                37    22.6    0.984    23    22    95    2    1    6.2    0    F    A
                                            Standard E
or    0.0105903788                                39    33.5    1.081    31    27    90    6    1    5.5    0    F    B
                                            Median    1.054                                42    24.1    1.049    23    32    100    8    1    5.7    1    F    A
                                            Mode    1.052                                43    74.5    1.112    67    42    95    20    1    5.5    0    F    F
                                            Standard Deviation    0.0748852864                                45    48.9    1.018    48    36    95    8    1    5.2    1    F    D
        b.    Using Fx (or formula) functions find the following (be sure to show the formula                                 Sample Variance    0.0056078061                                48    65.6    1.150    57    34    90    11    1    5.3    1    F    E
            and not just the value in each cell) asked for salary statistics for each gender:                                Kurtosis    -0.4223958602                                1    57.7    1.012    57    34    85    8    0    5.7    0    M    E
                Male    Female                        Skewness    -0.2146189897                                2    27.9    0.899    31    52    80    7    0    3.9    0    M    B
            Mean:    1.041    1.059                        Range    0.308                                4    63.7    1.117    57    42    100    16    0    5.5    1    M    E
            Sample Standard Deviation:    0.0842781506    0.064594298                        Minimum    0.876                                5    45.5    0.947    48    36    90    16    0    5.7    1    M    D
            Range:    0.305    0.235                        Maximum    1.184                                6    74.4    1.110    67    36    70    12    0    4.5    1    M    F
                                            Sum    52.515                                9    77.7    1.159    67    49    100    10    0    4    1    M    F
                                            Count    50                                12    55.5    0.973    57    52    95    22    0    4.5    0    M    E
                                                                                16    42.3    1.058    40    44    90    4    0    5.7    0    M    C
                                                                                19    24.3    1.056    23    32    85    1    0    4.6    1    M    A
    2    Develop a 5-number summary for the overall, male, and female SALARY variable.                                                                        21    79.1    1.181    67    43    95    13    0    6.3    1    M    F
        For full credit, show the excel formulas in each cell rather than simply the numerical answer.                                                                        25    23    1.000    23    41    70    4    0    4    0    M    A
                    Overall    Males    Females                                                    27    42.9    1.073    40    35    80    7    0    3.9    1    M    C
                Max    1.184    1.181    1.184                                                    29    77.5    1.157    67    52    95    5    0    5.4    0    M    F
                3rd Q    1.1055    1.107    1.1065                                                    30    46.2    0.963    48    45    90    18    0    4.3    0    M    D
                Midpoint    1.054    1.056    1.052                                                    32    27.2    0.876    31    25    95    4    0    5.6    0    M    B
                1st Q    0.99675    0.968    1.005                                                    33    62.9    1.104    57    35    90    9    0    5.5    1    M    E
                Min    0.876    0.876    0.949                                                    34    28.1    0.905    31    26    80    2    0    4.9    1    M    B
                                                                                38    60    1.052    57    45    95    11    0    4.5    0    M    E
    3    Location Measures: comparing Male and Female midpoints to the overall Salary data range.                                                                        40    23    1.002    23    24    90    2    0    6.3    0    M    A
        For full credit, show the excel formulas in each cell rather than simply the numerical answer.                                                                        41    42.1    1.052    40    25    80    5    0    4.3    0    M    C
        Using the entire Salary range and the M and F midpoints found in Q2                            Male    Female                                        44    63.8    1.120    57    45    90    16    0    5.2    1    M    E
        a. What would each midpoint's percentile rank be in the overall range?                            ERROR:#N/A    ERROR:#N/A    Use Excel's =PERCENTRANK.EXC function                                    46    61.8    1.085    57    39    75    20    0    3.9    1    M    E
        b. What is the normal curve z value for each midpoint within overall range?                            0.7572    0.7505    Use Excel's =STANDARDIZE function                                     47    61.5    1.079    57    37    95    5    0    5.5    1    M    E
                                                                                49    54.9    0.963    57    41    95    21    0    6.6    0    M    E
                                                                                50    61.9    1.086    57    38    80    12    0    4.6    0    M    E
    4    Probability Measures: comparing Male and Female midpoints to the overall Salary data range
        For full credit, show the excel formulas in each cell rather than simply the numerical answer.
        Using the entire Salary range and the M and F midpoints found in Q2, find                            Male    Female
        a. The Empirical Probability of equaling or exceeding (=>) that value for                            1    0    Show the calculation formula = value/50 or =countif(range,">="&cell)/50
        b. The Normal curve Prob of => that value for each group                            0.8545158926    0.8536002252    Use "=1-NORM.S.DIST" function
    5    Conclusions: What do you make of these results?                    Be sure to include findings from this week's lectures as well.
        In comparing the overall, male, and female outcomes, what relationship(s) see, to exist between the data sets?
        I could not figure out how to get the formula to ork in 3A. What I am seeing overt the data sets is that the range on the female side is much smaller than that of the Males. With that it makes the femal mean acutuly higher than the males. After looking more into it the males midpoint and 3rd quarter is actuly higher than the females.
        What does this suggest about our equal pay for equal work question?
        Right now it is still unclear cause we have not looked into other varibles such as performance, grade, and raises. In regard to just basic descriptive statistics it looks as if there are some issues that need to be adressed.
Week 2
    Week 2: Identifying Significant Differences - part 1                                                                    ID    Salary    Compa-ratio    Midpoint     Age    Performance Rating    Service    Gender    Raise    Degree    Gender1    Grade
                                                                        1    57.7    1.012    57    34    85    8    0    5.7    0    M    E
    To Ensure full credit for each question, you need to show how you got your results. This involves either...