Flowchart-based Mass Balancing in Excel

How to Mass Balance Mineral Processing Data using Excel Flowcharts and applying Information Theory via VBA Add-ins


What you'll learn
Apply a method of mass balancing using a flowchart-based system in Excel.
Understand how Information Theory can be used for mass balancing.
Understand the advantages of using Information Theory compared to the more conventional least squares approach.
Understand how flowchart-based modelling can be applied in Excel.

Requirements
Encouraged prerequisites are the Udemy courses: Flowcharting in Microsoft Excel, Effective use of Named Ranges, Mastering Named Ranges, Arrays and VBA in Excel, Effective use of Templates in Excel, Mass Balancing using Excel Solver and Practical Introduction to Information Theory.e of Named Ranges
The software and exercises used in this course assume an IBM-compatible system.
The version of Excel used should be relatively modern, and should be a desktop Excel rather than an online Excel. The software was originally developed using Excel 2010 – so it is suspected that versions of Excel must be 2010 or later.
The learner does not need to know VBA.
It would be helpful for the learner to have skills in the following: creating a flowchart, use of Named Ranges, use of cell styles or data validation and Information Theory.

Description
SECTION 1 INTRODUCTION
The introduction largely provides all the background material (such as the information provided by the prerequisites) but does not go into the same level of depth as the prerequisites.
By the end of the Introduction section you will have a good understanding of the mass balancing problem to be solved and an overview of the encouraged prerequisites.
Although mass balancing is applicable to both mineral processing and chemical engineering, the focus in this course is mineral processing.
Lecture 1 Introduction
The introduction lecture provides an overview of the course. The course uses flowcharting in
Excel
and
VBA
add-ins to mass balance data from mineral processing plants (or chemical engineering plants) using an approach called Information Theory.
The structure of a mass balance can be complex, represented by ‘dimensionality’. In this course, we focus on 1D meaning either size distributions or assays. The course is effectively trial software of a system called
MMMassBal
.
MMMassBal
can mass balance data that are more complex than 1D – so 1D here is primarily used as a trial version.
The Introduction also explains the encouraged prerequisite courses and what is covered in these.
By the end of the Introduction, you will have an overview understanding of the mass balancing problem to be solved.
Lecture 2 What is Mass Balancing?
This lecture provides an overview of mass balancing. Mass balancing is applicable to both mineral processing and chemical engineering. You will learn: what is mass balancing and the reasons to perform a mass balance.
Lecture 3 Where does Mass Balancing fit into Optimisation of Mineral Processing Plants?
In this lecture you will learn (at overview level) the steps involved in optimising the performance of a mineral processing plant and where mass balancing fits in. As stated in the previous lecture there are several reasons to Mass Balance data. However in the context of mineral processing optimisation, mass balancing is used to obtain a complete and consistent set of data from which one can then develop mathematical models of units. These unit models are then used in a simulation system to enable optimisation of the performance of a plant.
Lecture 4 The 1D Structure
Whilst mass balancing can be applied to more complex data structures such as 2D and 3D, in this course the focus is the simpler 1D structure. You will learn what is meant by 1D, and how size distributions and assays correspond to 1D structures.
Although the course enables mass balancing of 1D data, there are also 0D variables such as solid flow and water flow. You will understand the relationship between the 0D variables and 1D variables.
Lecture 5 The Information Theory approach to Mass Balancing
Information Theory is based on describing unit models as probabilistic systems. If one considers mass balancing, mineral processing does indeed correspond to a probabilistic system. For example, feed particles going to a cyclone have a probability of going to the heavies or light streams.
For this reason, Information Theory is a logical approach to modelling mineral processing and consequently Information Theory is immediately applicable to mass balancing.
This lecture does not explain Information Theory in depth which is considered in far more detail in the associated prerequisite course.
Lecture 6 Information Theory versus Least Squares Minimisation
Whilst Information Theory is well-suited to mass balancing, the more conventional approach is least squares minimisation. In this lecture, you will learn the differences between the two methods, and the pros and cons of the two methods (compared to each other).
You will learn that the Information Theory approach is much simpler for a user than the least squares approach.
From a technical mathematical viewpoint there are some key differences between Information Theory and least squares. The least squares objective is quadratic, and when differentiated is near-linear. In contrast the Information Theory approach remains nonlinear before and after differentiation.
Lecture 7 Missing Data
For most plant surveys, not all data is measured. This is because using conventional mass balancing the missing data can be estimated using mass balancing.
Estimation of missing data here is called inference. There are two types of inference: deductive inference and plausible inference. Here we are focusing on deductive inference.
A common set of data that are missing are solid flow and water flow. These are the 0D variables. However solid flow can be inferred from the higher level data. In this case, the higher level data are size distributions and assays. However water flow is often problematic, with limited effort during plant surveys to measure water flow directly. Instead, solid flow or total flow, and percent solids is used to infer water flow.
Lecture 8 Limitations of
Excel

Solver
In a prerequisite course the instructor explains how to solve mass balance problems using
Excel Solver
.
One would have liked to use
Excel

Solver
for more complex mass balances, but
Excel

Solver
is limited.
These limitations are:
Can only handle a small number of variables (about 64).
Variables must be on the same worksheet.
Has major difficulty in dealing with nonlinear objectives.
Now there are of course many commercial Solvers. The instructor has not specifically discussed these. Instead, the

More Info