SS.

JAIN SUBODH LAW &

MANAGEMENT COLLAGE

BY : ARYAN SINGH
MBA SEMESTER 1
UNDER : A/Prof MEENA SHUKLECHA
SUBJECT : M-110 DATA ANALYTICS LAB
Case Study No. 1

Q. What is Data Validation? How it works in Excel and why it’s important?
Explain custom validation rules by an example.

• What Is Excel Data Validation?

Data validation in Microsoft Excel is a feature that allows you to control the type of data entered into

a worksheet to improve the accuracy of the data.

Data Validation is a feature in Excel that controls what kind of data can be entered into a cell.

• It helps prevent mistakes.

• It ensures consistency in data entry.

• Example: If you only want numbers between 1 and 100, Data

Validation will block anything outside that range.

• How Its Work in Excel?

− Preparing the Dataset

We enter our data, such as a form with Name, Date of Birth, and Exam Centre columns, to apply
validation rules.
 − Applying Basic Validation

Step 1: Click on the Data Tab in the Ribbon.

Step 2: Now select Data Validation.

− After clicking on Data Validation, a menu appears.

Step 3: Select Data Validation and a dialogue box appear.

Data Validation also provides a feature of adding a drop-down list to a cell or group of cells.

− Follow the below steps to add a drop-down list:

Step 1: Select a cell in which we want to add the drop-down list.

Step 2: On the setting tab, Enter List in the Allow box.

Step 3: Type the Items of our Excel Validation list in the Source Box, Separated by commas
For example to limit the user input into two choices type Yes, NO.
Step 4: Now select the In-cell dropdown box in order for the dropdown arrow to appear next to the
cell.

Step 5: Press "OK"

• Why It’s Important?

Data validation is important in Excel because it prevents errors, ensures data accuracy and
consistency, and improves the usability of a spreadsheet by restricting what can be entered into
cells. This helps maintain data integrity, makes data reliable for analysis, and is particularly valuable
when multiple people are contributing to the same workbook.

 Key benefits of data validation:

1. Prevents data entry errors: It stops users from entering incorrect data types, values, or
formats, which helps avoid errors that could lead to miscalculations or inaccurate reports. For
example, you can prevent a user from entering text into a number field or a date outside of a
specific range.

2. Ensures data consistency: By setting specific rules, you ensure that data is entered
uniformly, even across multiple users. This is useful for things like creating consistent dropdown lists
for a "Department" column or standardizing date formats.

3. Improves data reliability: Validated data is more reliable for analysis and decision-making
because you can trust its accuracy. This is crucial for anyone who relies on spreadsheets for critical
business insights.

• Custom Validation Rules :

A custom validation rule in Excel uses a formula to apply a specific, unique rule to a cell or range,
allowing for complex conditions beyond simple data types.

 Example: Allow Only Numbers Greater

Than 50

Suppose you have a column where you want users to enter marks, but the rule is:

Only numbers greater than 50 are allowed.

Steps:

1. Select the cells

(e.g., column A).

2. Go to Data tab →
Data Validation →
Settings → Allow:
Custom.

3. Enter this formula:

=A1>50.

4. Click OK.
How It Works:

• If someone types 75 → Accepted

• If someone types 45 → Error message appears

• If someone types text like “ABC” → Error message appears

In short: You set the rule in Data → Data Validation → Custom → Formula, and Excel blocks any
entry that doesn’t meet the condition.
Case Study No. 2

Q. How to use various formulas of central tendency and

dispersion in Excel? Explain it by example.

Central tendency:
Central tendency describes the single value that best represents the center of a set of numbers. It
tells you what a “typical” or “middle” value looks like for the data.

You can find the central tendency in Excel using the AVERAGE, MEDIAN, and MODE functions, which
calculate the mean, median, and mode, respectively. To use them, type the function into a cell,
followed by a set of parentheses containing the cell range of your data.

Mean (Average) :

• What it is: The sum of all values divided by the count of values.

• Excel formula: =AVERAGE(range)

• Example: For a list of scores in cells A1 through A10, you would use =AVERAGE(A1:A10)

Median (Middle value) :

• What it is: The middle value in a dataset when it's arranged in order. If there's an even
number of values, it's the average of the two middle values.
 • Excel formula: =MEDIAN(range)

• Example: For a list of scores in cells A1 through A10, you would use =MEDIAN(A1:A10)

Mode :

• What it is: The value that appears most frequently in the dataset.

• Excel formula: =[Link](range) or

=[Link](range). Use =[Link] if there may be multiple modes, as it will return all
of them.

