Consider using the ClarisWorks spreadsheet. Here's what it might look like once you've collected some data. (See Figure 1.)
The basic design
In row one, whose entries are in bold text, you keep track of totals-how much money have you spent on gas, how many gallons, how many miles did you drive for your money, over how many days.
Row two of this spreadsheet displays your averages, the average cost of a full tank, the average gallons you get for a full tank, the miles, the miles per gallon, as well as the average cents you paid per mile, the average dollars you paid per gallon, and the average dollars paid per day for gas.
In row three, the headings of the collected data are displayed. Putting the headings here lets them serve as headings for totals and averages, above, and for data, below, at the same time.
In addition to the totals and averages generated in rows one and two, rows five and on show you the daily breakdown of the data you collect. You can see the miles that you drove between full tanks, the days between filling up, the daily miles per gallon, etc.
Set up
Once you've outlined the basic design of your spreadsheet, it's time to make it work. To set up the spreadsheet in this example, follow these steps.
Set up the totals and averages first.
1. Open ClarisWorks and create a spreadsheet document. Under the Views menu, choose Show Tools.
2. Go to the cell where you want your first total, the total of money paid for gas, to display. In Figure 2, which displays the row and column headings of this spreadsheet, the cell reference is C1 and the value it contains in the example is $159.
3. Enter the following formula:
=SUM(C4..C38)
The rest of the formulas are created in the same way. In particular, for averages and totals, the formulas are (Note: For clarity, annotations appear in brackets [] and are not part of the formula you type in a cell):
Totals:
E1=SUM(E4..E38) [Total gallons]
G1=SUM(G4..G38) [Total miles]
H1=SUM(H4..H38) [Total days]
Averages:
C2=IF(C5<1,"", AVERAGE(C5..C38))
[Average amount paid per full tank]
E2=IF(C5<1."", AVERAGE(E5..E38))
[Average gallons per visit]
G2=AVERAGE(G5..G38)
[Average miles per tank]
H2=AVERAGE(H6..H38)
[Average days between visits]
J2=AVERAGE(J6..J38)
[Average cents per mile]
K2=AVERAGE(K5..K38)
[Average dollars spent per day]
L2=AVERAGE(L6..L38)
[Average dollars per gallon]
Formatting
To make the calculated values display appropriately, as dates or currency or decimal numbers, highlight the cells that are to display calculated results. For example, for the cells that hold the date values, highlight the column of cells starting at B4, then select the Format menu and choose Number. In the dialog box that appears, make your selections for Date values. Do the same for the values that are to display currency and decimals, selecting the appropriate options in the Numeric dialog box.
The fun stuff
This ClarisWorks solution makes use of some tricks pointed out in Figure 2. Of note are:
* The gutter to the left of the general display. To create this gutter, "begin" your spreadsheet and the related formulas at column B, not A. Then reduce the column width of A.
* "Floating" text block. Add a "floating" text block by selecting the "A" tool and drawing a text box.
* Gradient in Header. Add a Header to your spreadsheet, then draw a rectangle with a gradient fill, cut it and paste it as an in-line graphic in the Header text. Align text and gradient by setting text to Superscript style.
* Em dash. Use an em dash (Option-shift-dash) to help you format your data and to prevent the display of irrelevant information, such as premature "miles per gallon" information, which depends on previous values to calculate.
* Colored cell borders. Add emphasis to a cell, through a colored border by selecting the "Rectangle" tool and holding down the Option key as you draw a rectangle. (The rectangle must have a transparent fill pattern.)
The advanced stuff
The heart of the solution is daily tabulations, indicated by those cells that have been formatted to have no borders (starting at G5). Note the calculations are spelled out in Figure 2 above. After you fill out your spreadsheet with these formulas, you may notice that what they all have in common is a test for a value (in this case, a date) that is greater than one in either B5 or B6, which represent your first and second visits to the gas station. To get the formulas to apply to all the days you fill your tank, you'll want to follow these steps for each column, G, H, I, J, K, and L:
1. Highlight the column of cells starting at G5 through G38.
2. Go to the Calculate menu and choose Fill Down.
The value for G6 (the miles covered between visits) can now calculate for each visit to the gas station. Do the same for the columns starting at H5, I6, J6, K6, and L5.
Your spreadsheet is ready to hit the pavement! Print out an empty version and record your data at the gas pump each time you fill your tank.
Figure 1. Track miles with a ClarisWorks spreadsheet. Collect data manually, then enter it into your spreadsheet to have ClarisWorks do all the math.
Figure 2. Behind the scenes of the ClarisWorks spreadsheet, these are the tricks that make it work.