Quatto Pro Spread Sheet Questions???

[Rick M]

I could sure use some help from those more knowledgeable than I.

Been trying to set up a mileage log in the Quattro Pro that came with my Wordperfect X3 suite. I am wa-a-a-y more than a virgin when it comes to spreadsheets. Kind of weird in a way since my history with computers goes back to 1983, but in a narrow corridor of programs I guess.

Anyway, I have set it up so that column A=DATE, C=ODOMETER, E=MILES, G=GALLONS, I=MPG, and K=AVG-MPG. Have stumbled around (long enough to rebuild an engine by the way) and can get the individual cells to compute. What I am trying to figure out is the syntax to have the formulas automatically progress down through each column for the Miles, MPG, and AVG-MPG without having to set up each cell. For example I set E5 as =IMSUB(C5,C4) but then I need it to know that for E6 it should look to (C6,C5) etc. Surely there is a series of keystrokes that accomplishes that, as well as having column I (MPG) automatically figure the MPG rounded to the nearest 0.1 or 0.01 not carried out to the 10,000ths, and then one to recompute the cumulative AVG-MPG as each new average results from the new entries.

It is not that I need this, just thought it would be a way to learn to run the program. As you may have concluded, it is running me in the ground with frustration. It seems obvious that the people that set up the help pages are adept at operating the program because the questions and answers are in the language of those already knowledgeable, not shade tree southern boy.

[klhansen]

Here's what I did

-------A-----B-----C-------D---------E-----
-----Date - ODO - Gal - Tank MPG - Cum MPG
1 ---1/1/01---0 ---XX-----XX--------XX
2 --1/15/01-350---21.875--16--------16
and so on

Cols A, B, & C are input
Cell D2 gets a formula = (B2-B1)/C2
Cell E2 gets a formula = (B2-$B$1)/Sum($C$1:C2) This gives you cumulative or lifetime average MPG (total miles divided by total fuel added)

Those formulas get copied down each column, and when they do, they look like this in D3 = (B3-B2)/C3
and in E3 = (B3-$B$1)/Sum($C$1:C3)

The $ embedded in the cell reference makes it absolute, so that the reference is always to B1 and C1 in the formula for col E. Otherwise, the cell references in the formulas increment by one row.

The syntax is for Excel, but should be similar for Quattro Pro

I also have a moving average set up for 4 fillups, but that's for another lesson.

Decimal point position is done by formatting the cells. In Excel, there's a button in the toolbar that moves the decimal point either left or right.

If you'd like, I can e-mail you my Excel Spread, which you should be able to import into QP.

Hope that helps

[Rick M]

It helps. I figured out how to make it round to 2 decimal places or any number I so choose for that matter. In reference to the copying of the formula down through a given column, is that a manual copy to each cell in columns D and E or is there a way to tell the program that this formula applies progressively to every cell in the column? I guess that is the question, Is there a way to auto copy through 9 years of fueling or any other long compilation of such data?

I do appreciate the help.

[Rick M]

Got it. Thanks again for the help, it got me going and I managed to get it set up just like I had in mind after your tips, especially the use of the $ in the string to force a start point.

On edit: Just don't know why the instructions in the help section of computer programs have to be so blessed cryptic. Once I got the formula for a cell figured out to make it repeat in the entire column was a simple copy:highlight the next cell down:hold shift while dragging down through the column:then paste, the program does the rest. Maybe everyone in the civilized world but me already knows how to do this ultimately simple operation, but not one of the several people I asked could tell me how to do it.

The result of the spreadsheet showed that over a period of 3 years and almost 40,000 miles our '05 Explorer has averaged ~17.5 mpg.