• Example: For a list of scores in cells A1 through A10, you would use
=[Link](A1:A10) or
=[Link](A1:A10)

Example :

Dataset :

A2: 5
A3: 7
A4: 7
A5: 10
A6: 12
Mean

• Formula: =AVERAGE(A2:A6)
• Explanation: Adds all values and divides by count.
• Result: 8.2

Median

• Formula: =MEDIAN(A2:A6)
• Explanation: Middle value when sorted.
• Result: 7

Mode

• Formula: =[Link](A2:A6)
• Explanation: Most frequent value.
• Result: 7

Dispersion Tendency:
Dispersion tendency in Excel measures how spread out your data is, with common measures being
range, variance, and standard deviation
Dispersion tells you how spread out the numbers in a dataset are — whether values cluster close to
the center or are widely scattered.

Key Excel functions for Dispersion:

• Range: The difference between the maximum and minimum values. o Formula:
=MAX(range) - MIN(range) o Example: For data in cells A1:A10, the
formula
would be =MAX(A1:A10) - MIN(A1:A10).

• Variance: The average of the squared differences from the mean. Use VAR.S for
sample data. o Formula: =VAR.S(range)

o Example: For data in cells A1:A10, the formula

would be =VAR.S(A1:A10).

• Standard Deviation: The square root of the variance, which shows how much
variation or dispersion there is in a dataset. Use STDEV.S for sample data.

o Formula: =STDEV.S(range)
o Example: For data in cells A1:A10, the formula
would be =STDEV.S(A1:A10).

• Quartile Deviation (or Interquartile Range): The difference between

the third quartile (Q3) and the first quartile(Q1).
o Formula: =[Link](range, 3) -
[Link](range, 1) o Example: For data in cells A1:A10, the
formula
would be =[Link](A1:A10, 3) -
[Link](A1:A10, 1)

Simple example:
 Dataset:
A2: 5
A3: 7
A4: 7
A5: 10
A6: 12

• Range: =MAX(A2:A6)-MIN(A2:A6) = 12 − 5 = 7

• IQR: [Link](A2:A6,3)-
[Link](A2:A6,1) = Q3=10, Q1=6 → 4

• Sample variance: =VAR.S(A2:A6) = ≈8.2

• Sample standard deviation: =STDEV.S(A2:A6) =

≈2.86
 Case Study No. 3

Q. How to Create graphs (Bar chart, Pie Chart, Pareto Diagram, Steam and
Leaf display etc.) in Excel. Explain by an example.

What is a Graph in Excel?

A Graph in Excel is a visual representation of data, often called a chart. It helps

you quickly see patterns, trends, and relationships in your data. Instead of
looking at rows and columns of numbers, a graph presents the information
using shapes like bars, lines, or pie slices, making it easier to understand and
compare.

How to Make a Graph in Excel?

Step 1: Select Your Data

Step 2: Use Recommended Charts

Step 3: Choose Your Chart

Step 4: Explore More Chart Options

Step 5: Customize Your Chart

Step 6: Use Chart Tools for Design and Formatting

1. Bar Graph

1. Select the range A1:C5

2. Click on the insert menu, then click on the bar menu ( ) and choose

Clustered Bar ( ) from the drop-down menu

You should get the chart below:

2. Pie chart

1. Select the range A1:B5

2. Click on the Insert menu, then click on the Pie menu ( ) and choose

Pie ( ) from the drop-down menu

You should get the chart below:
3. Pareto diagram
Plotting a Pareto diagram in modern versions of Excel is easy because it has a
built-in Pareto chart type. All you need to have is a list of items (issues, factors,
categories, etc.) in one column and their count (frequency) in another column.
As an example, we are going to do Pareto analysis of typical user complaints
about software based on this data set:
To make a Pareto graph in Excel, please follow these simple steps:

1. Select your table. In most cases, it is sufficient to select just one cell, and
Excel will pick up the whole table automatically.
2. On the Insert tab, in the Charts group, click Recommended Charts.
3. Switch to the All Charts tab, select Histogram in the left pane, and click
on the Pareto thumbnail.
4. Click OK.

That's all there is to it! The Pareto chart is immediately inserted in a

worksheet. The only improvement that you'd probably want to make is to
add/change the chart title:
4. Stem-and-Leaf style display
Following are the steps:

Step 1: Firstly, you need to sort the data. Select the data, B4:B14.

Step 2: Go to Home tab, under editing section, in Sort and Filter, select Sort Smallest to Largest.
Step 3: Sort Warning dialogue box appears. Select Continue with the Current Selection. Click on the
Sort button.

Step 4: The selected data got sorted.

Step 5: Add two new columns name Stem and Leaf.

