Formula Tools

Formula-writing helpers — auto-sum by group structure, fill results, assign references, round, lock references, error checking.

6 commands in the group

The Formula group on the DVD Addin tab has 1 split-button (Autosum) + 5 sub-menu buttons.

Command	Purpose	License
Autosum	Auto-write =SUM(...) by group structure	Free
Fill in Results	Copy values from formula cells to others	Premium
Assign Reference	Auto-assign formula references (relative/absolute)	Premium
Round Function	Wrap formula in ROUND/CEILING/FLOOR	Free
Lock References	Toggle $ for cell references ($A$1 ↔ A1)	Free
Error Check	Find #REF! / #NAME? / #DIV/0! cells in workbook	Free

Autosum

DVD Addin → group Formula → split-button Autosum (Σ icon).

Different from =SUM(): this command detects the group/level structure in tables with a Level column → auto-creates SUM with the right range for each total level.

Example

Multilevel quantity table:

Level	No.	Item	Quantity
1	1	Foundation	?
2	1.1	Excavation	250
2	1.2	Lean concrete	30
2	1.3	Foundation rebar	1.2
1	2	Superstructure	?
2	2.1	Floor 1 columns	20
2	2.2	Floor 1 beams	50

Select level-1 Quantity cells (e.g. D2, D6) → click Autosum → addin auto-fills:

• D2 = =SUM(D3:D5) (3 level-2 rows below until next level-1).
• D6 = =SUM(D7:D8).

→ Multilevel BoQ workflow becomes very fast — no manual range editing.

Scope

• Level column must exist (e.g. column A).
• If NO Level column → fallback to simple =SUM on the rightmost column (like Excel built-in AutoSum).

Fill in Results

DVD Addin → group Formula → menu Autosum → Fill in Results.

Copy the VALUE of formula results (instead of the formula) to other cells. Useful when:

• You need a snapshot of values at a point in time (e.g. month-end report).
• The source cell has complex dependencies → the destination cell becomes independent.

Equivalent to Excel "Paste Special → Values" but:

• Pick source + destination ranges in one dialog.
• Option to keep formatting or not.

Premium

This command requires an active license.

Assign Reference

DVD Addin → group Formula → menu Autosum → Assign Reference.

Assign reference formulas for a range of cells to corresponding source — auto-picks relative / absolute / mixed reference smartly.

Examples:

• Destination range D2:D100 referencing source range A2:A100 (1-1) → formulas = =A2, =A3, ... =A100.
• Destination range D2:F2 referencing cell B2 (broadcast) → formulas = =$B$2, ...

Dialog: pick source + destination ranges → addin generates appropriate formulas.

Premium

This command requires an active license.

Round Function

DVD Addin → group Formula → menu Autosum → Round Function.

Wrap the current formula in ROUND / CEILING / FLOOR / MROUND / TRUNC:

Example cell A1 = =B1*0.7 → click Round Function → dialog:

• Mode:
  • ROUND → =ROUND(B1*0.7, N)
  • CEILING → =CEILING(B1*0.7, N) (round up to multiple)
  • FLOOR → =FLOOR(B1*0.7, N) (round down)
  • MROUND → =MROUND(B1*0.7, N) (round to nearest multiple)
  • TRUNC → =TRUNC(B1*0.7, N) (truncate decimals)
• N (digits) — e.g. 0 (whole), 2 (2 decimals), -3 (round to thousands).

Applies to one cell or a whole range.

Lock References

DVD Addin → group Formula → menu Autosum → Lock References.

Cycle the $ lock for cell references in the selected formula:

A1 → $A$1 → A$1 → $A1 → A1 (cycle)

Equivalent to F4 in Excel while editing a formula. Difference:

• Applies to ALL references in the selected range (not just one cell).
• Dialog to choose mode (lock row / lock column / lock both / unlock).

Error Check

DVD Addin → group Formula → menu Autosum → Error Check.

Scan the entire workbook for cells with:

• #REF! — reference to deleted range.
• #NAME? — wrong/undefined function name.
• #DIV/0! — division by zero.
• #N/A — VLOOKUP / MATCH not found.
• #VALUE! — wrong data type.
• #NUM! — number too large/small.

Output: dialog listing error cells (Sheet + Address) → click cell → jump to it.

Useful when: receiving a workbook from a colleague/client and you want a quick sanity check before using it.

License gating

Command	Free	Activated/Trial
Autosum	✓	✓
Round Function	✓	✓
Lock References	✓	✓
Error Check	✓	✓
Fill in Results	✗	✓
Assign Reference	✗	✓

The 2 premium commands (Fill in Results + Assign Reference) are complex features; the 4 utilities are free.

Related

• Numbering — number sequence before Autosum so the Level column is correct.
• Cells and Rows — insert/delete rows before summing.