Data Analysis in Excel

How do URLs encode spaces?

%20

Stages of the flowchart for what we use Excel for

1) Understand the business problem 2) Get the data 3) Explore the data 4) Prepare the data 5) Analyse the data 6) Present the findings

What is the syntax for the AND() function?

=AND(logical_test1, logical_test2, ...) This is TRUE if all arguments evaluate to TRUE or FALSE if one or more arguments evaluate to FALSE

Give an example of the AVERAGEIF() function

=AVERAGEIF(M:M, S2, I:I)

What is the syntax of the AVERAGEIF() function?

=AVERAGEIF(range, criteria, [average_range])

Give an example for the AVERAGEIFS() function

=AVERAGEIFS(I:I, M:M, S2, P:P, "successful")

What is the syntax for the AVERAGEIFS() function?

=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

What is the syntax of the COUNT() function?

=COUNT(value_range)

What is the syntax of the COUNTA() function?

=COUNTA(value_range)

What is the syntax of the COUNTBLANK() function?

=COUNTBLANK(value_range)

Give an example of the COUNTIF() function

=COUNTIF(M:M, S2)

What is the syntax of the COUNTIF() function?

=COUNTIF(range,criteria)

Give the generic syntax for all the arithmetic functions

=FUNCTION(data_range)

What is the syntax for the IF() function?

=IF(logical_test, [value_if_true], [value_if_false]) where logical_test is the criteria you want to test value_if_true is the value that you want returned if the result is TRUE value_if_false is the value that you want returned if the result is FALSE

What is the syntax of the LEFT() or RIGHT() function?

=LEFT(text, number_of_characters)

What is the syntax for the LEN() function?

=LEN(text) e.g. LEN(B2)

What is the syntax for the LOWER() function?

=LOWER(text1)

What is the syntax for the OR() function?

=OR(logical_test1, logical_test2, ...) This is TRUE if any of the arguments evaluate to TRUE and FALSE if all the arguments evaluate to FALSE

What is the syntax for the PROPER() function?

=PROPER(text1)

What is the syntax for the ROUND() function?

=ROUND(VALUE(text), decimal places) e.g. = ROUND(VALUE(L2), 0)

What is the syntax of the substitute function?

=SUBSTITUTE(text, "old_text", "new_text", [instance]) e.g. =SUBSTITUTE(B2, ":", "_")

Give an example of the SUMIF() function

=SUMIF(M:M, S2, I:I)

What is the syntax of the SUMIF() function?

=SUMIF(M:M, S2, I:I)

Give an example of the UNIQUE() function

=UNIQUE(M2:M104)

What is the syntax of the UNIQUE() function?

=UNIQUE(array)

What is the syntax for the UPPER() function?

=UPPER(text1)

What is the syntax for the VALUE() function?

=VALUE(text) e.g. VALUE(L2)

Example of VLOOKUP explained

=VLOOKUP(F2, 'Date Tables'!D:E, 2, FALSE) where 'Date Tables' is the name of the tab where we have the month numbers and the matching month names; ! - automatically added by excel - shows that the table array is from another tab the D:E is the column range from the table array. FALSE looks for exact matches

How do you calculate a project length?

=deadline-launched_at e.g. E2-D2

What is the AVERAGEIF() function?

A function that calculates the average of values in a range that match criteria you specify

What is the COUNTIF() function?

A statistical function that counts the number of cells within a range that meet the given condition and that has two arguments — the range of cells to check and the criteria.

What function can you use instead of AVERAGEIF() if you have multiple criteria (but would still work for one criterion)?

AVERAGEIFS() function

How can you test multiple criteria at once?

By nesting AND() or OR() in the arguments of IF()

What is the syntax of the CONCATENATE function?

CONCATENATE(value1, [value2],...) e.g. CONCATENATE(A2,B2)

What does the PROPER() function do?

Capitalises the first letter of each word

What two forms can date data be treated as?

Continuous or Discrete

How can you use VLOOKUP() to convert month numbers to month names?

Create a tab with a data table showing how the month numbers and month names line up. And then use "Date Table" or the name of the tab as the table array argument in VLOOKUP()

What is the date data type?

Dates and times in an accepted date-time representation.

What is the syntax of the EXACT() function?

EXACT(value1, value2) This compares the cells and says TRUE or FALSE depending on whether they are the same. You can have more values.

What does the MONTH() function do?

Extracts only the month (as a numeric value by default with January = 1 and December = 12 etc.) from a date column

What does the WEEKDAY() function do?

Extracts only the weekday (as a numeric value by default) from a date column

What is the syntax for the FILTER() function?

FILTER(range, criteria, [if_empty]) The [if_empty] argument is an optional argument on what to do if a cell is empty. e.g. FILTER(C2:C104, C2:C104, >1000)

CMD/CTRL + F

Find a value

What does the MAX() function do?

Finds the maximum from a data range

