Chapter 3 Spreadsheet Design

Chapter 3 Spreadsheet Design - Chapter 3-Spreadsheet Design...

Info icon This preview shows pages 1–3. Sign up to view the full content.

Chapter 3-Spreadsheet Design DESIGNING & USING WORKBOOKS WITH MUTIPLE WORKSHEETS Thus far the workbooks we’ve used have contained only a single worksheet. As the sets of data inputs and calculations become more complex, the design of our workbooks will be critical to their ultimate usability and maintenance. This section will explore how to design your workbooks to be efficient and effective and how to execute workbook solutions requiring multiple worksheets. DESIGNING EFFICIENT/EFFECTIVE SPREADSHEETS This problem only required a simple single Excel worksheet. Frequently problems are more complex and require more and varied inputs, multiple calculations, and even multiple solution cases. What other techniques can be used to best solve complex problems using an Excel workbook? This section will look at some of the tools that are available to us in modern day spreadsheets, including the use of multiple worksheets, 3-D formulas, and a technique that allows us to work backwards to determine what input value is required for a desired output value. Before beginning any workbook, planning a design is critical to implementing an effective solution. Good planning will help avoid time consuming changes later. There are three main reasons why a spreadsheet solution may require changes: It is difficult to read and use. It does not allow additional variables to be easily included. It does allow values to be easily changed. The next few sections provide guidelines that can prevent a spreadsheet from having any of these three problems. LIST DATA INPUTS There are several concepts that should be taken into consideration when designing effective spreadsheets. One such concept, that has already been discussed previously, is the use of formulas that reference input values rather than use constants, i.e. “hard coded” values. Data should never appear more than once in a spreadsheet. In previous chapters we have seen how this allows us to easily make changes and updates. In the previous chapter we created a spreadsheet, like the one shown in Figure 1 , to calculate monthly travel costs based on the service fee shown in cell B1. If the Service Fee Page 1 Figure 1
Image of page 1

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

Chapter 3-Spreadsheet Design changes to 10%, we only need to change the value in cell B1 to 10%, and all affected values will be automatically updated. The user does not need to know specifically which cells are affected by the changes. This type of analysis, in which alternative values are substituted for data inputs, is known as a “what-if” analysis. A well designed spreadsheet will allow the user to vary one or more values to determine how they will affect the solution. VERTICAL VERSUS HORIZONTAL ORIENTATION Figure 2 displays a spreadsheet that lists two different job opportunities with varying wages and hours. The worksheet shows data organized with each category as a separate column and each job as a separate row.
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.
  • Fall '12
  • IssamSafa
  • Databases, Chapter 3-Spreadsheet Design

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern