BUSI2001 Chap.1 Data Analytics and Data Wrangling
Data Analytics and Data Wrangling
Business analytics asks four escalating questions of data: what happened, why it happened, what is likely to happen and what a manager should do. Before any of them can be answered, raw records must be wrangled into a usable shape.
This chapter works through the first workshop's Excel techniques: deleting rows that hold blanks, placeholder codes or text in numeric columns, converting values with IF and IF(OR) statements, banding firms by size with nested IF logic, and summarising cleaned data with PivotTables set to the right aggregation.
It also covers prompting a generative AI assistant for a cleaning formula and then verifying what that formula did to each column, since a fast formula can spread a slow mistake. The cleaning habits built here carry straight into the Business Data Analysis, where every finding depends on rows you can vouch for.
What this chapter covers
- 01
Descriptive analytics
- 02
Diagnostic analytics
- 03
Predictive analytics
- 04
Prescriptive analytics
- 05
Missing and placeholder values
- 06
IF and IF(OR) statements
- 07
Nested IF size bands
- 08
PivotTable averages
Worked example · free
Matching four business questions to the four types of analytics
- +1Question (a) summarises past results by group, so it is descriptive analytics.
- +1Question (b) seeks the cause of a pattern already observed, so it is diagnostic analytics.
- +1Question (c) estimates a future outcome from historical records, so it is predictive analytics.
- +1Question (d) recommends an action and weighs its consequences, so it is prescriptive analytics.
Key terms
- Diagnostic analytics
- Analysis that investigates why an observed pattern or outcome occurred.
- Nested IF
- An Excel formula that places IF statements inside one another to return one of several outputs.
- Placeholder value
- A code such as 99 or 999 entered in place of missing data that must not be treated as real.
- Paste values
- An Excel paste option that keeps a formula's results while discarding the formula itself.
- Cross-tabulation
- A table that counts or summarises one variable within the categories of another.
Data Analytics and Data Wrangling FAQ
Why delete whole rows instead of single cells?
In the workshop data, a row with a missing or unusable value in any feature cannot support coherent analysis across all features, so the exercise filters each column for blanks, text codes and placeholders and removes the entire row. On the practice sheet this removes 130 rows. Record how many rows you drop, because removal changes the population you describe.
How does IF(OR) differ from a plain IF?
A plain IF tests one condition and returns one of two outputs. Wrapping OR inside the test lets either of several conditions trigger the true output, such as flagging a company that covers more than 250 employees or operates in manufacturing. Nested IFs go further and return one of many outputs.
Can generative AI write my cleaning formulas?
Yes, the workshop demonstrates prompting an AI assistant for a formula that converts non-numeric entries to zero while keeping numbers. The responsibility for checking stays with you: test the formula on rows you understand, watch for numbers stored as text, and paste results as values before deleting helper columns.
Should a PivotTable show sums or averages?
It depends on the question. Sums suit totals such as revenue by region, while averages suit per-person measures such as age or satisfaction score, and counts suit questions about how many. In the workshop's loyalty exercise the comparison only makes sense as average age, because the groups differ greatly in size.
Assessment move
Rebuild the five cleaning tasks yourself on a small invented sheet of about twenty rows. Include deliberate traps: a blank, the text NA, a 999 code, a number typed as text and a value exactly on each IF threshold. For each task, predict what the formula should return before filling it down, then compare.
Write the nested IF for size bands twice, once with thresholds in ascending order and once in the wrong order, and observe how the second version mislabels firms. Next, build a PivotTable from your cleaned sheet and change the value field from Sum to Average and Count so you see how each answers a different question.
Finally, prompt a generative AI tool for one cleaning formula, state the column, the rule and the method in your prompt, and check its output against your manual version. In your weekly reflection, record one mistake the checks caught, because that habit is what the Business Data Analysis rewards when you explain your preparation steps.
When you are confident with the formulas, time yourself cleaning a fresh sheet from start to finish and note which step took longest; that step is where to practise next. Finally, write two sentences that move from a descriptive PivotTable finding to a diagnostic question, because that move is the start of every strong data analysis.
Working through Data Analytics and Data Wrangling in BUSI2001? Sia is AskSia’s AI Business Analytics tutor — ask any BUSI2001 Data Analytics and Data Wrangling question and get a clear, step-by-step explanation grounded in how BUSI2001 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.