Data Analysis with Spreadsheet Lab File

Bachelor in business administration (Trinity Institute of Professional

Studies)

Scan to open on Studocu

Downloaded by Tanishq Bhati

Studocu is not sponsored or endorsed by any college or university

Downloaded by Tanishq Bhati

 Trinity Institute of Professional Studies
Affiliated To Guru Gobind Singh Indraprastha
University
SECTOR 16-C, DWARAKA, NEW DELHI

Subject
Data Analysis with Spreadsheet Lab
Practical file

Submitted By: Submitted to:

Name of Student :- Deepak Vashistha Name of Faculty: - Dr. Raj Kumar Garg
Enrollment Number :- 02820688821 Designation:- Associate Proffesor
Course :- B.Com (Hons) Semester :- IV
Shift : - Ist Year :- 2021-2024

1
Downloaded by Tanishq Bhati
 INDEX
Sr. No. Particulars Page No.
1 Aggregate Functions 3
2 Count Function 4
3 Sum Function 5
4 Average Function 6
5 Minimum Function 7
6 Maximum Function 8
7 IF Function 9
8 Nested If Function 10
9 Pivot Table 11
10 Vlookup Function 12
11 Sorting and Filtering 13
12 Absolute and Relative Reference 15
13 Column Chart 16
14 Line Chart 18
15 Pie Chart 19
16 Doughnut Chart 21
17 Bar Chart 21
18 Area Chart 22
19 Bubble chart 23

Downloaded by Tanishq Bhati

 AGGREGATE FUNCTIONS

An Aggregate Function in SQL performs a calculation on multiple values and

returns a single value. SQL provides many aggregate functions that include avg,
count, sum, min, max, etc. An aggregate function ignores NULL values when it
performs the calculation, except for the count function.

An aggregate function in SQL returns one value after calculating multiple values
of a column. We often use aggregate functions with the group by and having
clauses of the select statement.

In other words, aggregate functions return the same value each time that they are
called, when called with a specific set of input values.

Various types of SQL aggregate functions are:

➢ Count()
➢ Sum()
➢ Avg()
➢ Min()
➢ Max()

Downloaded by Tanishq Bhati

 COUNT FUNCTION

The COUNT function counts the number of cells that contain numbers, and counts
numbers within the list of arguments. Use the COUNT function to get the number
of entries in a number field that is in a range or array of numbers.

Syntax

COUNT(value1, [value2], ...)

Downloaded by Tanishq Bhati

 SUM FUNCTION

The SUM function adds values. You can add individual values, cell references or
ranges or a mix of all three.

Syntax
=SUM(number1,[number2],...)

Downloaded by Tanishq Bhati

 AVERAGE FUNCTION

Average, which is the arithmetic mean, and is calculated by adding a

group of numbers and then dividing by the count of those numbers .

Syntax

=AVERAGE(number1, [number2], ...)

Downloaded by Tanishq Bhati

 MINIMUM FUNCTION
This article describes the formula syntax and usage of the MIN function in
Microsoft Excel. It returns the smallest number in a set of values.

Syntax

MIN(number1, [number2], ...)

The MIN function syntax has the following arguments:

▪ Number1, number2, ... Number1 is optional, subsequent numbers are

optional. 1 to 255 numbers for which you want to find the minimum
value.

Downloaded by Tanishq Bhati

 MAXIMUM FUNCTION

This article describes the formula syntax and usage of the MAX function in
Microsoft Excel .It returns the largest value in a set of values.

Syntax

MAX(number1, [number2], ...)

The MAX function syntax has the following arguments:

▪ Number1, number2, ... Number1 is required, subsequent numbers are

optional. 1 to 255 numbers for which you want to find the maximum value.

Downloaded by Tanishq Bhati

 IF FUNCTION
The IF function is a premade function in Excel, which returns values based on
a true or false condition.

It is typed =IF and has 3 parts:

=IF(logical_test, [value_if_true], [value_if_false])

The condition is referred to as logical_test, which can check things like:

• If a number is greater than another number >

• If a number is smaller than another number <
• If a number or text is equal to something =

Downloaded by Tanishq Bhati

 NESTED IF FUNCTION
