Excel Mass Balance Made Easy

Today, we address this gap by providing an easy-to-implement Excel Add-in solution. It automates mass balance calculations, making faster and more reliable for the mineral processing community. The provided codes are user-friendly and flexible, designed to integrate seamlessly into existing Excel workflows. The Mass Balance Add-in can handle a range of calculations, from basic stream balances to complex scenarios like flotation circuits with recirculating loads. Using these functions, mineral processing professionals can quickly model processes, perform optimizations, and study plant performance.

How to Use the Mass Balance Add-in

Installation

Follow these steps to implement and use the provided VBA codes in Excel:

1. Click here to download the Add-In file .
2. Load the Add-In in Excel:
   • Go to File → Options → Add-Ins.
   • At the bottom, select Excel Add-ins and click Go.
   • Click Browse and select the .xlam file you saved earlier.
   • Check the box next to the add-in name and click OK.

Once the add-in is loaded, the functions provided in the code will be available for use in any Excel workbook. You can now use these functions as regular Excel formulas.

Setting Up the Streams in the Excel Mass Balance Table

In the Excel mass balance table, each stream (feed, concentrate, tailings) is represented by a standard data set that includes the following data arranged in a column:

• Solid Mass Flowrate: The flowrate of the solid material in each stream.
• Water Flowrate: The flowrate of water in each stream.
• Component Percentages: The percentages of key components (such as metals, minerals, or elements) in each stream.

Each of these parameters is input into the corresponding column in the Excel table for every stream.

Main Features

Merging Streams

The SumMultipleStreams_MBA function combine multiple streams into one. It sums the solid mass flowrates, water flowrates, and component percentages from the selected streams and return the combined stream standard data.

Function Syntax:

SumMultipleStreams_MBA(streams As Range) As Variant

Parameters:

• streams (Range): The range that includes the streams standard data to be summed. Each stream should contain the solid mass flowrate, water flowrate, and component percentages.

This function is ideal for scenarios where you need to merge feed, concentrate, or tailings streams from different stages in the process.

Calculating Concentrate and Tailings Streams

The CalculateConcentrate_MPSE function calculates the concentrate stream based on the recovery values for each component – and water – for the input feed stream. It calculates how much of each component is recovered and return the concentrate standard data.

Function Syntax:

CalculateConcentrate_MPSE(inputStream As Range, recoveryToConcentrate As Range) As Variant

Parameters:

• inputStream (Range): The range containing the standard data for the feed stream.
• recoveryToConcentrate (Range): The range containing the recovery values for water and each component to the concentrate stream.

The SubtractStreams_MPSE function is used to calculate the tailings stream by subtracting the concentrate stream from the feed stream. It calculates the remaining mass and component percentages that are not recovered in the concentrate.

Function Syntax:

SubtractStreams_MPSE(feedStream As Range, concentrateStream As Range) As Variant

Parameters:

• feedStream (Range): The range containing the standard data for the feed stream.
• concentrateStream (Range): The range containing the standard data for the concentrate stream.

Calculate Recoveries

The CalculateRecoveryToConcentrate_MPSE function calculates the recovery of each component from a feed stream to a concentrate stream. It uses the solid and water mass flowrate along with component percentages of both the concentrate and feed streams to determine the recovery values.

Function Syntax:

CalculateRecoveryToConcentrate_MPA(concentrateStream As Range, feedStream As Range) As Variant

Parameters:

• concentrateStream (Range): The range containing the standard data for the concentrate stream.
• feedStream (Range): The range containing the standard data for the feed stream.

This function is particularly useful when you have fixed stream data (e.g., a measured or targeted concentrate composition) and need to determine the recoveries to the concentrate for each component. By calculating recovery values directly from the given stream data, this function helps you back-calculate and analyze process efficiency.

Example Calculations

Stream Merge

To illustrate the functionality of the mass balance codes, consider a simple case study for two streams:

Input Data:

Stream 1: 200 tons per hour (tph) with a concentration of 30% ore.

Stream 2: 150 tph with a concentration of 25% ore.

Output Stream: 350 tph (sum of Stream 1 and Stream 2), but the ore concentration is unknown.

