Creating Static Owner Bid Forms with Ediphi's Excel Add-In (Last Mile)

Important Note: This guide is intended for users who are comfortable with Excel formulas and willing to maintain their own custom workbooks. Static templates are powerful but require Excel expertise to troubleshoot when issues arise.

Static templates are considered user-created content. The formulas in this guide are examples - you are responsible for adapting them to your specific needs. If formulas break due to data structure changes, you must fix them yourself.

Ediphi Support does not provide assistance with custom Excel formulas or static templates.

Static Owner Bid Forms are custom Excel worksheets that reference Ediphi data using formulas such as SUMIF and other standard Excel functions. Unlike dynamic reports generated through the Ediphi add-in, these forms have a fixed structure that does not change when the data is refreshed. They aggregate information from hidden Ediphi tables and allow for complete control over formatting, layout, and additional calculations. This makes them particularly useful for standardized client deliverables that require a consistent appearance and specific data presentation.

While Static Owner Bid Forms offer powerful customization options, they also require a solid understanding of Excel and must be actively maintained by the user. These templates are provided as examples only; it is your responsibility to adapt, troubleshoot, and update them to meet your specific needs.

Ediphi’s recommended solution is the dynamic reporting feature built into the add-in. Static templates should only be used if you fully understand and accept the maintenance responsibilities involved in working with custom Excel files.

Prerequisites

Before creating a static bid form, ensure:

The Ediphi Excel Add-In is installed and active.
Project data has been loaded (Data tab → Load Project).
Hidden data tables exist in your workbook (Estimate, Report, UseGroups, etc.).

When to Use Static Templates vs. Dynamic Reports

Use Dynamic Reports (Built-in Add-In Features) when:

You need reliable, supported functionality.
Data structures may change frequently.
Multiple users need consistent results.
You want automatic updates without formula maintenance.

Use Static Templates only when:

You have specific formatting requirements that dynamic reports can’t meet.
You’re comfortable debugging Excel formulas.
You accept responsibility for maintaining the workbook.
You understand that Ediphi updates may break your formulas.

Common Pitfalls to Avoid

Version Drift: Copying formulas between workbooks with different data structures.
Naming Mismatches: Use group names or markup names that don’t exactly match your data.
Formula Fragility: Complex INDIRECT formulas break easily when column names change.
Support Confusion: Expecting Ediphi support to fix broken custom formulas.

Recommended Approach

Start with Dynamic Reports - Use the add-in’s built-in reporting features first.
Document Your Customizations - Keep notes on any formula modifications you make.
Test Thoroughly - Verify formulas work with your specific project data.
Train End Users - Ensure anyone using the workbook understands how to maintain it.
Keep Backups - Save working versions before making changes.

For Users

Start Simple: Begin with basic formulas before attempting complex INDIRECT references.
Test Incrementally: Build your template step by step, testing each formula as you go.
Keep Original Copies: Always maintain an unmodified backup of working templates.
Learn the Data Model: Understanding Ediphi’s table structure is crucial for troubleshooting.

Basic Structure

1. Header Section - Project Information

The header pulls information from the Report table using direct cell references:

2. Column Headers

Create headers in row 9 (or your preferred row):

3. Data Rows with SUMIF Formulas

CSI Division Example (MasterFormat Level 1)

For each CSI division, create a row with formulas that aggregate data from the Estimate table:

Alternative Grouping Options

UniFormat Level 1 (UF1)

UniFormat Level 2 (UF2)

MasterFormat Level 2 (MF2)

If your project uses custom sort fields:

Adding Subtotals and Totals

Subtotal Row (Construction Costs)

Markups Section

Add markups below the subtotal:

Note: For markups, you might need to use SUMPRODUCT for multiple criteria:

Grand Total Row

Data Rows with BreakoutIF Formula

The BreakoutIF formula is a custom Ediphi Excel function designed to conditionally sum use group breakout costs from the Estimate table based on specific criteria. It allows you to filter and aggregate cost data dynamically based on any condition you specify.

Syntax

Parameters

How It Works

Finds the column: Searches for the column header matching {useGroupName}_$ in the Estimate (__Estimate worksheet) table.
Applies condition: Filters rows where the condition is TRUE.
Sums values: Adds up the filtered cost values from the use group column.
Returns result: Returns the total sum, or "Use Group Not Found" if the use group doesn't exist.

Examples

Example 1: Sum costs for a specific sort code

Sum all "Office" use group costs where the sort code is "03 10 00":

Example 2: Sum costs for multiple conditions

Sum "Retail" use group costs where the division is "03" AND the item is marked as base bid:

Example 3: Sum costs by system

Sum "Parking" use group costs for the MEP system:

Example 4: Sum costs by type

Sum "Office" use group costs for all line items (excluding markups):

Example 5: Sum costs above a threshold

Sum "Retail" use group costs where the total cost is greater than $10,000:

Example 6: Sum costs using wildcard matching

Sum "Office" use group costs where the name contains "Door":

Common Use Cases

By Division

By Trade

By System

By Alternate Status

By Floor/Location

Tips and Best Practices

1. Check Use Group Names

Make sure the use group name matches exactly (case-sensitive):

✅ Correct: "Office" , "Retail" , "Parking"
❌ Incorrect: "office" , "OFFICE" , "Off ice"

2. Verify Column Names

Ensure the column you're referencing exists in the Estimate table. Common columns include:

Estimate[name] - Item name/description
Estimate[division] - CSI division
Estimate[sort_code] - Full sort code
Estimate[trade] - Trade category
Estimate[system] - System assignment
Estimate[type] - Item type (line_item, markup, etc.)
Estimate[baseBid] - Base bid flag
Estimate[alternate] - Alternate name
Estimate[total] - Total cost

3. Combine Multiple Conditions

Use Boolean operators to create complex filters:

AND: (condition1)*(condition2)
OR: (condition1)+(condition2)
NOT: NOT(condition)

Example:

4. Handle Errors

If you see "Use Group Not Found", check:

The use group name spelling and capitalization.
Whether the use group column exists in the Estimate table.
The Estimate table has been loaded with data.

5. Performance Considerations

The formula recalculates when the Estimate table changes.
Complex conditions may slow down recalculation on large datasets.
Consider using named ranges for frequently used conditions.

GSF: =GSF() - Returns the total Gross Square Feet from the FloorAreas table.
Standard Excel Functions: You can combine BreakoutIF with other Excel functions like SUM, IF, SUMIFS, etc.