Using Excel to create a database ...

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

  1. LawnLad

    LawnLad LawnSite Senior Member
    Posts: 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.

    If you want more info or explanation on this, email me.
     

Share This Page