1.
Create a Mark list as shown below and calculate the Total, Percentage,
Average, Maximum and Minimum by applying appropriate
formulas/function. Use Vlookup for Grade and appropriate function for
Rank.
Sl
Nam Sub Sub Sub Tot Percenta Resu Ran Grad
N
e 1 2 3 al ge lt k e
o
1
2
3
4
5
6
7
8
9
1
0
Avg
Max
Min
2. Enter at least ten details for the below Table. Use appropriate string
Function to get the full name. Use Data validation to enter the marks
between 0 to 20. If you enter the values anything else, it should
F ir s t Nam e
display a message invalid value.
3. TODAY, NOW, YEAR, MONTH, NETWORKDAYS, EOMONTH . Explain
these functions with an example
4. LEFT, MID, RIGHT, LEN, SUBSTITUTE, SEARCH, ISNUMBER. Explain
these functions with an example.
5. Create the following Table in excel format and solve subquestions
ID Name Department Salary
101 Amitha HR 50000
Manjunat
102 h Finance 60000
103 Raj Kumar IT 55000
104 Abishek Finance 58000
105 Shruthi HR
106 Krishna Production 55500
107 Adithya 70000
108 Harshitha Production 65000
109 Mithun Production 80000
110 Deepu IT 65000
a. Arrange the names in Ascending Order
b. Display only Production department
data
c. Use count and counta for Department and Salary column after the last record
d. Highlight top 3 salary
6. Create anexcel file and perform the following
Region Produc Salesperso Quantit Sales
t n y Amoun
t
North Laptop Alice 5 5000
South Laptop Bob 3 3000
East Tablet Charlie 7 3500
West Tablet David 2 2000
North Phone Alice 4 4000
South Phone Bob 6 6000
East Laptop Charlie 8 8000
West Tablet David 1 1500
North Phone Alice 3 3000
South Laptop Bob 5 5000
a. Show total Sales Amount by Region.
b. Show total Quantity sold by Product.
c. Which Product generated the highest Sales Amount?
d. Add slicers and filters for dynamic analysis
Python
7. In a bag, there are 10 red, 8 blue, and 12 green marbles. If you pick
one marble at random, what is the probability of picking a red marble?
8. A factory produces 100 light bulbs per hour, and each bulb has a 5%
chance of being defective. What is the probability that exactly 3 out of
100 bulbs in an hour will be defective? Use binomial Distribution.
9. The average height of adult men in a certain country is 70 inches with a
standard deviation of 3 inches. What is the probability that a randomly
selected man is between 67 and 73 inches tall? (Normal Distribution)
10. A researcher wants to test if there is a significant difference in average
scores among students taught by three different teachers. The scores of
students in each class are as follows:
Teacher A: [85, 89, 88, 92, 87]
Teacher B: [78, 81, 79, 74, 82]
Teacher C: [90, 94, 93, 89, 91]
Test if there is a significant difference in the mean scores between the
classes. Use ANOVA
11. A company collects data on advertising expenditure (in thousands of
dollars) and corresponding sales (in thousands of units). The data is as
follows:
Advertising Expenditure: `[10, 15, 20, 25, 30, 35, 40]`
Sales: ` [9, 11, 13, 15, 18, 20, 22]`
Fit a simple linear regression model to predict sales based on advertising
expenditure, check model adequacy, and make a prediction when the
advertising expenditure is $28,000.
12. A survey was conducted to see if there is an association between
gender and preference for a type of snack (Chips, Cookies, and Fruit).
The survey results are summarized as follows:
Chips Cookie Fruit
Male 20 15 25
Female 30 35 25
Use a Chi-Square Test of Independence to check if snack preference is
independent of gender.
13. A researcher collects data on the number of study hours and the scores
of students on a test. The data is as follows:
Study Hours: [2, 3, 5, 6, 8, 9, 10, 12]
Scores: [25, 30, 47, 50, 60, 65, 78, 85]
Visualize the data using a scatter plot, calculate the correlation coefficient
between study hours and scores, and fit a simple linear regression line.
Power BI
14. Write the steps to - Get Started with Power BI - Sign up for Power BI -
Create an excel file with five data fields and 10 records and upload it as CSV
file
15. Create a Dash board in Power BI using the available sample data