OUTLINE – EXCEL
LECTURE
Dr.Don. Stierman - Spring, 2011
Basic point, click, drag with the mouse {demonstrate - hold down left mouse key}
Spreadsheet cell entries: label, number, or equation {=}{demonstrate each}.
Operators (arithmetic): + , - , * , / , ^ (Also: brackets in equations)
Cell address: Column (alphabetic), row (numerical).
Relative address reference (c1) vs. absolute address reference ($c$1). Note that copying an equation containing a relative cell address uses relative position to look for values used in the 'paste' cell or cells, while an absolute reference locks row, column or both. The $ precedes the row and/or column to be locked.
The equation =a2^b1 takes the number in cell A2 and raises it to the power of the exponent in cell B1. However, if I lock the column for the number and the row for the exponent, I can type the equation once into cell B2, copy the contents of B2 and then paste from the clipboard into cells B2 through F6. Try it! If you type the equation correctly, you save a lot of work. If you type the equation incorrectly, you can make many mistakes with a single stroke of you key. But remember: only someone bold enough to risk making a mistake will ever discover anything new.
| A | B | C | D | E | F | |
| 1 | 0 | 1 | 2 | 3 | 4 | |
| 2 | 1 | =$a2^b$1 | ||||
| 3 | 2 | |||||
| 4 | 3 | |||||
| 5 | 4 | |||||
| 6 | 5 |
Format numbers – how and why
Functions: f(x) {demonstrate Insert Function}
Note: trig functions require radians as arguments: there are 2p radians in a 360O circle.
Generate series of numbers {input first 2 values of series, click and drag - demonstrate}
NOTE: generate increasingly negative values; decimal series; etc., as well as integers.
Cut, copy and paste tools. Copy and paste equations {demonstrate}. When you paste an equation you paste the equation, not the value shown inthe cell.
Paste special - converting equations to values.
Insert and Delete columns or rows: Edit menu {Column - Width}; Insert menu; date and time. Lots of Right Click!
File
functions – saving your work; opening files saved previously: using your thumb drive:Chart
functions: turning numbers into works of art. {demonstrate: selecting range, chart type, format chart elements. General rule: if you see something that has to be changed, point at and double click it.}Plan your chart, calculate the numbers needed, go to Chart Wizard. To edit basic chart elements, single click right button with cursor on the chart gets you the menu.
Project for the day
Plot: sin(x), cosine(x), sin(x) + cos(x), sin2(x), and sin(x2) from 0 to 360 degrees. Note: Excel functions require angles expressed in radians (there are 2π radians in a circle - 2π radians = 360 degrees) but you need to show degrees as independent variable axis values.
Note that there are equations in which a variable (let's call that variable "y") depends on how a second variable influences a mathematical operation or function. You first heard of equations in for form y = f(x) in 9th grade or earlier.
Some things to consider: spreadsheets make writing and calculating easy, why not use dense sampling of variable "x"? A computer screen is several hundred pixels wide - hundreds of data points is not an unreasonable quantity, but thousands of data points will probably not provide useful details. 10 data points is really too sparse, 10,000 too many.
Extra credit: A really curious person might make 2 sets of curves - one with sample interval = 10 degrees, another with sample interval = 1 degree. Really curious people make good scientists. They are also good observers and can describe how the 10 degree sample interval graphs differ from the 1 degree sample interval.
Label
and format axes, add title (your name, date due) to chart. PREVIEW BEFORE PRINTING!!! {demonstrate}.Convert your chart into an image (preferred!) and insert in into a Word document and send it to me attached to a Blackboard message (by noon, Friday if you want to see your grade Friday afternoon).
Check out the Helpful Hints page for advice on turning Excel charts into images that conform to WYSIWYG principles. Most of the time, Windows Office thinks it knows better than you do and changes axis scales without your permission when you copy from Excel and paste into Word or PowerPoint. Visit this 'hints' page if you have this problem for my solution - not elegant but it works.
I've found that copy and paste into Word or PowerPoint sometimes changes the chart format (axis labels and chart dimensions, for example).
Note: check your chart and compare with solutions displayed on the WWW.
My graph - does your graph look something like this? NOTE IMPORTANT DETAILS!
Thus you can preview your result before printing it, compare it with your current chart, and decide if additional editing is needed before making the hard copy. If, in reviewing these instructions, you think you can do what is required without attending the lecture, simply complete the project, save the spreadsheet and send me the entire file attached to an e-mail message.
Next week: Importing files into Excel; data processing and management, array functions and more charts.
Background: Mayan ruins at Copán, Republic of Honduras, late November of 2002. Photo by Dr.D.