Excel Hints

 


 

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.