Step 6: In cell C4, write the formula =[Link](B4/10). The formula divides the selected cell by
10 and changes it to its floor value.
Step 7: Press Enter. The required output is attained. For example, =[Link](10/10) is equal to
1.

Step 8: Current active cell is C4. Drag and drop from C4 to C14. The same formula gets copied in
C5:C14.
Step 9: In cell D4, write the formula =RIGHT(B4, 1). The formula gives the last character of cell B4.

Step 10: Press Enter. The required output is attained. For example, =RIGHT(10, 1) is equal to 0.

Step 11: Current active cell is D4. Drag and drop from D4 to D14. The same formula gets copied in
D5:D14.
Step 12: Add a new column in cell E3, name Leaf.

Step 13: In cell E4, write the formula =REPT("0",

COUNTIF($B$4:$B$14, C4*10 + 0)) & REPT("1",

COUNTIF($B$4:$B$14, C4*10 + 1)) & REPT("2",

COUNTIF($B$4:$B$14, C4*10 + 2)) & REPT("3",

COUNTIF($B$4:$B$14, C4*10 + 3)) & REPT("4", COUNTIF($B$4:$B$14, C4*10 + 4)) & REPT("5",
COUNTIF($B$4:$B$14, C4*10 + 5)) & REPT("6",

COUNTIF($B$4:$B$14, C4*10 + 6)) & REPT("7",

COUNTIF($B$4:$B$14, C4*10 + 7)) & REPT("8",

COUNTIF($B$4:$B$14, C4*10 + 8)) & REPT("9",

COUNTIF($B$4:$B$14, C4*10 + 9)). This formula might seem complicated but it's very easy.
Understand, the sub formula of it

i.e. =REPT("0", COUNTIF($B$4:$B$14, C4*10 + 0)). This sub formula simply counts the number of 10
in the data set and then =REPT() function repeats the number of 0's in it. Now, its easy, repeat the
same sub formula for all and then you will concatenate all possible number of repetitions of number
i.e. 10, 11, 12, 13....19.

Step 14: Press Enter. Copy the same formula to the range E4:E14.
Step 15: Your stem and leaf plot is ready. You can still do some customizations. You can hide column
D from your worksheet i.e. the first leaf name column. Right-click on Column D and click on the hide
button.

Step 16: Similarly, you can hide the same values in the stem column. Select the required rows and
right-click on them. Click on the hide button.
Step 17: Your Stem and Leaf plot is ready.
Case Study No. 4
Q. How to calculate correlation in Excel? Explain.

What is Correlation in Excel

Correlation is a statistical measure that describes the relationship between two

or more variables. It indicates how one variable change when another variable
changes. Correlation can help identify patterns or associations in data and is
used widely in fields such as finance, science, and social studies.

Types of Correlation

Positive Correlation: When one variable increases, the other variable also increases.
For example, as height increases, weight tends to increase.

Negative Correlation: When one variable increases, the other decreases. For
example, as the price of a product increases, the demand for it might decrease.

No Correlation: There is no relationship between the variables. For example, the

amount of rainfall and a person's age are unrelated.

What is the Correlation Coefficient in Excel?

The correlation coefficient is a numerical value ranging from -1 to +1 that

quantifies the strength and direction of the relationship between variables:
+1 indicates a perfect positive correlation.

-1 indicates a perfect negative correlation.

0 indicates no correlation.

Correlation helps in understanding relationships in data, making it a valuable

tool for data analysis and decision-making.

How to Calculate Correlation Coefficient in

Spreadsheets?

In general, if the correlation coefficient is close to -1 or +1 then we can say that

the bivariate data are strongly correlated to each other.

The correlation coefficient is calculated using Pearson's Correlation Coefficient

which is given by :

Using CORREL Function:

In this article, we are going to see how to find correlation coefficients in Excel.
Consider the following data set :
In Excel to find the correlation coefficient use the formula :

=CORREL(array1,array2) array1 : array of variable x array2: array of variable y

To insert array1 and array2 just select the cell range for both.
1. Let's find the correlation coefficient for the variables X and Y1.

Correlation coefficient of x and y1

array1 : Set of values of X. The cell range is from A2 to A6. array2 : Set
of values of Y1. The cell range is from B2 to B6.
Similarly, you can find the correlation coefficients for (X, Y2) and
(X, Y3) using the Excel formula. Finally, the correlation coefficients are as follows
:
From the above table we can infer that :

X and Y1 have negative correlation coefficient.

X and Y2 have positive correlation coefficient.
X and Y3 are not correlated as the correlation coefficient is almost
zero.