Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Category | Data 1 | Data 2 | Data 3 | Data 4 | Data 5 |

|----------|--------|--------|--------|--------|--------|

| A        | 10     | 15     | 20     | 25     | 30     |

| B        | 5      | 10     | 15     | 20     | 25     |

| C        | 8      | 12     | 16     | 20     | 24     |

Task :

1. Calculate the mean and standard deviation for each category using AVERAGE and STDEV functions.
2. Use CORREL function to determine the correlation between Data 1 and Data 2.
3. Perform a t-test using T.TEST function to compare the means of Data 3 and Data 4.
4. Use FREQUENCY function to create a frequency distribution table for Data 5.
5. Create a box plot to visualize the distribution of data points for each category.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

 

Step To Solve:

1.       Calculate Mean and Standard Deviation for Each Category

To calculate the mean (average) and standard deviation for each category using Excel functions:

·         Mean (Average): Use the AVERAGE function:

=AVERAGE(range)

Example for Category A (Data 1 to Data 5):

=AVERAGE(B2:F2)

Drag this formula across for Categories B and C.

·         Standard Deviation: Use the STDEV function:

=STDEV(range)

Example for Category A (Data 1 to Data 5):

=STDEV(B2:F2)

Drag this formula across for Categories B and C.

17. Determine Correlation between Data 1 and Data 2

To determine the correlation between Data 1 and Data 2 using the CORREL function:

=CORREL(B2:B4, C2:C4)

This formula calculates the correlation coefficient between Data 1 (B2

) and Data 2 (C2).

18. Perform a T-test to Compare Means of Data 3 and Data 4

To perform a T-test to compare the means of Data 3 and Data 4 using the T.TEST function:

=T.TEST(Data 3 range, Data 4 range, 2, 1)

• Data 3 range: Range of cells containing Data 3 (e.g., D2).
• Data 4 range: Range of cells containing Data 4 (e.g., E2).
• 2: Specifies a two-tailed distribution (testing if means are not equal).
• 1: Assumes equal variances for the two samples.

19. Create a Frequency Distribution Table for Data 5

To create a frequency distribution table for Data 5 using the FREQUENCY function:

Assume you want to create bins (intervals) for Data 5:

• Define bins in a column (e.g., G2).
• Use the FREQUENCY function to count how many values fall within each bin:

=FREQUENCY(Data 5 range, bins)

Example:

=FREQUENCY(F2:F4, G2:G6)

Enter this as an array formula by pressing Ctrl + Shift + Enter.

20. Create a Box Plot to Visualize Data Distribution for Each Category

To create a box plot to visualize the distribution of data points for each category:

• Select the range of data for each category (e.g., B2

for Category A).

• Go to Insert > Recommended Charts > Box and Whisker.
• Adjust settings to ensure each category's data is properly grouped and represented.