4 -Moving around the spreadsheet
7 - Entering formulas in cells
12 - Copying, moving and erasing data
-When two or more keys are to be pressed together, they are separated by a hyphen, i.e., ALT-F5
-Inputs will be typed on a separate line, i.e., +H4-S6*G17(1000-G2)
-Commands will be written between brackets { } and separated by a comma, i.e., {/,Edit,Copy}.
1 -Using DOS commands move to the subdirectory that contains the "Quattro Pro" software, it can be under any name but usually under "Q" or "QPRO" or "QUATTRO:.
* If there is a path for Quattro Pro in the Autoexec.bat file you need not do step 1.
(The next page shows these contents.)
It consists of columns labeled with letters, and row labeled with numbers, columns and rows intersect in small rectangles, each rectangle is called a cell.
A cell address is a combination of the row number and the column letter for a certain cell, i.e., cell B5 is the small rectangle in column "B": and row "5".
Cells start empty until you fill them.
The input line shows information about the current cell such as the cell address and the value inside the cell, i.e., B5: 2356 means in cell B5, the current cell, there is a value of 2356.
It appears on the bottom of the screen, it has information about the spreadsheet filename and the status modes you are working in, i.e., "Caps Lock" for using capital letters in the spreadsheet.
You must see the word "READY" in the status line to work with Quattro Pro. If you do not see it wait until it appears.
It contains all the commands used, it has submenus under the menu bar.
To move from the spreadsheet to the menu bar, press /, for vice versa pres ESC.
It is optional and it needs a mouse. It contains commands often used.
4 - Moving Around the Spreadsheet:
CTRL-Left arrow One window to the left
CTRL-Right arrow One window to the right
End,Home Moves to the lower right corner of the active area
To choose a command in a submenu:
1 -To access the menu press /.
2 -Use arrow keys to move to the desired command (i.e., FILE), then press ENTER.
3 -The submenu will appear, use arrow keys to move to the desired command (i.e., NEW), then press ENTER.
The last three steps will be expressed in this manual in the following way:
To escape from a submenu to the previous menu press ESC, (an example for a submenu is on the next page).
There are three types of data in a cell:
- A value means simply a number, type the number and press ENTER, (i.e., 90210).
-A label means a stream of characters, type the characters and press ENTER, (i.e., 'salaries).
-Quattro Pro Automatically puts a single quotation mark before the entry to assign the entry as a label, when the first entry character is an alphabetical character.
-If your entry has a number at the beginning, and you want to assign the entry as a label, write a single quotation mark before it, (i.e., '2 JAN).
-You can enter a date as 06-10-92, and Quattro Pro will treat it as a date.
-Before entering the date press CTRL-D, then type the date and press ENTER.
7 - Entering Formulas in Cells:
A formula should be proceeded by a "+" signal i.e., +(B4+B5).
This means the value in B4 is added to the value in B5 and the sum is assigned to the current cell.
The first "+" signal is only to identify that what comes after is a formula.
Another example (on page 189): +(A3*A7)-B5
This means to subtract the value in B5 from the product of values in A3 and A7, an example of this is shown on the page 190, the formula is written in the current cell B8, but only the answer appears, +(A3*A7)-B5 is equivalent to (4*3)-7 which is equal to 5.
To edit a current cell press F2, and the cursor will move from the spreadsheet area to the input line, you can then edit whatever is in the current cell, when you finish press ENTER.
A block is a number of cells that form together a solid rectangular shape as shown on page 190. To specify a block, whose upper left corner is c8 and lower right corner is _13, you should type:
When you enter certain commands, Quattro Pro asks you to select a block, like in {COPY} command. Quattro Pro also gives you the last block as an answer, if you want it just press ENTER, if you do not, the following example will show you what to do:
*Press CTRL-C to copy, Quattro Pro will ask you for the source block, you will have the following options to choose the block:
1 -Type the address of a new block and ENTER, (i.e., B5. . D7).
2 -Use the arrow keys to change the lower corner of the block, then press ENTER (The upper left corner is always anchored).
3 -If you want to change the upper left corner you have to unanchor it by pressing ESC, then using the arrow keys move to the cell desired and press the dot character ( . ) to anchor it again, repeat step 2.
Only simple and necessary operations are explained.
To save a spreadsheet follow these steps:
2 -Quattro Pro will ask you for the filename, type the filename and ENTER.
3.If previous savings have been made, Quattro Pro will ask whether to backup the old file or to replace it by the new one or to cancel the whole command.
4.Choose one of the options and press ENTER.
To save in another place rather than the hard disk or under another subdirectory use:
Then follow the same previous steps.
Quattro Pro will ask the name of filename to retrieve, type it and press ENTER.
Using DOS commands without quitting Quattro Pro:
OR: {/,FILE,UTILITIES,FILE MANAGER}
Lines can be drawn between cells, a line can be drawn under number of horizontal cells (in the same row) as to underline them, lines can be drawn around a block of cells to make a box.
2 -Quattro Pro will ask for the block of cells to draw lines in, you can choose a rectangular block, a column block, a row block or even a single cell as a block.
3 -Quattro Pro will ask where the lines are to be drawn in the block (e.g., bottom, right, left ... etc.), choose one of them.
4 -Quattro Pro will ask what kind of line to be drawn:
- None (to erase an existing line).
5 -The steps 3 and 4 can be repeated to make many different lines and boxes in one block.
If a line is drawn on an existing line, the last one will replace the previous one.
12 - Copying, Moving and Erasing Data:
1 -{/,EDIT,COPY} or press CTRL-C.
2 -Quattro Pro asks for the block to be copied and calls it "source block", enter the source block.
3 -Quattro Pro asks for the place where you want to paste your block and calls it "destination block", enter the destination block.
1 -{/,EDIT,MOVE} or press CTRL-M.
2 -The same steps as in copying (steps 2 & 3).
2 -Choose the block to be erased.
Functions are built-in tools that allow you to perform specialized and sometimes complex operations, these operations might be statistical, mathematical, trigonometrical, financial and many more.
A well known example is the "SUM" function, it adds the numbers in a certain block and assigns the summation in the cell where the SUM function is written.
If this function is written in cell B8, it will add whatever is in cells B1 to B6, and assign the summation to cell B8 as shown on the next page.
1 -@AVG(B1. .B6), to compute the average.
2 -@MIN(B1. .B6), to compute the minimum.
3 -@MAX(B1. .B6), to compute the maximum.
There are more than 75 different functions built-in Quattro Pro.
Any function must start by a "@" sign.
2 -{BLOCK{, Quattro Pro will ask you what part of the spreadsheet you would like to print, enter the desired block.
3 -{DESTINATION}, choose either PRINTER or GRAPHIC PRINTER.
4 -{SPREADSHEET PRINT} or {PRINT TO FIT}.
1 - Equipment Ownership Costs:
-The spreadsheet appears on page 20, the entries of the spreadsheet are shaded (numbers or characters the user has to input).
-Page 21 shows a sample input entry and the results obtained.
-Page 22 shows a printout of the functions in the spreadsheet just to show where the functions are and how they work.
-Page 23 to 24 shows what is written in each cell for any user to build up the same spreadsheet again.
Just enter your inputs in the shaded cells and the other cells will be computed automatically.
Relationships inside the spreadsheet:
1 -Annual capital recovery costs:
2 - Capital recovery factor (I) %.
4 - Expected years of use (N).
5 - Expected hours of use/year (H).
Annual capital recovery costs =
2 -Total annual ownership costs:
4 - Tax, License & Registration $.
Total annual ownership costs = Depreciation + Storage + Insurance + (Tax, license & Registration) + Administration + Inflation * P
1 - Repairs (% of total cost / year).
3 - Fuel, Oil & Grease ($/hr).
4 -Total equipment ownership costs:
Total equipment ownership costs =
(Annual cap. recovery + Total annual ownership)/H
How to build your own spreadsheet:
On a new spreadsheet, enter the cell entries as they appear on pages 200 & 201.
means in cell C4 whose width is 18 characters (cell width adjustment will be explained later in this chapter), type:
means in cell E11 whose width is 11 characters and is to be expressed as currency with two decimals (C2), type:
means in cell E15 whose width is 11 characters and is to be expressed as a percentage with two decimals (P2), type:
To adjust the width of a cell you have to adjust the width of the whole column containing this cell:
1 - {/,STYLE,COLUMN WIDTH} or CTRL-W.
2 - Type the number of characters desired for cell width and press ENTER.
The cell width appears on the Input Line:
[W11] means that the width of this cell is 11 characters.
2 -Currency and Percentage formats of a cell:
To make a cell entry appear as currency or percentage:
1 - {/,OPTIONS,FORMAT} or CTRL-F, then Numeric Format.
2 - Quattro Pro will ask you to choose what kind of format, choose the format desired and
3 - Quattro Pro will ask you the number of decimals that will appear, type your number and
4 - Quattro Pro will ask you to identify the block of cells to have this format, enter the
The cell format will appear on the Input Line:
(C2) inidicates that the format is currency and decimals = 2.
@IF (condition, true expression, false expression)
means if cell E15 = 0 is true then give 0 value to current cell, and if cell E15 = 0 is false then give (E14/E15) to the current cell.
REPAIRS (% OF TOTAL COST)/YEAR
TOTAL EQUIPMENT OWNERSHIP COSTS
CAPITAL RECOVERY FAC (%) 9.00%
ANNUAL CAPITAL RECOVERY COSTS $5,174.12
DEPRECIATION (STRAIGHT LINE) $3,500.00
TOTAL ANNUAL OWNERSHIP COSTS $5,775.00
REPAIRS (% OF TOTAL COST)/YEAR 15.00%
FUEL, OIL & GREASE ($/HOUR) $4.50
TOTAL HOURLY OPERATION COSTS $9.89
TOTAL EQUIPMENT OWNERSHIP COSTS $23.57
C2: [W18] ' EQUIPMENT OWNERSHIP COSTS
B10: ' INITIAL PURCHASE INFORMATION
B15: ' CAPITAL RECOVERY FAC (%)
B17: ' ANNUAL CAPITAL RECOVERY COSTS
E17: (C2) [W11] @1F(E15=0,0,((E11-E14)*((E15*((1+E15)^E13))/(((1+E15)^E13)-1)))+(E14*E15))
B21: ' DEPRECIATION (STRAIGHT LINE)
B28: ' TOTAL ANNUAL OWNERSHIP COSTS
E28: (C2) [W11] @SUM(E21. .E25)+(E11*E26)
B32: ' REPAIRS (% OF TOTAL COST)/YEAR
B34: ' FUEL, OIL & GREASE ($/HOUR)
B37: ' TOTAL HOURLY OPERATION COSTS
E37: (C2) [W11] @1F(E12=0,0,(E32*E11)/E12)+E33+E34+E35
B41: ' TOTAL EQUIPMENT OWNERSHIP COSTS
E41: (C2) [W11] @1F(E11=0,0,((E17+E28)/E12)+E37)
-The spreadsheet appears on page 27, the entries of the spreadsheet are shaded (numbers or characters the user has to input).
- Page 28 shows a sample input entry and the results obtained.
- Page 29 shows the position of the spreadsheet according to the row and column labels.
-Page 30 to 32 shows what is written in each cell for any user to build up the same spreadsheet again.
What is the spreadsheet about:
The contractor makes an estimate of his costs before the project, which is called the estimated cost, he adds his planned profit to it. The sum is the contract amount, which is used to bid.
Each month the contractor calculates the amount of the project completed in ($) to get paid by the owner. It is a good management procedure to update the cost estimation each month, that means to make a new estimation of cost. This procedure indicates if the project is on track or not from the profit point of view.
Each month in the spreadsheet you input the actual costs, and the new estimated cost to complete (an updated estimate of cost of what still has to be done on the project). They should add up to the initial estimated cost (done before the project), but practically they will not. The spreadsheet will give you a new profit percentage for the whole project.
Relationships inside the spreadsheet:
Total cost to date = Sum of actual costs to date
Updated estimated cost =Total cost to date + Estimated cost to complete
(The updated estimated cost might not be equal to the initial estimated cost.)
3 -Actual percentage completed:
Actual percentage completed = Total cost to date / Updated estimated cost
Earned value = Actual % completed * Contract amount
= Actual % completed * 7,500,200
Estimated profit ($) = Contract amount - Updated estimated cost
= 7,500,200 - Updated estimated cost
Estimated profit (%) = Estimated profit ($) / Updated estimated cost
How to build your own spreadsheet:
On a new spreadsheet, enter the cell entries as they appear on pages 30 to 32, and as has been done on the previous example.
the (^) sign is to center the label in the cell.
The table is just some boxes and lines put together:
2 -Quattro Pro will ask you to choose a block, type B12. .K26 , then press ENTER.
3 -Choose the Position of line to be ALL, then press ENTER.
4 -Choose the type of line to be SINGLE, then press ENTER.
5 -Choose the type of line to be OUTSIDE, then press ENTER.
6 -Choose the type of line to be DOUBLE, then press ENTER.
7 -Press ESC to go back to block selection.
8 -Just as before select block (B12. .K13).
Project: New River Shopping Mall
Planned Profit: $450,200 (6.39%)
D7: [W11] ' NEW RIVER SHOPPING MALL
F14: (C0) [W11] @IF(E14=0,0,F13+E14)
H14: (P2) [W12] @IF(G14=0,0,F14/(F14+G14))
I14: (C0) [W11] @IF(H14=0,0,7500200*H14)
J14: (C0) [W11] @IF(I14=0,07500200-(F14+G14))
K14: (P2) [W11] @IF(J14=0,0,J14/(F14+G14))
F15: (C0) [W11] @IF(E15=0,0,F14+E15)
H15: (P2) [W12] @IF(G15=0,0,F15/(F15+G15))
I15: (C0) [W11] @IF(H15=0,0,7500200*H15)
J15: (C0) [W11] @IF(I15=0,0,7500200-(F15+G15))
K15: (P2) [W11] @IF(J15=0,0,J15/(F15+G15))
F16: (C0) [W11] @IF(E16=0,0,F15+E16)
H16: (P2) [W12] @IF(G16=0,0,F16/(F16+G16))
I16: (C0) [W11] @IF(H16=0,0,7500200*H16)
J16: (C0) [W11] @IF(I16=0,0,7500200-(F16+G16))
K16: (P2) [W11] @IF(J16=0,0,J16/(F16+G16))
F17: (C0) [W11] @IF(E17=0,0,F16+E17)
H17: (P2) [W12] @IF(G17=0,0,F17/(F17+G17))
I17: (C0) [W11] @IF(H17=0,0,7500200*H17)
J17: (C0) [W11] @IF(I17=0,0,7500200-(F17+G17))
K17: (P2) [W11] @IF(J17=0,0,J17/(F17+G17))
F18: (C0) ]W11] @IF(E18=0,0,F17+E18)
H18: (P2) [W12] @IF(G18=0,0,F18/(F18+G18))
I18: (C0) [W11] @IF(H18=0,0,7500200*H18)
J18: (C0) [W11] @IF(I18=0,0,7500200-(F18+G18))
K18: (P2) [W11] @IF(J18=0,0,J18/(F18+G18))
F19: (C0) [W11] @IF(E19=0,0,F18+E19)
H19: (P2) [W12] @IF(G19=0,0,F19/(F19+G19))
I19: (C0) [W11] @IF(H19=0,0,7500200*H19)
J19: (C0) [W11] @IF(I19=0,0,7500200-(F19+G19))
K19: (P2) [W11] @IF(J10=0,0,J19/(F19+G19))
F20: (C0) [W11] @IF(E20=0,0,F19+E20)
H20: (P2) [W12] @IF(G20=0,0,F20/(F20+G20))
I20: (C0) [W11] @IF(H20=0,0,7500200*H20)
J20: (C0) [W11] @IF(I20=0,0,7500200-(F20+G20))
K20: (P2) [W11] @IF(J20=0,0,J20/(F20+G20))
F21: (C0) [W11] @IF(E21=0,0,F20+E21)
H21: (P2) [W12] @IF(G21=0,0,G21/(F21+G21))
I21: (C0) [W11] @IF(H21=0,0,7500200*H21)
J21: (C0) [W11] @IF(I21=0,0,7500200-(F21+G21))
K21: (P2) [W11] @IF(J21=0,0,J21/(F21+G21))
F22: (C0) [W11] @IF(E22=0,0,F21+E22)
H22: (P2) [W12] @IF(G22=0,0,F22/(F22+G22))
I22: (C0) [W11] @IF(H22=0,0,7500200*H22)
J22: (C0) ]W11] @IF(I22=0,0,7500200-(F22+G22))
K22: (P2) [W11] @IF(J22=0,0,J22/(F22+G22))
F23: (C0) [W11] @IF(E23=0,0,F22+E23)
H23: (P2) [W12] @IF(G23=0,0,F23/(F23+G23))
I23: (C0) [W11] @IF(H23=0,0,7500200*H23)
J23: (C0) [W11] @IF(I23=0,0,7500200-(F23+G23))
K23: (P2) [W11] @IF(J23=0,0,J23/(F23+G23))
F24: (C0) [W11] @IF(E24=0,0,F23+E24)
H24: (P2) [W12] @IF(G24=0,0,F24/(F24+G24))
I24: (C0) [W11] @IF(H24=0,0,7500200*H24)
J24: (C0) [W11] @IF(I24=0,0,7500200-(F24+G24))
K24: (P2) [W11] @IF(J24=0,0,J24/(F24+G24))
F25: (C0) [W11] @IF(E25=0,0,F24+E25)
H25: (P2) [W12] @IF(G25=0,0,F25/(F25+G25))
I25: (C0) [W11] @IF(H25=0,0,7500200*H25)
J25: (C0) [W11] @IF(I25=0,0,7500200-(F25+G25))
K25: (P2) [W11] @IF(J25=0,0,J25/(F25+G25))
Compton, Jay C., Construction Management Using Lotus 1-2-3, Management Information Source, Portland, Oregon, c1987.
LeBlond Group, "Guide to Using Quattro Pro 3.0/4.0," PC-Magazine, Ziff-Davis Press, Emeryville, California, 1992.
"Assignments of CGN 6905, Computer Applications in Construction Management," Fall 1992, instructed by Dr. Ralph D. Ellis, University of Florida.
G2 Estimator User Manual, G2 Inc., 1989.
Primavera Project Planner 5.0 Reference, Primavera Systems, Inc., 1991.
This document was produced using an evaluation version of HTML Transit 2
