Excel Optimization and Data Analysis Flashcards (45 cards)

All cards

1Question

How do you open the PivotTable creation dialog after converting source data to an Excel Table?

Answer

Choose Insert > PivotTable > From Table/Range > New Worksheet.

2Question

What does a PivotTable field placed in Rows display?

Answer

One row per item.

3Question

What does a PivotTable field placed in Columns display?

Answer

One column per item.

4Question

What can the Values area do with numbers?

Answer

Sum, count, or average them.

5Question

What does the Filters area let you display?

Answer

Selected data.

6Question

Which value-sorting order can PivotTable analysis apply?

Answer

Largest to smallest.

7Question

Which value filters can PivotTable analysis apply?

Answer

Top 10 or Top 5.

8Question

What must users do after PivotTable source data changes?

Answer

Refresh the PivotTable.

9Question

How are new rows included when PivotTable source data is an Excel Table?

Answer

They are included automatically.

10Question

What overall margin results from $280 profit on $1,000 sales?

Answer

28%.

11Question

What does Goal Seek find when running a model backwards?

Answer

One input value that makes a formula cell reach a specified target.

12Question

In Goal Seek, what must the Set cell contain?

Answer

A formula.

13Question

In Goal Seek, what does the To value specify?

Answer

The typed target number.

14Question

What does Goal Seek change to reach one target?

Answer

One input value.

15Question

Which Excel tool optimizes a best value or enforces limits?

Answer

Solver.

16Question

What is a Solver objective?

Answer

The single formula cell whose value is minimized or maximized.

17Question

Which cells are Solver variables?

Answer

Input cells that Solver may change to optimize the objective.

18Question

What do Solver constraints specify?

Answer

Rules that changing cells or model results must satisfy.

19Question

What distinguishes a binding constraint from a constraint with slack?

Answer

A binding constraint controls the design; a constraint with slack does not limit it.

20Question

What does a PivotTable do to rows and their values?

Answer

It groups rows and aggregates their values.

21Question

What can a PivotTable calculated field define using Profit and Sales?

Answer

A Margin ratio: Profit divided by Sales.

22Question

What does double-clicking a PivotTable value open?

Answer

The source rows behind it.

23Question

How do you fix a PivotTable showing Count instead of Sum for a numeric total?

Answer

Fix the blank or text source cell, then select Sum in Value Field Settings.

24Question

For the ball model, which valid time is nearest a starting guess of 1 second?

Answer

0.99 seconds, going up.

25Question

For the ball model, what is the other valid time for reaching 15 m?

Answer

3.09 seconds, coming down.

26Question

What maximum height does the ball model reach?

Answer

20.4 meters.

27Question

Which Solver method handles formulas with powers, products, or ratios?

Answer

GRG Nonlinear.

28Question

Which Solver method applies when all model relationships are linear?

Answer

Simplex LP.

29Question

When should integer constraints be added to a Solver model?

Answer

When a decision variable represents a whole item that cannot be produced fractionally.

30Question

Why should Solver models include minimum-size constraints for design dimensions?

Answer

Without them, Solver may choose zero or negative dimensions.

31Question

What should you compare when nonlinear Solver results vary with starting points?

Answer

Compare the objective values of candidate solutions.

32Question

Why can a feasible tall, thin beam still fail in real life?

Answer

Solver checks only entered limits, so the beam may buckle sideways.

33Question

What happens to a relative reference such as A1 when filled?

Answer

It adjusts.

34Question

What happens to an absolute reference such as $A$1 when filled?

Answer

It stays locked.

35Question

What does a mixed reference such as $A1 lock?

Answer

Only the column.

36Question

How can IF, MIN, and MAX be used in spreadsheet pricing models?

Answer

They can model tiered pricing.

37Question

In the tiered pricing example, how many initial hours are free?

Answer

10 hours.

38Question

What formula calculates the charge after 10 free hours?

Answer

=max⁡(0,hours−10)×rate=\max(0,\text{hours}-10)\times\text{rate}

39Question

Which chart type should be used for measured data?

Answer

An XY Scatter chart.

40Question

Where should bar-chart axes begin?

Answer

At zero.

41Question

What should chart axis labels specify?

Answer

The quantity and its unit.

42Question

What can a fitted trendline display?

Answer

Its equation and R².

43Question

Why is predicting beyond measured data risky?

Answer

It extrapolates beyond the measured range.

44Question

Which match modes can XLOOKUP use?

Answer

Exact match or next-larger match mode 1.

45Question

What can cause an XLOOKUP #N/A error?

Answer

Typos, spaces, or numbers stored as text.

Test yourself with the quiz

Answer 17 questions on Excel Optimization and Data Analysis.

1. You need to build a PivotTable from a new dataset and place it on a separate sheet. Which setup sequence should you use?

2. You want a PivotTable to show one row per region, one column per product, and total sales for each combination. Where should each field go?

Take the quiz →

Read the study guide

Go over the full study guide for Excel Optimization and Data Analysis.

Read the study guide →

Related classes

Make your own flashcards

Upload your class notes and Revizly writes your flashcards in about a minute.

Flashcard maker