Using the SumMultipleStreams_MBA function, you can calculate the combined stream’s solid mass flowrate, water flowrate, and component percentages.

Function Usage:

=SumMultipleStreams_MBA(C22:C25; D22:D25)

Assuming C22:C25 contains the data for Stream 1 and Stream 2 as follows:

.

Concentrate and Tailings Calculation

In this example, we will use the Mass Balance Add-in functions to calculate the mass balance of a flotation circuit. We will use CalculateConcentrate_MPSE to determine the concentrate flowrate based on the recovery to concentrate for each component, and SubtractStreams_MPSE to calculate the tailings flowrate by subtracting the concentrate flow from the feed stream.

Input Data:

• Feed Stream: 500 tph with a concentration of 20%Fe ore.
• Component 1 Recovery to Concentrate: 85% (e.g., recovery of a target metal or mineral to the concentrate).
• Component 2 Recovery to Concentrate: 75% (e.g., recovery of a second component).

Step 1: Calculate the Concentrate Stream

Using the CalculateConcentrate_MPSE function, we can calculate the concentrate stream based on the feed stream and the recovery values for each component.

Function Usage:

excel

=CalculateConcentrate_MPSE(A1:B1, C1:D1)

Assuming:

• A1:B1 contains the feed stream data:
  • A1: 500 tph (Feed Stream Solid Mass Flowrate)
  • B1: 20% Fe (Feed Stream Fe Ore Concentration)
• C1:D1 contains the recovery values:
  • C1: 85% (Component 1 Recovery to Concentrate)
  • D1: 75% (Component 2 Recovery to Concentrate)

The function will calculate the concentrate stream data.

Expected Output:

• Concentrate Stream Solid Mass Flowrate: (500 tph * 85%) = 425 tph
• Concentrate Stream Fe Ore Concentration: (20% * 85%) = 17%

Step 2: Calculate the Tailings Stream

Using the SubtractStreams_MPSE function, we can calculate the tailings stream by subtracting the concentrate stream from the feed stream.

Function Usage:

excel

=SubtractStreams_MPSE(A1:B1, E1:F1)

Assuming:

• A1:B1 contains the feed stream data (same as above)
• E1:F1 contains the concentrate stream data:
  • E1: 425 tph (Concentrate Stream Solid Mass Flowrate)
  • F1: 17% Fe (Concentrate Stream Fe Ore Concentration)

The function will calculate the tailings stream data.

Expected Output:

• Tailings Stream Solid Mass Flowrate: (500 tph – 425 tph) = 75 tph
• Tailings Stream Fe Ore Concentration: The remaining concentration not recovered in the concentrate.

Using these functions, you can easily perform mass balance calculations for a flotation circuit in Excel.

Flotation Circuit with Recirculating Load

In a flotation circuit, material is processed in stages. A recirculating load occurs when part of the tailings from the flotation process is returned to the feed to improve recovery or grade. This creates a loop of mass flows that must be accounted for in a mass balance calculation. The key challenge here is that the recirculating load affects both the concentrate and tailings calculations, as the same material is being reprocessed.

To calculate the mass balance in such a system, iterative calculations are needed. This is because the flowrates and component percentages in the recirculating load depend on the initial mass flowrates, and the calculations need to update iteratively until they converge to stable values.

In this example, we will calculate the mass balance of a flotation circuit with both rougher and scavenger stages, where the scavenger concentrate is recirculated back into the system. We will also combine the scavenger concentrate with the fresh feed to create the combined feed for the rougher stage. The recovery values for the rougher and scavenger stages will be provided in the last two columns for each stream.

Scenario Setup

Assume we have the following setup for the rougher-scavenger flotation circuit:

• Fresh Feed Stream (F): This stream enters the flotation circuit. It has a solid mass flowrate of 100 tons per hour (tph) and consists of 30% of mineral A and 70% of waste.
• Rougher Concentrate Stream (C_R): This stream is the output of the rougher flotation stage, containing the valuable mineral A. We will calculate this stream using the recovery values.
• Scavenger Concentrate Stream (C_S): This stream is the output of the scavenger flotation stage, also containing mineral A but at a lower grade than the rougher concentrate.
• Scavenger Tailings Stream (T_S): This is the waste stream after the scavenger stage, containing the residual minerals.
• Combined Feed Stream (F_C): The combined feed for the rougher stage consists of the fresh feed (F) and the scavenger concentrate (C_S).

