Introduction to data analysis using Excel
Reading data into excel:
- Text formats: comma separated, tab delimited, fixed width
>> ctrl+ down arrow > to see number of rows (ctrl+ up arrow to go back)
>> shift+ ctrl+ right > to select the whole row
>> view > freeze panes
>> SUMIF > to calculate something based on a specific criterion (1-range where criteria
exist.2-‘’what criteria’’?.3-data range where to calculate sum)
>> COUNT > to count the number of elements in a data range
>> MAX/MIN > to find the maximum/minimum value of a data range
>> COUNTIF > to count the number of elements in a data range based on certain conditions
(condition put in “ ‘’) (range of data,
>> POWER > to calculate a number raised to the power of some other numbers (be tavan
resandan!)
(the arguments that a function takes!)
Fixed referencing > referring to a certain cell- using two $
>> if fixing a certain row or column number > put one $ in front of it! >> this way you define the
rules in one cell and apply it to the whole table!
Relative referencing
The IF command:
=IF( logical_test, value_if_true, value_if_false) >> what test to do, what to do if the test is true, what
to do if the test is false
=IF(C2=” ”, , )
Select the cell (as the logical test or the criteria), what to do if it’s met, what to do if it’s not met)
>> if numerical data >> no “”
Nested IF command:
=IF( logical_test, value_if_true, IF( , , ))
=IF(C2="Critical", "***", IF(C2="High", "**", "*"))
=IF(FALSE, 1, IF(TRUE, 2, 3))
FALSE > is not true, right (1 should return if FALSE is true which is not! Then if it’s not true >> second
IF: if true >> return 2
IF (something), then if YES do this, if NO do that.
=IF(AND(B2="Truck", A2>100), "Large Truck Order", "Regular Order")
=IF(B2="Truck",
IF(A2>100,
"Large Truck Order",
"Small Truck Order"),
"Non-Truck Order")
=IF(OR(I2="Extra Large",I2="XX Large",I2="XXX Large"),0.01,0)
=VLOOKUP(I2,$L$6:$M$15, 2, FALSE)
VLOOK UP function:
VLOOKUP connects information from two columns based on a matching value.
Create a VLOOK UP table within your spreadsheet
1st column > values that I wish to match or look up in my data (example: order priority)
2nd column > based on these values, how do I want to rate them or define them or … (here:
how many stars)
Important: the VLOOK UP table should be sorted ascending based on the first column
Tell Excel where to look for data that you want to match >> second column is where Excel
looks for the matching data
Once it finds what you are looking for (in this example: Critical), it will show the
corresponding value in the second column, then put True to find approximate match and
FALSE for an exact match.
How VLOOKUP works (with FALSE)
When the last argument is FALSE, Excel:
1. Searches for an exact match
2. Looks only in the first column of the table (column A)
3. Returns the value from column 2
4. Stops at the first match it finds
VLOOKUP is used to search for a value in one column and return related information from another
column.- It searches vertically down a column.
Think of it like: “Find this item, and tell me something about it.”
=VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])
lookup_value: What you want to find
table_array: Where to search
column_index_number: Which column to return from
range_lookup: Exact match (FALSE) or approximate (TRUE)
VLOOKUP = first match only
FILTER + INDEX = complete control (filters and then bring back the one you want, the second one, 3rd
one, or how many? :D)
Filter > returns all the items
HLOOK UP (horizontal):
=HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])
RANDBETWEEN(bottom, top) >> to randomly generate a number between two data
=VLOOKUP(RANDBETWEEN(1,20), table_array, column index_number, [range_lookup])
Press F9 > randomly generate a new number
XLOOK UP > replacing both!
- Works vertically and horizontally
- No column index numbers
- Can look left or right
- More reliable
- Cleaner formula