[Performing Data Manipulation]

(Data Blending)

What is the primary focus of data blending – using test-based functions to manipulate how data is displayed

Which function would you use to remove leading or trailing spaces from data – TRIM

What does the CLEAN function do – removes non-printable characters from data

If you want to display the first four characters of a cell in Excel, which function would you use – LEFT

Which function would you use to extract the last four characters of a text string – RIGHT

How would you extract specific characters from the middle of a text string – use the MID function

What does the UPPER function do – converts all letters to uppercase

What is the purpose of the LOWER function in Excel – converts text to lowercase

If you want to capitalize the first letter of each word in a text string, which functions should you use – PROPER

Which function helps you determine the number of characters in a text field – LEN

What is the purpose of the CONCATENATE function in Excel – to merge two or more text fields into one

How can you ensure that there is space between concantenated first and last names – use the formula = CONCATENATE(A1,” “,B1)

(Parsing Strings)

What is the main purpose of parsing strings in data analysis – to organize text into structured data by splitting it into parts

Which of the following tools are used to extract characters from a string – LEFT,RIGHT,MID

Why might you need to use different menthods to parse a string – because text is often unstructured and variable in length

What is a delimiter, and why is it important in parsing strings – a character used to split text into parts such as tab, commas, or pipe

What is the most common delimiter used in text fields – comma (,)

What would happen if you used a space as a delimiter when parsing names – it would only work for names with two words (first and last names)

If you were parsing names from Latin America or the Middle East, why might using space as a delimiter not work well – people in these regions often have more than two names

In Power Query inside Excel, how can you split a column based on a delimiter – right-click the column and choose “Split Column”

What happens if the data is not in the proper format when using a delimiter to split text – you may end up with partially split data, requiring manual cleanup

What is a potential issue when parsing names in regions where people have multiple names – the delimiter will incorrectly split the names into too many columns

(Date Manipulation)

What does the NOW function in Excel return – the current date and time

What is the primary difference between the NOW and TODAY functions in Excel – TODAY returns the current date, while NOW returns the current date and time

Which function would you use to calculate the numebr of days between two dates – DATEDIFF

What does the NETWORKDAYS function calculate – the total number of business days between two date, exluding weekends

Which of the following functions returns a number from 1 to 7 to represent the day of the week for a given date – WEEKDAY

What is the purpose of the WEEKNUM function – it calculates the week number of the year for a given date

What does the MONTH function in Excel return – a value from 1 to 12 representing the month of the year

How does the WEEKDAY function handle the days of the week – it assigns a number from 1 to 7, where 1 is Sunday

When using NETWORKDAYS, which days are excluded from the calculation – Sat & Sun

Which of the following functions would you use to calculate the number of business days between two dates – NETWORKDAYS

(Conditional Logic)

What is the purpose of conditional logic in data analysis – to check if a condition is met and return a result based on it

Which of the following is an example of simple “IF” function – =IF(A1<50, “Fail”, “Pass”)

What does the “ISNULL” function check for in a data set – if a cell contains a null or empty value

What does the “AND” logical function do – it returns true if both conditions are true

What is the key difference between “AND” and “OR” functions – AND required both conditions true, while OR requires either condition true

Which function would you use to show students who either have not bought anything or bought something over a year ago – OR

How would you format an IF function in Excel to check if a student’s score > 80, “Pass”, “Fail”)

Which of the following would use the “AND” function – if student’s score is greater than 70 and less than 80, return a warning

What would happen if you use an “OR” function with two conditions and both conditions are false – the function returns false

Which function is useful for segmenting data and offering special deals or discounts based on certain conditions – OR

(Aggregation Functions)

What does the SUM function do in Excel – adds up all the values in a column to produce a total

Which function would you use to count the number of distinct records in a column – DISTINCT COUNT

What is the main difference between COUNT and DISCOUNT COUNT – COUNT counts all values, while DISTRICT COUNT counts unique values only

How does the AVERAGE function in Excel work – it adds up all the values and divides by the count of values

What does the MAX function do in Excel – it finds the largest value in column

Which function would you use to find the smallest value in a column – MIN

When would you use the DISTINCT COUNT function – to count unique entities, like students or customers

What does the AVERAGE function return – the mean value of a set of numbers

What would the MAX function be most useful for in a business setting – identifying the largest order placed on a given day

Which aggregation function would you use to calculate the total sales amount for the day – SUM

(System Functions)

What are system functions – functions packed with reporting/analysis tools to perform certain tasks

What are system functions important in reporting tools – they automate tasks like updating report details e.g., date and page number

What does using system functions help save in the context of generating reports – time and effort

What type of information can system functions help track and report on in a report – page numbers, last refresh date, and report name

How can system functions make reports easier to use for the readers – by automatically updating crucial details e.g., refresh date and page numbers

Why is it important to know when a report was run – to analyze the data with an understanding of its freshness

What would happen if system functions were not used in generating reports – the data would have to be updated manually, taking more time

What is one common feature that most data analytics and reporting tools offer with system functions – automatic calculation of report names, dates, and page numbers