Step 1: Enable Iterative Calculations in Excel

To correctly perform the mass balance calculations for a flotation circuit with recirculating load, you’ll need to enable iterative calculations in Excel. Here’s how:

1. Go to the Excel Options:

1. Click on the File tab.
2. Select Options at the bottom.
3. Enable Iterative Calculations:

2. In the Excel Options window, select the Formulas tab.
3. Under Calculation options, check the box next to Enable iterative calculation.
4. Set the Maximum Iterations (e.g., 100) and the Maximum Change (e.g., 0.001). These settings determine how many times Excel will recalculate until the results converge.
5. Click OK to apply the changes.

Once iterative calculations are enabled, you can use the provided functions to calculate the concentrate and tailings, including the recirculating load, with the process updating itself until the mass balance is balanced.

Now that the formulas are set and iterative calculations are enabled, you can run the mass balance calculations. Excel will automatically update the streams based on the recovery values for the rougher and scavenger stages. This iterative process will continue until the system converges, providing accurate mass balances for each stream in the flotation circuit.

Step 2: Define the Streams in Excel

In your Excel sheet, define the following columns for each stream:

Stream	Solid Mass Flowrate (tph)	Water Flowrate (tph)	Mineral A %	Waste %	Rougher Recovery	Scavenger Recovery
Fresh Feed (F)	100	200	30%	70%	–	–
Rougher Concentrate (C_R)	(Calculated)	(Calculated)	85% recovery	–	85%	–
Scavenger Concentrate (C_S)	(Calculated)	(Calculated)	60% recovery	–	–	60%
Scavenger Tailings (T_S)	(Calculated)	(Calculated)	–	–	–	–
Combined Feed (F_C)	(Calculated)	(Calculated)	(F + C_S)	–	–	–

Here, the Rougher Recovery and Scavenger Recovery columns will contain the respective recovery values for each stage.

Step 3: Define the Code Functions

1. Calculate the Rougher Concentrate (C_R): The CalculateConcentrate_MPSE function will calculate the amount of mineral A in the rougher concentrate stream, based on the rougher recovery value. The solid mass flowrate and water flowrate are also adjusted in the calculation.

Example formula for Rougher Concentrate (C_R):

excel

Copy code

=CalculateConcentrate_MPSE(F2:F3, G2:G3)

Where F2:F3 contains the feed stream’s solid mass flowrate and component percentages, and G2:G3 contains the recovery value for the rougher stage.

• Calculate the Scavenger Concentrate (C_S): Similarly, the scavenger concentrate is calculated using the CalculateConcentrate_MPSE function based on the scavenger recovery rate.

Example formula for Scavenger Concentrate (C_S):

excel

Copy code

=CalculateConcentrate_MPSE(T_S2:T_S3, G3:G4)

Where T_S2:T_S3 contains the scavenger tailings stream’s solid mass flowrate and component percentages, and G3:G4 contains the scavenger recovery value.

• Calculate the Scavenger Tailings (T_S): To calculate the scavenger tailings, we subtract the scavenger concentrate from the scavenger feed stream using the SubtractStreams_MPSE function.

Example formula for Scavenger Tailings (T_S):

excel

Copy code

=SubtractStreams_MPSE(T_S2:T_S3, C_S2:C_S3)

Where T_S2:T_S3 is the scavenger feed, and C_S2:C_S3 contains the scavenger concentrate.

• Combine Fresh Feed and Scavenger Concentrate for Combined Feed (F_C): The combined feed for the rougher stage is the sum of the fresh feed (F) and the scavenger concentrate (C_S). We can use the SumMultipleStreams_MPSE function to calculate the combined feed stream.

Example formula for Combined Feed (F_C):

excel

Copy code

=SumMultipleStreams_MPSE(F2:F3, C_S2:C_S3)

Where F2:F3 is the fresh feed and C_S2:C_S3 contains the scavenger concentrate stream.