Mass Balancing using Excel Solver

Course Overview

Optimisation problems are problems where one seeks to maximise (or minimise) a function by changing variables

Excel Solver is a publicly available addin in Microsoft Excel that can be applied to optimisation problems. Whilst Excel Solver has strengths and weaknesses, it is suitable for simple optimisation problems.

The class of optimisation problems that are the focus of the course is ‘the mass balance problem’. The mass balance problem is a fundamental problem in mineral processing and chemical engineering – and it involves minimising the difference between original measured value and new estimated values subject to mass conservation constraints. The conventional approach is to treat the problem as a least squares problem (quadratic minimisation or nonlinear problem).

1 Section 1 Introduction to Mass Balancing

Section 1 is an introduction. Section 1 is split into three lectures:

1. An overview of the course,

2. A brief explanation of the Mass Balancing problem

3. A brief explanation of Least squares minimisation

1.1 Lecture 1 Overview

1.1.1 Objectives

You will understand what is covered in the course – which primarily consists of two concepts:

• Mass balancing
• Using Excel Solver

1.1.2 1.1.2 Description

Mass balancing is a technique used in mineral processing to reconcile plant data so that it is consistent. Mass Balancing is conventionally solved using least squares minimisation. Hence the problem is to identify a least squares objective function that is minimised subject to constraints.

Excel Solver is an addin that can solve a variety of optimisation problems including the nonlinear mass balance problem.

1.2 Lecture 2 A brief explanation of the mass balancing problem

1.2.1 Objective

You will understand the mass balance problem in mineral processing.

1.2.2 Description

The mass of what goes into a unit must equal what comes out. This is called mass conservation. There are a variety of mass balance problems – but here the core problem is that ore properties are measured before and after the unit. The measured ore properties are subject to sampling error; and therefore the measurements are generally not consistent with mass conservation. They are therefore adjusted to allow a consistent understanding of ore flow through units.

1.3 Lecture 3 Least squares minimisation

1.3.1 Objective

The mass balance problem is conventionally solved using least squares minimisation. Hence you will learn to apply least squares minimisation.

1.3.2 Description

Least square minimisation is an extension of maximum likelihood theory. You may recall least squares minimisation as the basis of regression.

There are two branches of least squares minimisation:

• Non-weighted least squares minimisation
• Weighted least squares minimisation

Non-weighted least squares minimisation simply means that the new estimates are as close as possible to the original estimates.

Weighted least squares means that we take into account the expected departure of the measurements from the actual value.

2 Section 2 Solver

Excel Solver is an addin freely available at all licensed Excel users. You will learn how to use Excel Solver and apply it to straightforward mass balance problems. The section is split into 6 lectures:

4. Using Excel Solver

5. Setting up Excel Solver

6. Applying Excel Solver (Ex1)

7. Two Products Exercise (Ex2)

8. Water Flow Exercise (Ex3)

9. Reducing Constraint Equations and variables

2.1 Lecture 4 Using Excel Solver

2.1.1 Objective

You will understand the advantages of using Excel Solver for optimisation problems. You will also discover some disadvantages of using Excel Solver.

2.1.2 Description

Excel Solver is an addin that is available to all Excel users. It is used to optimise (minimise or maximise) an objective function, represent by a formula in cell, subject to constraints, and by varying cell values (variables).

2.2 Lecture 5 Setting up Excel Solver

2.2.1 Objective

Although Excel Solver is available to all users not all users will know how to access it. You will learn how to gain access to Excel Solver.

2.2.2 Description

Excel Solver is made available by using the options in Excel.

2.3 Lecture 6 Applying Excel Solver

2.3.1 Objective

You will apply Excel Solver to a simple problem.

2.3.2 Description

Excel Solver minimises or maximises an objective. The objective is a function in an Excel cell. Excel Solver also requires variables that are to be adjusted. These variables are cells. It is common to add constraints.

The problem given is a simple mass balance problem. Harder problems will then be introduced into successive lectures.

2.4 Lecture 7 Two Products Exercise

2.4.1 Objective

You will solve a mass balance problem where there are two products coming from the unit.

2.4.2 Description

A more complex mass balance problem is constructed with two products. Excel Solver is applied to minimise the least squares error.

2.5 Lecture 8 Water Flow

2.5.1 Objective

You will mass balance both water flow and solid flow.

2.5.2 Description

Whilst mineral processors are most interested in solid flow (and of course assays), water flow is additional information which is important for both unit models and for improving the estimate of solid flow.

2.6 Lecture 9 Reducing Constraint Equations and variables

2.6.1 Objective

You will be able to reduce the number of constraining equations and variables used by the Solver algorithm.

2.6.2 Description

Solver has a limit on how many variables that can be adjusted.

The equations can be adjusted to reduce the number of variables thereby improving the possibility of convergence.

For more complex problems one therefore needs to have a good understanding of the problem to incorporate into the problem methods that simplify the problem.

3 Section 3 Mass Balancing extensions

By this stage in the course you should be competent in using Excel Solver. For the remainder of the course you will focus on mass balance extensions.

There are four lectures:

10. The 1D Mass Balance problem

11.  The 2D Mass Balance problem

12. Estimating Solid Flows from Assays

13. Treatment of Remnant Minerals

There are of course other extensions, but this is sufficient in the context of using Excel Solver.

3.1 Lecture 10 The 1D Mass Balance Problem

3.1.1 Objective

You will be able to mass balance assay data.

3.1.2 Description

This is the first lecture which focuses on dimensionality of ore properties. Examples of 1D data are size distribution or assays – but not both (which is 2D).

3.2 Lecture 11 The 2D Mass Balance problem

3.2.1 Objective

You will be able to mass balance assay data within size-classes.

3.2.2 Description

In this lecture we extend the mass balance problem to 2D data (assays within sizes). The course does not go to more complex dimensions even though 3D mass balancing is also a valid subproblem. The problem complexity is limited because of the limitations of Excel Solver.

3.3 Lecture 12 Estimating Solid Flows from Assays

3.3.1 Objective

You will be able to estimate solid flows using assays.

3.3.2 Description

Mass balancing thus far has largely been used to adjust measured values. It can also be used to estimate unmeasured variables. In this case we estimate unmeasured solid flow values.

3.4 Lecture 13 Treatment of Remnant Minerals

3.4.1 Objective

You will learn the options on how to extend mass balancing of assays to include remnant minerals.

3.4.2 Description

If we have say copper (Cu) and lead (Pb) and we focus on these we can also consider the remnant mineral. We can do this by either adding a constraint or adding a variable; but the effect is to ensure that the sum of the focus minerals: Cu and Pb, does not exceed 1 or (100%).

4 Section 4 Closing

The closing section consists of the following lectures

14. Closing lecture

15. Bonus Lecture

4.1 Lecture 14. Closing Lecture

4.1.1 Objective

You will be able to summarise knowledge gained by the course. Acknowledgement to those who have contributed is included.

4.2 Lecture 15. Bonus Lecture

4.2.1 Objective

To discuss extension courses in preparation.

4.2.2 Description

The course was introduction only. The scope was limited to using Excel Solver.

There are extension courses and future courses being developed and planned.