Best-fit
straight lines |
If you allow Excel to draw its
version of a best-fit line and then display the equation,
you are unable to give more weight to points in which you
have more faith. Here is a way of clawing control back.
- Choose two cells in which to
put the gradient and y intercept of your eventual
best-fit line. Let's call these Z1 and Z2
respectively.
- Choose an empty column - let's
say Y - and head it "best-fit y".
- Suppose your x values are
currently in column A, starting in row 2
- Enter into Y2 the formula
=A2*$Z$1+$Z$2. You will see that this calculates
y = mx + c for the first value of x.
- Copy the formula down column
Y.
- Chart your actual data as
series 1 and the Y column as series 2. Format
series 1 to have points but no line. Format
series 2 to have no marker for the points, but to
have a line joining them. The line will
necessarily be straight, because you have
calculated them from a straight line equation;
but the line almost certainly won't be anywhere
near your actual points.
- Tinkering with the number in
Z1 will tilt the line this way and that, while
tinkering with the number in Z2 will shift it up
and down. So by judicious adjustment you can
twist and slide the line until it fits the data
to your satisfaction (i.e ignoring anomalous
points if you want to). This can be done
very slickly if you know how to attach scroll
bars to Z1 and Z2 (look on the control toolbox
toolbar, avialable via the view menu).
- Read off the gradient and
y-intercept from Z1 and Z2
|
|
|
Subscripts
& superscripts in column headings |
- Select the cell.
- Highlight the relevant text,
either in the editing window or in the cell
itself.
- Starting in the main menu bar,
click on Format > Cells > subscript (or
superscript) > OK.
The formatting will take effect in the cell,
but not in the editing window
|
|
|
Entering raw
data |
- Highlight the column you wish
to use.
- Starting in the main menu bar,
click on Format > Cells > number tab >
Text > OK.
- Starting in the main menu bar,
click on Format > Cells > alignment tab
> Horizontal: Centre > OK. (This is
optional, but it often looks better.)
Then enter the data exactly as you wish
them to appear, including trailing zeros as
necessary. Subsequent columns will treat references to
these cells as numbers, as you would wish.
|
|
|
Significant
figures |
This is only relevant for calculated
columns.
- Highlight the relevant column.
- Starting in the main menu bar,
click on Format > Cells > number tab >
Scientific > OK. Before pressing OK, set the
number of decimal places to one fewer than the
number of significant figures you want.
This method only works on calculated
columns. You have to put up with E notation. It is
possible to write a complicated function that does it
without, but then you lose the underlying non-displayed
figures.
|
|
|
Quantity ¸ Unit
in
column headings
|
Getting the quantity divided by unit format
is tricky. You can do it by underlining the quantity, but
if the cell goes into word-wrap mode that obviously goes
wrong. You can also do it by inserting a row between the
quantity and unit, drawing a line in it and then
narrowing the cell (by dragging its lower boundary in the
bar on the left hand side). Then you need to select the
whole column and click on the Centred Text button. |
|
|