MASS BALANCE ADD-IN – Custom Excel Functions for Mineral Processing

Excel Mass Balance Made Easy

Mass balance calculations are essential for evaluating mineral processing circuits. They are used in plant design, equipment sizing, process optimization, circuit auditing, and metallurgical performance analysis. Excel is widely available and flexible, but it does not provide native mineral-processing stream-balance functions.

The DPSIM Mass Balance Add-In fills this gap by adding custom Excel functions for common stream calculations. With the add-in installed, engineers can merge streams, calculate concentrate and tailings streams from recovery values, and back-calculate recoveries from measured feed and concentrate data.

These calculations are almost always present when performing plant design, equipment sizing and process optimization. The add-in integrates directly into Excel workflows, making calculations faster and more reliable for mineral processing workbooks.

Files to Download

Use the add-in file as the main installation file. The example workbook is provided as an Excel file that uses the installed add-in functions.

• DPSIM_MassBalance_AddIn.xlam – the main Excel Add-In file containing the Mass Balance custom functions.
• DPSIM_MassBalance_Example.xlsx – the primary example workbook with formulas using the function names. The add-in must be installed for these functions to calculate.
• DPSIM_MassBalance_Example_Macro.xlsm – macro-enabled example, useful only when you need to test or review workbook macros.
• DPSIM_MassBalance_Source.zip – optional source package for users who want to inspect or import the VBA code manually.

How to Use the Mass Balance Add-In

Installation

1. Download DPSIM_MassBalance_AddIn.xlam.
2. Open Excel and go to File > Options > Add-ins.
3. At the bottom of the window, select Excel Add-ins and click Go.
4. Click Browse, select the .xlam file, check the add-in box, and click OK.
5. Open the example workbook and recalculate the file if Excel prompts you to do so.

The example workbook can be distributed as .xlsx because the functions are provided by the installed add-in. Use .xlsm only if the workbook itself contains VBA macros.

Setting Up the Streams in the Excel Mass Balance Table

The functions use a standard vertical stream vector. Each stream is stored in one Excel column:

• Row 1: solid mass flowrate.
• Row 2: water flowrate.
• Rows 3 onward: component grades by mass.

All stream ranges used together must have the same number of rows. For example, if the feed contains two components, the stream vector has four rows: solid flow, water flow, component 1 grade, and component 2 grade.

Main Functions

Merging Streams

The SumMultipleStreams function combines two or more stream columns into one. It sums solids and water directly and calculates the combined component grades by solid-mass weighting.

Function syntax:

=SumMultipleStreams(stream1, stream2, [stream3], ...)

Example:

=SumMultipleStreams(C22:C25,D22:D25)

Calculating Concentrate and Tailings Streams

The CalculateConcentrate function calculates a concentrate stream from a feed stream and a recovery vector. The recovery vector starts with water recovery, followed by the recovery of each component to the concentrate.

The concentrate is not calculated by simply multiplying the total feed solids by the recovery of one component. The function calculates recovered mass for each component and for water, then returns the standard stream vector with solid flow, water flow and component grades recalculated.

=CalculateConcentrate(feed_stream_range,recovery_range)

Then use SubtractStreams to calculate the complementary stream, such as tailings:

=SubtractStreams(feed_stream_range,concentrate_stream_range)

Calculate Recoveries

Use CalculateRecoveryToConcentrate when the feed and concentrate streams are known and the recovery values need to be calculated. The result includes solid recovery, water recovery and component recoveries.

=CalculateRecoveryToConcentrate(concentrate_stream_range,feed_stream_range)

Example Calculations

Stream Merge

Assume two feed streams have the following solid flowrates and component grades. For simplicity, water is omitted in this explanation, but the spreadsheet function also handles the water row.

• Stream 1: 200 tph with 30% component A.
• Stream 2: 150 tph with 25% component A.
• Combined stream: 350 tph with the combined grade calculated by mass weighting.

The combined grade is not a simple arithmetic average. It is weighted by the solid mass flowrate:

(200 x 30% + 150 x 25%) / 350 = 27.86%

=SumMultipleStreams(C22:C25,D22:D25)

Concentrate and Tailings Calculation

For a feed stream with solid flow, water flow and component grades, define a recovery vector with water recovery followed by the component recoveries. The concentrate solids are calculated from the recovered component masses, not by multiplying the total feed solids by one component recovery.

Step 1: Calculate the concentrate stream.

=CalculateConcentrate(feed_stream_range,recovery_range)

Step 2: Calculate the tailings stream.

=SubtractStreams(feed_stream_range,concentrate_stream_range)

Using these functions, you can perform the stream calculations for a flotation circuit while keeping solids, water and component grades consistent across the workbook.

Flotation Circuit with Recirculating Load

A rougher-scavenger circuit with a recycled scavenger concentrate creates a circular reference in Excel. The example workbook uses iterative calculation so that the circulating load converges.

The calculation sequence is:

1. Combined rougher feed = fresh feed + scavenger concentrate.
2. Rougher concentrate = CalculateConcentrate(combined feed, rougher recovery vector).
3. Rougher tailings = SubtractStreams(combined feed, rougher concentrate).
4. Scavenger concentrate = CalculateConcentrate(rougher tailings, scavenger recovery vector).
5. Scavenger tailings = SubtractStreams(rougher tailings, scavenger concentrate).

To enable iterative calculations in Excel:

1. Go to File > Options > Formulas.
2. Under Calculation options, check Enable iterative calculation.
3. Use a reasonable maximum iteration count, such as 100.
4. Use a small maximum change, such as 0.001, depending on the precision needed.

Once iterative calculation is enabled, Excel can update the recirculating stream until the system converges to stable values.

The downloadable example workbook contains the Mass Balance formulas and can be used as a starting point for testing the add-in functions.