AI for data analysis and spreadsheet tasks in AI for Everyone - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

How AI assists in data cleaning in spreadsheets

Which of the following best describes how AI can help clean data in spreadsheets?

A AI can automatically detect and correct inconsistent data entries by learning patterns in the data.

B AI replaces all manual data entry by generating random data to fill empty cells.

C AI only highlights errors but cannot suggest corrections or fix them.

D AI deletes all duplicate rows without checking if they are truly duplicates.

💡 Hint

Think about how AI learns from data patterns to improve quality.

AI uses pattern recognition to find inconsistencies and can suggest or apply corrections, improving data quality automatically.

🔍 Analysis

intermediate

Output of AI-generated summary for spreadsheet data

Given a spreadsheet with sales data, an AI tool generates this summary code snippet. What is the output?

AI for Everyone

sales = [120, 150, 100, 130, 170]
summary = {'total_sales': sum(sales), 'average_sales': sum(sales)/len(sales)}
print(summary)

A {'total_sales': 670, 'average_sales': 135.0}

B {'total_sales': 670, 'average_sales': 134.0}

C {'total_sales': 650, 'average_sales': 130.0}

D TypeError: unsupported operand type(s) for +: 'int' and 'list'

💡 Hint

Calculate the sum and average of the list values carefully.

The sum of the sales list is 670, and the average is 670 divided by 5, which is 134.0.

❓ data_output

advanced

Result of AI clustering on spreadsheet data

An AI model clusters customer data into groups based on spending. Given this code, what is the cluster assignment output?

AI for Everyone

import numpy as np
from sklearn.cluster import KMeans

X = np.array([[100, 200], [110, 210], [500, 600], [520, 610]])
model = KMeans(n_clusters=2, random_state=0).fit(X)
print(model.labels_)

A [0 0 1 1]

B [1 1 0 0]

C [0 1 0 1]

D ValueError: n_clusters must be less than or equal to the number of samples

💡 Hint

Look at how the points are grouped by proximity.

The first two points are close and assigned cluster 0, the last two are close and assigned cluster 1.

🔍 Analysis

advanced

Identify the error in AI data extraction code

What error does this AI-powered spreadsheet data extraction code produce?

AI for Everyone

data = {'Name': ['Alice', 'Bob'], 'Age': [25, 30]}
ages = data['age']
print(ages)

A TypeError: list indices must be integers or slices

B Output: [25, 30]

C NameError: name 'data' is not defined

D KeyError: 'age'

💡 Hint

Check the exact spelling and case of dictionary keys.

The key 'age' does not exist because dictionary keys are case-sensitive; the correct key is 'Age'.

🚀 Application

expert

Choosing AI method for spreadsheet anomaly detection

You want to detect unusual transactions in a large spreadsheet using AI. Which method is best suited for this task?

A Manually review each transaction row for anomalies without AI assistance.

B Use clustering to group transactions but ignore outliers as they are not important.

C Apply unsupervised anomaly detection algorithms like Isolation Forest without labeled data.

D Use a supervised classification model trained on labeled normal and anomalous transactions.

💡 Hint

Consider if labeled anomaly data is available or not.

Unsupervised methods like Isolation Forest detect anomalies without needing labeled examples, ideal for unknown anomaly patterns.

Practice

1. What is one main benefit of using AI for data analysis in spreadsheets?

easy

A. It quickly summarizes large amounts of data.

B. It replaces the need for any human input.

C. It makes spreadsheets run faster on old computers.

D. It automatically deletes unnecessary files.

Solution

1. Step 1: Understand AI's role in spreadsheets

   AI helps by analyzing and summarizing data quickly, saving time.

2. Step 2: Evaluate each option

   Only It quickly summarizes large amounts of data. correctly describes AI's benefit; others are incorrect or unrelated.

3. Final Answer:

   It quickly summarizes large amounts of data. -> Option A

4. Quick Check:

   AI benefit = quick data summary [OK]

