-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathData Analysis Functions
192 lines (114 loc) · 8.93 KB
/
Data Analysis Functions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
------------------------------------- Deleting Rows with missing values ------------------------------------
Step 1: Select whole data
Step 2: Click "Find & Select" in the "Home" Tab
Step 3: Click "Go to Special" in the "Find & Select" Dropdown and choose "Blanks"
Step 4: Click "Delete" in "Home" tab and then click "Delete Sheet Rows"
------------------------------------- Filling missing values with any value e.g "0" ------------------------------------
Step 1: Select whole data
Step 2: Click "Find & Select" in the "Home" Tab
Step 3: Click "Go to Special" in the "Find & Select" Dropdown and choose "Blanks"
Step 4: write 0 in blank cell and then press Ctrl+Enter to auto fill 0 in all other missing cells
OR
Step 1: Select whole data
Step 2: Click "Find & Select" in the "Home" Tab
Step 3: Click "Replace"
Step 4: Write value in "Replace with" and leave "Find" empty
------------------------------------- Filling missing values using Formulas ------------------------------------
1) By Average of value above and below the missing value
Step 1: Select whole data
Step 2: Click "Find & Select" in the "Home" Tab
Step 3: Click "Go to Special" in the "Find & Select" Dropdown and choose "Blanks"
Step 4: write formula "=AVERAGE(CELL_ABOVE, CELL_BELOW)" in blank cell and then press Ctrl+Enter to auto fill average value in all other missing cells
2) By Carrying forward the value above missing cell
Step 1: Select whole data
Step 2: Click "Find & Select" in the "Home" Tab
Step 3: Click "Go to Special" in the "Find & Select" Dropdown and choose "Blanks"
Step 4: Write the value of above cell e.g "=F3" and then press Ctrl+Enter to auto fill the above value in all other missing cells
------------------------------------- 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.
If we want to search a value against some keyword. For example we want to search a price of a particular product
FORMULA:
=VLOOKUP(KEYWORD_CELL,RANGE_OF_DATA,COLUMN_INDEX_OF_DATA_IN_RANGE,FALSE)
EXAMPLE : =VLOOPUP(F2,A1:D17,4,FALSE)
NOTE: THE KEYWORD COLUMN MUST BE TO THE LEFT OF THE DESIRED RESULT
------------------------------------- HLOOKUP Function ------------------------------------
HLOOKUP (Horizontal Lookup) is an Excel function to lookup and which retrieves data from a specific row in a table. It searches for a value in the table's first row and returns
another value in the same column from a row according to the given condition.
It is similar to VLOOKUP but it is used when data is in horizontal table. Same Syntax
FORMULA:
=HLOOKUP(KEYWORD_CELL,RANGE_OF_DATA,COLUMN_INDEX_OF_DATA_IN_RANGE,FALSE)
------------------------------------- XLOOKUP Function ------------------------------------
It can look up both vertically and horizontally and perform an exact match (default), approximate (closest) match, or wildcard (partial) match
Regardless of which side the return column is on. NO NEED FOR KEYWORD TO BE ON THE LEFT SIDE
FORMULA:
=XLOOKUP(KEYWORD_CELL,ARRAY_OF_ALL_KEYWORDS,ARRAY_OF_ALL_RESULTS)
=XLOOKUP(J4,C2:C20,F2:F20)
------------------------------------- COMBINING CELL VALUES ------------------------------------
1) AMPERSAND
=C3&"--"&D3
2) CONCATENATE / CONCAT
=CONCATENATE(O3:O21,"--",F3:F21)
------------------------------------- "LEN" FUNCTION ------------------------------------
LEN returns the length of text as a number.
LEN works with numbers, but number formatting is not included.
LEN returns zero if a value is empty.
FORMULA:
=LEN(C2)
=LEN("HELLO")
------------------------------------- "SUMIFS" FUNCTION ------------------------------------
The SUMIFS function calculates the sum of a range based on one or more true or false condition.
SYNTAX:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
FORMULA:
=SUMIFS(ARRAY/COLUMN_OF_INTEGERS_TO_BE_SUMMED, ARRAY/COLUMN_OF_KEYWORDS, KEYWORD_CELL)
=SUMIFS(F2:F20,B2:B20,R3)
=SUMIFS(S12:S20, T12:T20, "=B*", R12:R20, "To?")
Using wildcard characters like the question mark (?) and asterisk (*) in criteria1,2 can help you find matches that are similar but not exact.
A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk,
type a tilde (~) in front of the question mark.
Difference between SUMIF and SUMIFS.
The order of arguments differ between SUMIFS and SUMIF. In particular, the sum_range argument is the first argument
in SUMIFS, but it is the third argument in SUMIF. This is a common source of problems using these functions.
NOTE: The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.
------------------------------------- "IFERROR" FUNCTION ------------------------------------
Function to trap and handle errors in a formula. IFERROR returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the
formula.
The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
For example, if we divide 8 by 0, it gives an error, so we can handle this by specifying any value
Formula:
=IFERROR(8/0,0)
=IFERROR(C3,0)
------------------------------------- "SUBSTITUTE" & "REPLACE" FUNCTION ------------------------------------
--> Substitutes new_text for old_text in a text string
--> Use SUBSTITUTE when you want to replace specific text in a text string;
--> Use REPLACE when you want to replace any text that occurs in a specific location in a text string.
SUBSTITUTE SYNTAX:
=SUBSTITUTE(text, old_text, new_text, [instance_num (optional)]) #If you specify instance_num, only that instance of old_text is replaced
(means if the instance_num is 3, replace the value occuring third time). Otherwise, every occurrence of old_text in text is changed to new_text.
REPLACE SYNTAX:
REPLACE(old_text, index_number, how_many_characters_to_replace, new_text)
------------------------------------- "MINIFS" & "MAXIFS" & "AVERAGEIFS" FUNCTION ------------------------------------
some of these functions are only available in excel 2019 or 365
MINIFS:
Used to find a minimum value out of multiple values of a same category. For Example, if a same mobile is sold for different prices, this function will tell
the minimum price that it is sold for
MAXIFS:
Used to find a maximum value out of multiple values of a same category. For Example, if a same mobile is sold for different prices, this function will tell
the maximum price that it is sold for
AVERAGEIFS:
Used to find a average value out of multiple values of a same category. For Example, if a same mobile is sold for different prices, this function will tell
the average price that it is sold for
FORMULA:
=MINIFS(Column_to_find_min_from, Category_column, Criteria)
=MAXIFS(Column_to_find_max_from, Category_column, Criteria)
=AVERAGEIFS(Column_to_find_average_of, Category_column, Criteria)
------------------------------------- "COUNTIFS" FUNCTION ------------------------------------
This is used to find total count of occurance of a particular category
FORMULA:
=COUNTIFS(Criteria_range, Criteria)
------------------------------------- "LEFT" & "RIGHT" FUNCTION ------------------------------------
LEFT function helps to get a specified number of characters from a text from the left side.
=LEFT(text, number_of_characters)
RIGHT function helps to get a specified number of characters from a text from the right side.
=RIGHT(text, number_of_characters)