Mass Balancing using Excel Solver

About 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. 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.