|
Step-by-Step: Test Your Sheet
|
|
|
What you will learn:
|
to test fit to screen
to test with easy numbers
to test with special numbers
to document the sheet
|
Start with:
trips34.xls (saved in
previous lesson)
Test Fit to Screen: 640 x 480
If your monitor is using a resolution than larger 640 x 480, you cannot tell by looking at
your screen if your layout will fit at 640 x 480. You need to either change
resolutions on your monitor, or resize the browser window to the resolution you
want to check. Use one of the following to see how your sheet fits:
- Switch resolution with your video card software: Some video cards
allow you to switch the resolution of the monitor without having to reboot the
computer. If yours does, you are in good shape! The choice is often in a popup
menu from an icon in the Tray at the right of the Windows taskbar. Otherwise,
check out Display Properties (right click on the Desktop and choose Properties).
The Settings tab will show what resolutions you are set up to use. However, you
may have to reboot to change the resolution. It's not worth doing that. Use
another method instead.
- Switch resolution with other software: There are other programs
that let you switch the monitor resolution, like the Power Toy QuickRes.
- Set browser size with software: Programs like Browser Sizer
open a browser window for you that is a particular size, like 640 x 480. Very
handy!
- Set browser size manually: Make an image that is the right size
and resize your window the same size.
Open Paint. Resize the blank
default canvas to 640 pixels wide and 480 pixels tall using Image | Attributes. (Pixels are called pels in the dialog.) Fill the blank white canvas with a bright color. Switch to Excel with the sheet you want to check as the active sheet.
Maximize the sheet in Excel's window. Do not have Excel itself maximized. Position the upper left corner of Excel's window on top of the colored image in Paint. Drag the edges of Excel's window until the whole window is the size the Paint image. Ta Da!
What you see in Excel's window is what you would see at 640 x 480 resolution
with your current toolbars showing. You can save your Paint image to use later
if you need to do this kind of check often.
If your sheet is too wide for the screen, what can you do about it?
Some possibilities:
- Reduce the width of one or more columns
- Reduce the font size and then the width of one or more columns
- Rearrange your sheet
Test Calculations: Easy Numbers
Your formulas look like they are working while using this "realistic" data. But it's hard to be sure. It would be easy to have dropped a zero or to leave out a part of a formula or accidentally stuck in an extra cell reference while clicking around. You need to check your formulas with numbers that are easy to work with. In fact, it would make a lot of sense to start out with really easy numbers and then test with realistic data later.
- Before you continue, make a note of Gardner's
numbers. You will need to put them back in after testing is done.
- Replace the Assumed Values: Use 10, 10%, 10%. Did
the concatenated labels update?
- Replace Data Values: Replace the #
Tickets Sold with 10, 10, 10 and the Sales of Other trips with
10000.
- Check Calculations: Widen column G to see the calculations. Since you used numbers that are easy to calculate with in your head, you can easily see that the Points and Total Points are working well. Only the Sales in G4 is
not
obviously right or wrong. Do a
little hand calculation to see if that is correct.
Tahiti = 10 tickets x $1500 = 15000
New Zealand = 10 tickets x $3000 = 30000
World = 10 tickets x $6000 = 60000
Other = 10000
Total = 15000 + 30000 + 60000 + 10000 = 115000. Correct!
Test Calculations: Special Numbers
Before you can say that your formulas are definitely
working right, you must check out some special situations. What happens if some of the numbers are not there? Will the spacing be big enough for the largest and smallest numbers likely to be used? Test it out!
- Zeros: One at a time, replace each of the data numbers in the Light Turquoise or Tan cells with a zero. (Do not forget to press
ENTER to actually enter the value.) Does the table still give good answers?
Undo between each change.
- Large/Small Numbers: Change the number of tickets sold for Tahiti to the
largest number you
think is likely to be sold. Are the cells for calculated values wide enough?
If not, widen the column. Try some other large values in different parts of the sheet.
At some point the Bonus and Total Commission will not fit in the
column. The decision to make is which is more important- keeping the tables on one screen at 640 x 480 resolution, or having the cells wide enough to show really large sales? It's up to you.
Are there formatting changes you could make to keep the tables on one screen and
on one printed sheet and have still wider columns? So many choices!
You should also test very small numbers, especially when negative numbers make sense on the sheet. This sheet would not use negative numbers, but the commission rate might be very small. What might be the smallest
percentage likely to be used for the commission and bonus rates?
How about small sale numbers? Try some to see if they are handled well by your formulas and cell sizes.
- Return the data to the values for Gardner. [Multiple Undo or
close without saving and re-open]
Documentation
Your spreadsheet is designed, formatted, and tested. All that remains is to document
what you did. Some documentation was done when you created titles and labels. Now you must create the Comments that will explain how the sheet works, who did it, etc.
- Main Comment: Select cell A1 and insert a Comment. Format the shape and color of the Comment's text
box to suit yourself.
In this comment include -
- your name
- date completed
- class for which
it was done
- Purpose
- Planning Spreadsheets steps 1 and 2 from the previous lesson Create a What If Sheet. (Of
course, your comments will not be exactly like the ones in the illustration.)
- Goals
- Output
- Input
- Formulas
- Design Considerations
- Other Comments: Select at least one cell containing a formula and create a comment that explains the formula in words rather
than cell references.
- Page Setup: Open File | Page Setup | Sheet and set Comments to
At the end so
that all comments will be printed on a separate page after the sheet prints.
- Header: Create a header with your name and the date on the left, the file name and sheet name in the
center, and Excel Project 5 on
the right.
- Spell Check. (This will check your comment text,
too!)
- Check the Print Preview.
-
Save
as trips35.xls to the excel project5
folder on your Class disk.
-
Print the
Bonus sheet only. You should get 2 pages, the bonus calculator and the comments.
