Mass Balancing using Excel Solver

Excel Solver is applied to a fundamental nonlinear optimisation problem (mass balancing in mineral processing).
Free Download What you'll learn
To learn how to use Excel Solver.
Learn how to mass balance mineral processing data using Microsoft Excel.
Understand the mass balance problem in mineral processing.
Use a conventional approach e.g. Least Squares Minimisation to solve a mass balance problem.
Understand the advantages and disadvantages of using Excel Solver.
Apply Excel Solver to mass balance problems.
Apply mass balance extensions in the context of using Excel Solver.
Requirements
Be familiar with Microsoft Excel. No formal prerequisites necessary.
Have strong enough mathematical understanding to be aware of what is meant by an optimisation problem.
The mass balance problem is a straightforward problem – and should be understandable regardless of whether one has familiarity with mineral processing or chemical engineering.
Description
Course OverviewOptimisation problems are problems where one seeks to maximise (or minimise) a function by changing variablesExcel 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 BalancingSection 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 problem3. A brief explanation of Least squares minimisation1.1 Lecture 1 Overview1.1.1 ObjectivesYou will understand what is covered in the course – which primarily consists of two concepts:Mass balancingUsing Excel Solver1.1.2 1.1.2 DescriptionMass 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 problem1.2.1 ObjectiveYou will understand the mass balance problem in mineral processing.1.2.2 DescriptionThe 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 minimisation1.3.1 ObjectiveThe mass balance problem is conventionally solved using least squares minimisation. Hence you will learn to apply least squares minimisation.1.3.2 DescriptionLeast 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 minimisationWeighted least squares minimisationNon-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 SolverExcel 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 Solver5. Setting up Excel Solver6. Applying Excel Solver (Ex1)7. Two Products Exercise (Ex2)8. Water Flow Exercise (Ex3)9. Reducing Constraint Equations and variables2.1 Lecture 4 Using Excel Solver2.1.1 ObjectiveYou will understand the advantages of using Excel Solver for optimisation problems. You will also discover some disadvantages of using Excel Solver.2.1.2 DescriptionExcel 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 Solver2.2.1 ObjectiveAlthough 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 DescriptionExcel Solver is made available by using the options in Excel.2.3 Lecture 6 Applying Excel Solver2.3.1 ObjectiveYou will apply Excel Solver to a simple problem.2.3.2 DescriptionExcel 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 Exercise2.4.1 ObjectiveYou will solve a mass balance problem where there are two products coming from the unit.2.4.2 DescriptionA 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 Flow2.5.1 ObjectiveYou will mass balance both water flow and solid flow.2.5.2 DescriptionWhilst 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 variables2.6.1 ObjectiveYou will be able to reduce the number of constraining equations and variables used by the Solver algorithm.2.6.2 DescriptionSolver 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 extensionsBy 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 problem11.  The 2D Mass Balance problem12. Estimating Solid Flows from Assays13. Treatment of Remnant MineralsThere are of course other extensions, but this is sufficient in the context of using Excel Solver.3.1 Lecture 10 The 1D Mass Balance Problem3.1.1 ObjectiveYou will be able to mass balance assay data.3.1.2 DescriptionThis 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 problem3.2.1 ObjectiveYou will be able to mass balance assay data within size-classes.3.2.2 DescriptionIn 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 Assays3.3.1 ObjectiveYou will be able to estimate solid flows using assays.3.3.2 DescriptionMass 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 Minerals3.4.1 ObjectiveYou will learn the options on how to extend mass balancing of assays to include remnant minerals.3.4.2 DescriptionIf 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 ClosingThe closing section consists of the following lectures14. Closing lecture15. Bonus Lecture4.1 Lecture 14. Closing Lecture4.1.1 ObjectiveYou will be able to summarise knowledge gained by the course. Acknowledgement to those who have contributed is included.4.2 Lecture 15. Bonus Lecture4.2.1 ObjectiveTo discuss extension courses in preparation.4.2.2 DescriptionThe course was introduction only. The scope was limited to using Excel Solver.There are extension courses and future courses being developed and planned.
Who this course is for
The main target audience is mineral processors.
Chemical Engineers are also encouraged to enrol in course.
Anyone involved in optimisation wishing to learn Excel Solver is encouraged to enrol.
Homepage

https://www.udemy.com/course/mass-balancing-using-excel-solver/