Nested IF functions, meaning one IF function inside of another, allow you to test
multiple criteria and increases the number of possible outcomes.

Syntax

=IF(logical_test, [value_if_true], [value_if_false])

10

Downloaded by Tanishq Bhati

 PIVOT TABLE

A pivot table is a table of grouped values that aggregates the individual items of a
more extensive table (such as from a database, spreadsheet, or business intelligence
program) within one or more discrete categories. This summary might include
sums, averages, or other statistics, which the pivot table groups together using a
chosen aggregation function applied to the grouped values.

11

Downloaded by Tanishq Bhati

 VLOOKUP FUNCTION

VLOOKUP stands for ‘Vertical Lookup’. It is a function that makes Excel search
for a certain value in a column (the so called ‘table array’), in order to return a
value from a different column in the same row.

Syntax: =VLOOKUP(VLOOKUP=VLOOKUP (lookup_value, table_array,

col_index_number, [range_lookup]),)

12

Downloaded by Tanishq Bhati

 SORTING AND FILTERING
You sort data to quickly organize your data and to find the data that you want and
filter data to display only the rows that meet criteria that you specify and hide rows
that you do not want displayed, for one or more columns of data.

For example

When we apply “ Sort and Filter” function on the data.

13

Downloaded by Tanishq Bhati

We obtained data of females by using “SORT” function on the data

14

Downloaded by Tanishq Bhati

 ABSOLUTE AND REALATIVE REFERENCE
There are two types of cell references: relative and absolute.

1. Relative References
2. Absolute references

They behave differently when copied and filled to other cells.

• Relative references change when a formula is copied to another cell.

• Absolute references, on the other hand, remain constant no matter where
they are copied

Absolute Reference

Relative Reference

15

Downloaded by Tanishq Bhati

 COLUMN CHART
Data that’s arranged in columns or rows on a worksheet can be plotted in a column
chart. A column chart typically displays categories along the horizontal (category)
axis and values along the vertical (value) axis, as shown in this chart:
Result of
Year Result of IXth Xth Result of XIth Result of XIIth
2013-14 78 98 90 98
2014-15 89 78 75 89
2015-16 94 97 86 79
2016-17 60 67 87 92
2017-18 82 98 94 91
2018-19 80 60 62 83
2019-20 70 93 90 79
2020-21 94 57 65 84
2021-22 91 87 98 93
2022-23 92 95 78 99

CLUSTERD COLUMN
Result
120

100

80

60

40

20

0
2013-14 2014-15 2015-16 2016-17 2017-18 2018-19 2019-20 2020-21 2021-22 2022-23

Result of IXth Result of Xth Result of XIth Result of XIIth

16

Downloaded by Tanishq Bhati

STACKED COLUMN

100%
90%
80%
70%
60%
50%
40% Result of XIIth
30% Result of
XIth Result
20% of Xth Result
10% of IXth
0%

2013
-14 2014
-15 2015
-16 2016
-17 2017
-18 2018
-19 2019
-20 2020
-21 2021
-22 2022
-23

17

Downloaded by Tanishq Bhati

 LINE CHART
Data that's arranged in columns or rows on a worksheet can be plotted in a line
chart. In a line chart, category data is distributed evenly along the horizontal axis,
and all value data is distributed evenly along the vertical axis. Line charts can show
continuous data over time on an evenly scaled axis, so they're ideal for showing
trends in data at equal intervals, like months, quarters, or fiscal years.
Year Result of IXth Result of Xth Result of XIth Result of XIIth
2013-14 78 98 90 98
2014-15 89 78 75 89
2015-16 94 97 86 79
2016-17 60 67 87 92
2017-18 82 98 94 91
2018-19 80 60 62 83
2019-20 70 93 90 79
2020-21 94 57 65 84
2021-22 91 87 98 93
2022-23 92 95 78 99

400
350
300
250
200
150
100
50
0
2013-14 2014-15 2015-16 2016-17 2017-18 2018-19 2019-20 2020-21 2021-22 2022-23

Result of IXth Result of Xth Result of XIth Result of XIIth

18

Downloaded by Tanishq Bhati

 PIE CHART