Hint: Think about what AI speeds up in spreadsheets [OK]

Common Mistakes:

• Thinking AI replaces all human work
• Confusing AI with computer speed
• Assuming AI deletes files automatically

2. Which of these is the correct way to ask AI to create a chart from spreadsheet data?

easy

A. Sort the data alphabetically.

B. Delete all rows with empty cells.

C. Create a bar chart showing sales by month.

D. Calculate the sum of column A.

Solution

1. Step 1: Identify the request for chart creation

   Only Create a bar chart showing sales by month. asks AI to create a chart, specifying type and data.

2. Step 2: Check other options

   Options B, C, and D ask for data cleaning or calculation, not chart creation.

3. Final Answer:

   Create a bar chart showing sales by month. -> Option C

4. Quick Check:

   Chart request = Create a bar chart showing sales by month. [OK]

Hint: Look for the option mentioning chart creation [OK]

Common Mistakes:

• Choosing options about sorting or summing instead of charting
• Confusing data cleaning commands with chart requests

3. Given this AI command in a spreadsheet: "Show me the average sales per region." What kind of output should you expect?

medium

A. A list of average sales values grouped by each region.

B. A chart showing total sales over time.

C. A sorted list of all sales data.

D. A cleaned dataset with no missing values.

Solution

1. Step 1: Understand the AI command

   The command asks for average sales grouped by region, so output should reflect that.

2. Step 2: Match output to options

   A list of average sales values grouped by each region. matches the request; others describe different outputs.

3. Final Answer:

   A list of average sales values grouped by each region. -> Option A

4. Quick Check:

   Average sales per region = A list of average sales values grouped by each region. [OK]

Hint: Look for grouping and averaging in the output [OK]

Common Mistakes:

• Expecting a chart instead of a list
• Confusing sorting with averaging
• Thinking data cleaning is the output

4. You asked AI to "Remove duplicates from the sales data," but the output still has duplicates. What is the likely error?

medium

A. Duplicates were removed but new ones appeared automatically.

B. The AI command was unclear or incomplete.

C. The spreadsheet software does not support duplicate removal.

D. The AI deleted the wrong rows.

Solution

1. Step 1: Analyze the AI command issue

   If duplicates remain, the command might have been unclear or missing details.

2. Step 2: Evaluate other options

   Software usually supports duplicate removal; new duplicates don't appear automatically; AI deleting wrong rows is less likely without error.

3. Final Answer:

   The AI command was unclear or incomplete. -> Option B

4. Quick Check:

   Unclear command = duplicates remain [OK]

Hint: Check if the AI command was clear and specific [OK]

Common Mistakes:

• Blaming software limitations without checking command
• Assuming duplicates appear automatically
• Thinking AI deletes wrong data without error

5. You want AI to clean a spreadsheet by removing empty rows, fixing date formats, and summarizing total sales by month. Which AI command best combines these tasks?

hard

A. "Sort sales data alphabetically and create a pie chart."

B. "Delete all data and start a new spreadsheet."

C. "Calculate average sales and highlight top 10 values."

D. "Clean empty rows, standardize dates to YYYY-MM-DD, then show total sales per month."

Solution

1. Step 1: Identify tasks in the command

   The tasks are removing empty rows, fixing date formats, and summarizing sales by month.

2. Step 2: Match tasks to options

   Only "Clean empty rows, standardize dates to YYYY-MM-DD, then show total sales per month." includes all these tasks clearly combined.

3. Final Answer:

   "Clean empty rows, standardize dates to YYYY-MM-DD, then show total sales per month." -> Option D

4. Quick Check:

   Combined cleaning and summary = "Clean empty rows, standardize dates to YYYY-MM-DD, then show total sales per month." [OK]

Hint: Look for option combining cleaning and summarizing tasks [OK]

Common Mistakes:

• Choosing options with unrelated tasks
• Missing the date format fixing step
• Ignoring the summary by month requirement