# Using Excel to create a database ...

Discussion in 'Business Operations' started by CSRA Landscaping, Feb 26, 2002.

1. ### LawnLadLawnSite Senior Memberfrom Cleveland, OhioPosts: 738

Okay... here's the equation - pay no attention to the number 17, as it is simply the row in which I copied the formula. The \$ sign fixes the number so when you copy the cell, it doesn't change the numbers in the formula, since excel likes to think too much.

=IF(\$C17="EA",\$B17*\$D17,IF(\$C17="SQFT",\$B17*\$D17,IF(\$C17="Bale",\$B17*\$D17,IF(\$C17="CY",\$B17*\$D17,IF(\$C17="TON",\$B17*\$D17,IF(\$C17="LB",\$B17*\$D17,IF(\$C17="LF",\$B17*\$D17,"")))))))

Hope this will clarify. Follow the example above:

On the bid sheet:
Column A = Description of item
Column B = Quantity of item
Column C = Unit of measure (EA, SQFT, BALE, etc)
Column D = Price
Column E = hidden column - don't worry about for moment

Therefore, in the equation above, If C, the unit of measure, equals EA, SQFT, etc. then multiple B (quantity) times D (price). This formula rests in the materials column.

=IF(OR(\$C17="Day",\$C17="Ehr"),\$B17*\$D17,"")

Sitting in the equipment column, if C, the unit of measure equals DAY, EHR (equip hours), then multiple B (quanity) times D (price).

Therefore, if I have in the following columns
A: Rototiller B: 2 day C: DAY D: \$30.00
The column for equipment will pick up that this is an equipment calcualation, not material or labor, and therefore drop \$60.00 into the equipment column.

Hope this helps.

PS - I separate the foreman and technician hours into two columns and designate them with F & T in the Unit of Measure (U/M) column. So I don't have to constantly type in the hourly rate, I have it fixed in an assumptions area with other mark up numbers (overhead, profit, etc). This way I reference the same number, and if I want to change the labor rate throughout the whole project, I only have to type it once, not each time labor was applied to the job.