Data that's arranged in one column or row on a worksheet can be plotted in a pie
chart. Pie charts show the size of items in one data series, proportional to the sum
of the items. The data points in a pie chart are shown as a percentage of the whole
pie.
Year Result of IXth Result of Xth Result of XIth Result of XIIth
2013-14 78 98 90 98
2014-15 89 78 75 89
2015-16 94 97 86 79
2016-17 60 67 87 92
2017-18 82 98 94 91
2018-19 80 60 62 83
2019-20 70 93 90 79
2020-21 94 57 65 84
2021-22 91 87 98 93
2022-23 92 95 78 99

PIE CHART

2013-14 2014-15 2015-16 2016-17 2017-18

2018-19 2019-20 2020-21 2021-22 2022-23

19

Downloaded by Tanishq Bhati

 DOUGHNUT CHART
Data that's arranged in columns or rows only on a worksheet can be plotted in a
doughnut chart. Like a pie chart, a doughnut chart shows the relationship of parts
to a whole, but it can contain more than one data series.
Year Result of IXth Result of Xth Result of XIth Result of XIIth
2013-14 78 98 90 98
2014-15 89 78 75 89
2015-16 94 97 86 79
2016-17 60 67 87 92
2017-18 82 98 94 91
2018-19 80 60 62 83
2019-20 70 93 90 79
2020-21 94 57 65 84
2021-22 91 87 98 93
2022-23 92 95 78 99

Doughnut Chart
2013-14 2014-15 2015-16 2016-17 2017-18
2018-19 2019-20 2020-21 2021-22 2022-23

20

Downloaded by Tanishq Bhati

 BAR CHART
Data that's arranged in columns or rows on a worksheet can be plotted in a bar
chart. Bar charts illustrate comparisons among individual items. In a bar chart, the
categories are typically organized along the vertical axis, and the values along the
horizontal axis.
Year Result of IXth Result of Xth Result of XIth Result of XIIth
2013-14 78 98 90 98
2014-15 89 78 75 89
2015-16 94 97 86 79
2016-17 60 67 87 92
2017-18 82 98 94 91
2018-19 80 60 62 83
2019-20 70 93 90 79
2020-21 94 57 65 84
2021-22 91 87 98 93
2022-23 92 95 78 99

Bar Chart
2022-23
2021-22
2020-21
2019-20
2018-19
2017-18
2016-17
2015-16
2014-15
2013-14
0
50
100
150
200
250
300
350
400

Result of IXth Result of Xth Result of XIth Result of XIIth

21

Downloaded by Tanishq Bhati

 AREA CHART
Data that's arranged in columns or rows on a worksheet can be plotted in an area
chart. Area charts can be used to plot change over time and draw attention to the
total value across a trend. By showing the sum of the plotted values, an area chart
also shows the relationship of parts to a whole.
Year Result of IXth Result of Xth Result of XIth Result of XIIth
2013-14 78 98 90 98
2014-15 89 78 75 89
2015-16 94 97 86 79
2016-17 60 67 87 92
2017-18 82 98 94 91
2018-19 80 60 62 83
2019-20 70 93 90 79
2020-21 94 57 65 84
2021-22 91 87 98 93
2022-23 92 95 78 99

Area Chart

400

350

300

250

200

150

100

50

0
2013-14 2014-15
2015-16 2016-17
2017-18
2018-19 2019-20
2020-21 2021-22
2022-23

Result of IXth Result of Xth Result of XIth Result of XIIth

22

Downloaded by Tanishq Bhati

 BUBBLE CHART

Much like a scatter chart, a bubble chart adds a third column to specify the size of
the bubbles it shows to represent the data points in the data series.

Year Result of IXth Result of Xth Result of XIth Result of XIIth

2013-14 78 98 90 98
2014-15 89 78 75 89
2015-16 94 97 86 79
2016-17 60 67 87 92
2017-18 82 98 94 91
2018-19 80 60 62 83
2019-20 70 93 90 79
2020-21 94 57 65 84
2021-22 91 87 98 93
2022-23 92 95 78 99

Bubble Chart

120

100

80

60

40

20

0
0 2 4 6 8 10 12

Result of IXth Result of XIth

23

Downloaded by Tanishq Bhati