What does the AVERAGE() function do?

Finds the mean

What does the MEDIAN() function do?

Finds the median

What does the MIN() function do?

Finds the minimum from a data range

When do you want to use discrete data?

For when you want to analyse date groups e.g. by month, day or quarter etc.

What are functions in Excel?

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order

CMD/CTRL + G

Go to a specific cell

What is the SUMIF() function?

It allows you to add cells that meet a certain criterion

What does the CONCATENATE function do?

Joins two text strings together in a new cell. e.g. First Name and Last Name concatenated to Full Name.

CMD/CTRL + Arrow Key

Jump to the next blank cell in that direction

What is the boolean data type?

Logical or TRUE/FALSE.

What is the syntax of the SORT() function?

SORT(array, [sort_index], [sort_order] e.g. SORT(B2:B104, 1, -1) The sort_index is the column number to sort by. The sort_order: 1 is A to Z or smallest to largest. -1 is Z to A or largest to smallest.

What is the text data type?

Strings, numbers or dates represented in a text format.

What is the syntax of the TRIM() function?

TRIM(value)

What are Excel's default/preset alignments for each data type?

Text - left aligned Logical - centre aligned Numeric/Date-time - right aligned

Name the four data types in Excel?

Text, Number, Date, Boolean

What does the substitute function do?

The SUBSTITUTE function looks for a specified string within a cell and replaces it with the new text.

What does the SUM() function do?

The SUM() function is used for addition

What does the COUNTA() function do?

This counts all non-blank cells in a range regardless of data type

What does the COUNTBLANK() function do?

This counts all the blank cells in a range

What does the COUNT() function do?

This counts the number of cells that contain numeric values in a range

What does the FILTER() function do?

This function filters which values to consider.

What does the SORT() function do?

This function sorts values alphabetically.

What does the UNIQUE() function do?

This lists all the unique values from a range e.g. if you have a randomised data set which includes a column on countries - some appearing multiple times - it will list all the countries

What does the TRIM() function do?

This replicates the value in a previous cell but without a gap at the start of the cell.

How do you use the LEN() function in combination with other functions to extract only the portion of text we want?

Use as part of a nest with LEFT() or RIGHT() e.g. =LEFT(text, number_of_characters) or more specifically =LEFT(B2, LEN("3D My Kids"))

How do you count characters?

Use the LEN() function to find out the length

The UNIQUE() function will list the unique values in the order they are come across in the array. How do you list them alphabetically instead?

Use the SORT() function

As id columns look like numbers they may be set as such but as you are not using them as values or for arithmetic, what can you do to set the data type to string?

Use the TEXT() function with syntax =TEXT(value,format)

How are optional arguments represented in Excel?

Using square brackets

How do you convert a text string to a number data type?

VALUE() function

What does the VLOOKUP() function do?

VLOOKUP() allows you to incorporate data from another dataset and plug it into the working dataset

What is the syntax for VLOOKUP()?

VLOOKUP(lookup_value, table_array, col_num, [range_lookup]) where lookup_value is what you want to look up table_array is where you want to look for it col_num is the column number in the range containing the value to return range_lookup - an optional argument - checks if there is and approximate match (if you enter argument as TRUE) or an exact match (if you enter the argument as FALSE)

What does VLOOKUP() stand for?

Vertical Lookup

What is the syntax of the WEEKDAY() function?

WEEKDAY(date, [return_type]) return_type is an optional argument which can be used to set which day to start with. By default it starts with Sunday (1) so if you want to start the week with Monday you have to put 2

What are nested formulas?

When a function is used as part of another function e.g. =SORT(FILTER(range, criteria), [Sort_index], [Sort_order] Filter is executed first and then the results are sorted.

Why is it helpful to combine VLOOKUP() and MONTH() in a nested formula?

Whenever possible it is best to combine functions together to limit the number of columns in your dataset

How can you improve the CONCATENATE function?

You can create different variations e.g. CONCATENATE(A2, "-", B2) connects the id and name with a dash in between.

How do you convert date columns into discrete groups?

You should extract the relevant part of the date into a new column e.g. for MONTH or WEEKDAY

What does the LEFT() function do?

You use this to extract the first x characters in a cell. e.g. =LEFT(M2, LEN(M1)-7) removes the last 7 letters and extracts the rest before

What does the RIGHT() function do?

You use this to extract the last x characters in a cell. e.g. =RIGHT(M2, LEN(M1)-7) removes the first 7 letters and extracts the left.

What does the LOWER() function do?

Puts everything in lower case.

What does the UPPER() function do?

Puts everything in upper case.

What is the syntax of the MONTH() function?

MONTH(date)

The NOW() and TODAY() functions can both be used to know how many days ago a project ended. (NB no arguments needed at all) e.g. NOW()-E2. What is the difference between the two functions?

NOW() returns the exact date and time whereas the TODAY() function only returns the date

What is the number data type?

Numeric values.