1.
In Microsoft Excel, Perform the following tasks in dataset
(56,43,24,67,87,45,69,97,54,24,45,68,85,14,10,92)
a) Calculate highest score using MAX
b) Calculate lowest score using MIN
c) Calculate the average score using AVG
d) Find the total sum of score using SUM
e) Sort score in ascending order and display it in column B.
f) Find the square root of the total sum using SQRT
2. Using Microsoft Excel, find the Mean, Median, Mode and Standard deviation, Variance,
Skewness, Kurtosis for the given data and represent them in histogram
164,153,102,75,268,86,17,75,187,178,198,187,94
3. a) Import a Worksheet from One Workbook to Another in Excel
b) Export Excel data to CSV
c) Export ranges from Excel to CSV with any delimiter and any character encoding
4. In Microsoft Excel, normalize following data and display it in Bold, red colour.
Student Height(cm)
Kim 152
John 155
Mary 168
Jason 175
Hillary 153
Michael 163
Lisa 158
Rachel 156
Bob 161
Alice 150
Mike 170
5. Apply various transformations, queries to load data to data model in Power BI
Desktop
solutions.
6. Prepare data in Power BI Desktop using following tasks:
a) Set Power BI Desktop options
b) Get data from SQl server and CSV file
7. In Microsoft Excel, Perform bivariate and multivariate analysis using scatterplot,
correlation efficient, simple linear regression on the following dataset:
8. Create sales Exploration report in Power BI Desktop using following tasks:
a) Create scatter charts
b) Create visual to forecast values
c) Work with key Work with the key influencers visual
9. In Microsoft Excel,
a) Enter “welcome” message into sheet, format with Arial, Font, Bold and Blue color
Background
b) Calculate average of values (10, 20, 30, 40, 50, and 60) and display result in cell
B1.
c) Round average value using ROUND function and display result in cell B2.
10. Design report in Power BI Desktop with advanced design features such as Sync
slicers,
drillthrough page, conditional formatting, bookmarks.
11. Perform Z-Test, T-test in MS-Excel using TEST() function on given data set:
(Data1 for Z-Test and Data1 and Data2 for T-test)
12. In Microsoft Excel, Create student mark sheet which contains five subject marks,
total, average grade and remarks.
13. Create calculated tables, calculated columns, measures using Data Analytics
Expressions (DAX)
14. Design three page report and publish report to Power BI Desktop then interact
with report.
15. Create relationships between tables and then configure table, column properties
to improve friendliness and usability of data model in Power BI Desktop.
16. Create an Excel worksheet that contains [Link]. & Marks in 5 subject of a
student.
Calculate their grades as per the following :
Marks Grades
0-40 4
40-50 3
50-60 2
60 & above 1
17. Create an Excel worksheet that contains Names & Sale for 10 salesmen.
Calculate their bonus as per the following :
Sale Bonus
0-30000 0
30000-40000 3000
40000-50000 4000
50000-60000 5000
60000-70000 6000
70000-80000 7000
80000 & above 8000
18. Create an Excel worksheet that contains Customer No. , Number of units
consumed for
10 customers. Calculate their bill amount as per the following :
Number of units Rate
< 200 Rs. 3
>=200, < 500 Rs. 6
>= 500 Rs. 10
19. Create an Excel worksheet to Perform bivariate analysis on the dataset.
20. Generate an interactive report using Power BI Desktop application.
21. Create a dashboard and perform data analysis using Power BI Desktop
application.
22. From the student table given below, fetch Steve’s mark in English.
23. Given the below worksheet Write appropriate text functions in excel to calculate
first name, last name and email id.
24. In the following Excel worksheet Calculate the Maximum mark, minimum mark,
mean, median, standard deviation and variance for each subject.
25. A worksheet contains name and marks in 3 subjects . Calculate Total Marks.
a) Construct 3D Pie Chart for Total marks
b) Construct 2D Line Chart for Subject 1 and Subject 3
c) Construct 2D Column Chart for Sub1,Sub2,Sub3
d) Construct Stacked Column Chart for Sub1,Sub2,Sub3
26. Create an Excel worksheet that contains Name & Sales of 10 salesmen.
Calculate commission as per the following:
Sales Commission
First 30,000 5%
Next 40,000 10%
Excess 15%
27. Create an Excel worksheet that contains Roll Number, Marks in 2 subjects for 10
students in a class. Calculate Result and Grade using the following:
A student is declared as PASS if he gets 40 or more in both the subjects, Otherwise
FAIL.
All FAILED students will be given Grade IV.
For PASSED students Grade will be obtained as follows:
AVERAGE GRADE
>=60 I
<60 but >=50 II
<50 but >=40 III
28. In a class with 10 students, the medical check-up take place wherein they were
weighed, the following data w captured. Calculate the Mean, Median, Mode and
Standard deviation,
Variance, Skewness, Kurtosis of the data set based on the given information.
29. You have a dataset representing the number of customer complaints received by
a company each day for a month:
4, 2, 6, 5, 3, 4, 2, 5, 4, 3, 7, 4, 5, 2, 3, 4, 5, 6, 5, 4
a. Calculate the mean, median, and mode of the number of complaints.
b. Calculate the standard deviation and variance.
c. Determine the skewness and kurtosis of the distribution.
30. You have a dataset representing the ages of participants in a marathon:
32, 34, 35, 36, 38, 40, 42, 45, 50, 58
a. Calculate the skewness of the age distribution. Is it positively or negatively
skewed?
31. A pharmaceutical company is testing the effectiveness of four different drug
formulations to treat a specific condition. The data shows the improvement scores of
patients after treatment with each formulation:
Formulation 1: [12, 15, 14, 13, 16]
Formulation 2: [18, 17, 20, 19, 15]
Formulation 3: [10, 8, 12, 11, 9]
Formulation 4: [22, 23, 21, 24, 20]
Perform a one-way ANOVA to determine if there are significant differences in the
mean improvement scores among the four formulations.
32. A company wants to test if there is a significant difference in the average
productivity between two different departments. They collect data on the number of
tasks completed in a day for each department:
Department A: [24, 27, 30, 22, 26]
Department B: [31, 29, 34, 28, 32]
Perform an independent samples t-test to determine if there's a significant difference
in productivity between the two departments.
33. You have a dataset of exam scores for two subjects, "Math" and "English." The
scores are on different scales, making comparisons difficult. You want to normalize
the scores for each subject to a common scale between 0 and 1.
Load the dataset into Excel and calculate the mean and standard deviation for
both "Math" and "English" scores.
Using Excel formulas, normalize the scores for each subject to a scale between 0
and 1.
Provide the normalized dataset for both subjects.
34. Given a dataset with three features, follow these steps to perform PCA:
Create a dataset with the following data points:
Feature 1: [2, 3, 5, 7, 8]
Feature 2: [5, 4, 6, 8, 7]
Feature 3: [1, 2, 1, 3, 2]
1. Standardize the data (mean = 0, standard deviation = 1).
2. Calculate the covariance matrix.
3. Find the eigenvalues and eigenvectors of the covariance matrix.
4. Sort the eigenvalues in descending order and select the corresponding
eigenvectors.
5. Transform the data into the new feature space using the selected eigenvectors.
35. You work for a coffee shop and claim that the average wait time for customers is
less than 5 minutes. You take a random sample of 25 customer wait times and find
the following wait times (in minutes): 4.2, 4.5, 4.0, 4.8, 4.3, 4.6, 4.1, 4.7, 4.2, 4.4, 4.2,
4.9, 4.5, 4.3, 4.6, 4.2, 4.7, 4.0, 4.5, 4.1, 4.6, 4.3, 4.8, 4.2. Use Excel to perform a
one-sample Z-test to determine if the average wait time is less than 5 minutes with a
5% significance level.