2001XLProficiencyWSolutions

2001XLProficiencyWSolutions - Excel Proficiency Exercises...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
Excel Proficiency Exercises With suggested solutions E XCEL R EVIEW 2001-2002
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
The best way to learn Excel is to use it. The best way to use Excel is on the job to solve a problem you need solving or by devising your own problems and finding solutions to them. This document includes practice exercises that illustrate features of the Excel software that are useful for modeling problems. Thanks to Decision Science Professor Laura Kornish for suggesting the exercises in this document. Try solving each problem on your own. If you need help, notes and suggested solutions are included. You may come up with solutions that use different techniques and that look a bit different from the ones here; your solutions may be completely valid! Excel offers many ways to accomplish the same thing. Paula Ecklund Spring 2001
Background image of page 2
Contents Page 1. Multiplication Table Problem. ....................................................................... 1 Relative, Absolute, & Mixed Addressing 2. Olive Oil Pricing Problem. ............................................................................ 6 IF Statements, SUMPRODUCT Function, MIN Function 3. Web Service Problem. ................................................................................. 13 Forecasting & Charting 4. Pro Forma Problem. .................................................................................... 19 Forecasting, Data Table, Goal Seek 5. Data Relationship Problem . ......................................................................... 28 Scatter or XY Plot
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
THIS PAGE INTENTIONALLY BLANK.
Background image of page 4
1 1. Multiplication Table Problem Relative, Absolute, and Mixed Addressing The Exercise Create a 10x10 multiplication table in a spreadsheet, as shown below. The cells inside the table (i.e., within the black border) should contain only formulas, not numbers. You should find it unnecessary to enter more than a single formula, which can be drag- copied to fill the rest of the table. ±1 ±2 ±3456789 1 0 ± 1 1 2 3456789 1 0 2 2 4 6 8 10 12 14 16 18 20 3 3 6 91 21 51 82 12 42 73 0 4 4 8 12 16 20 24 28 32 36 40 5 5 10 15 20 25 30 35 40 45 50 6 6 12 18 24 30 36 42 48 54 60 7 7 14 21 28 35 42 49 56 63 70 8 8 16 24 32 40 48 56 64 72 80 9 9 18 27 36 45 54 63 72 81 90 10 10 20 30 40 50 60 70 80 90 100 Notes The principle behind completing this multiplication table is simple. You want a formula in each cell of the table matrix that multiplies the value in that cell’s column header by that cell’s row header. The trick is to write a single formula (a “master formula”) that can be copied into all the matrix cells and is valid for each one. Solving this problem by writing a single formula requires that you understand Excel’s mixed addressing feature. Note that mixed addressing comes into play only when a formula is copied, as we’re doing here. So that’s the only time you need to concern yourself with it. Before you tackle mixed addressing, you should first understand Excel’s related addressing options: relative and absolute . Excel’s default is relative addressing. That is, cell references contained within a formula that’s copied are adjusted in the copy relative to their position in the spreadsheet. Fixed addressing is the opposite. As its name implies, a fixed reference, when copied as part of a formula, does not change.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/20/2011 for the course BUS 101 taught by Professor Ecklund during the Spring '01 term at Duke.

Page1 / 38

2001XLProficiencyWSolutions - Excel Proficiency Exercises...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online