Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Mass Balancing in Excel for Mineral Processing

Name: Mass Balancing in Excel for Mineral Processing
Rating: 3.5 (5 reviews)

Learn flowchart-based mass balancing of mineral processing data using Excel, VBA add-ins, and Information Theory.

Created byStephen Rayward, Marvelous Ajayi

Last updated 7/2024

English

What you'll learn

Apply a flowchart-based system in Microsoft Excel to perform mass balancing of mineral processing data.
Understand how Information Theory can be applied to mass balancing problems.
Compare the Information Theory approach with the conventional least squares minimization method and understand their key differences and advantages.
Understand how flowchart-based modelling can be applied in Excel.

Course content

5 sections • 24 lectures • 2h 25m total length

Introduction6:06
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.
What is mass balancing?5:45
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.
Where does mass balancing fit into optimisation of mineral processing plants?3:31
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.
The 1D Structure4:41
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.
The Information Theory approach to mass balancing9:19
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.
Information Theory versus least squares minimisation4:14
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.
Missing data9:42
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.
Limitations of Excel Solver5:28
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 Instructor developed his own optimisation system specifically customised and suitable for the mass balance problem.

Setting up the Mass Balance2:59
In this lecture you will copy the MMMassBal1D files using a zip folder. The files will be copied into a directory in the C Drive called MM.
This is all that is needed for installation.
Opening the Testcase4:43
In this lecture you will open up a test case and have a look at data that is to be mass balanced.
The various worksheets that you will explore are:
An example flowsheet
The data
The restructured data (in a format suitable for mass balancing)
You are encouraged to peruse the testcase workbook prior to commencing other lectures.

Understanding the Ribbon System12:43
If you are new to VBA and create command buttons, these are often difficult to navigate because they are contained in a worksheet.
A more elegant approach is to create Ribbon commands that are then easily available for navigation.
This lecture will introduce you to the Ribbon commands necessary to run the mass balance. However, some of the Ribbon commands have been automated so as to simplify the number of commands the student needs to run. Although this automation of commands simplifies running for this course, it also decreases the available functionality.
Brief Discussion of Templates9:11
The recommended prerequisite course for this lecture is “Effective use of Templates in Excel”. In this lecture we discuss the concept of templates. For mass balancing, there are two types of templates of interest:
The stream template
The unit template
The stream template changes if we are using sizes or assays or if the number and types of sizes or assays change. However changing the template is straightforward. The templates themselves use Named Ranges. The user should ensure that the created templates maintain the consistent use of the Named Ranges.
The unit template is fixed, because a single template is used that is applicable to the various unit classes. The unit classes are: Comminution, Splitters, and Separators. For the trial version Splitters are not available.
Creating a Flowchart7:54
The recommended prerequisite course for this lecture is “Flowcharting in Microsoft Excel”. However this lecture will provide a simple example of how to create a flowsheet. It is noted that there are two words used that are interchangeable: flowchart and flowsheet.
The instructor aims to use the work ‘flowchart’ when describing the methodology to create a flowchart using shapes and connectors.
The word ‘flowsheet’ is used to relate to a mineral processing plant. Thus a ‘connector’ for a flowchart is a ‘stream’ for a flowsheet, and a ‘shape’ in a flowchart is a ‘unit’ in a flowsheet.
A concept taught is naming the units and connectors in a consistent manner.
Creating a Template8:18
In this lecture you will learn how to change the stream template for different 1D data structures.
Creating the Mass Balance Tables7:19
Lecture 15 Creating the Mass Balance Tables
MMMassBalance1D uses the templates to automatically create the tables for the streams and units. You will learn the process by how this is done.
There are a number of steps involved (assuming the templates and flowsheet are created).
These steps are:
To configure the testcase (i.e. indicate the flowsheet and templates being used).
Run a command to read the flowsheet and configuration information.
Use the MMMassBalance1D Ribbon to specify that tables are to be created.
Run the command to create the tables.
Although the tables are created advanced Excel methods are also used to input the data directly from the raw data tables.

Inputting Mass Balance Data6:48
The main input at this stage is inputting the experimental data. There are a few ways this can be done:
Input manually
Input via cut and paste
Use a formula in the template to automate input.
The third method, being automated, is preferred.
This involves knowledge of various Excel functions. The course doesn’t focus on teaching Excel formulae. So in this lecture, you will learn how a specific formula is used.
When inputting data you do not need to input the standard deviations. This is a departure from the more conventional but manual approaches.
Performing a Mass Balance with Assay Data6:44
In this lecture, you will learn how to mass balance assay data where the data were inputted. This is a continuation of the previous lecture.
For assay data, mass balance of assay is always applicable at units; so although you don’t need to specify whether a unit is a comminution unit, it is good practise to do so.
The mass balance is divided into four steps:
Load the data
Specify the structure
Perform the balance
Unload the data.
In time, these four steps may be combined – but at this stage, it is convenient to separate the steps.
For all steps, there are two substeps:
Initialise
Run
The reasons for the steps and substeps largely have to do with technical reasons for the way VBA manages complex systems, and is not discussed in detail in the course.
MMMassBalance1D is available for a variety of structures, but here, we are mainly focusing on Size or Assay (but not both) and solid flow and water flow.
To specify the structure choose 1D: Assay and deselect water.
Deselecting water means that we are not mass balancing water flow, which also means we are not balancing total flow and percent solids.
So to run the mass balance you only need to implement the seven steps:
Load the data – Initialisation
Load the data – Run
Specify the structure
Perform the balance – Initialisation
Perform the balance - Run
Unload the data – Initialisation
Unload the data – Run
On completion of running the mass balance you are encouraged to vary the input data and rerun, checking the results each time.
The outcome of this lecture is that you now know all the steps to mass balance assays data for a mineral processing circuit.
Performing a Mass Balance with Size Data4:05
In this lecture you will learn that running a mass balance for size data is very similar to running the mass balance for assays data.
The main differences are:
The structure needs to be specified as Size.
Comminution units need to be specified as comminution units – that is mass of size components is not conserved.
You will run the mass balance for a set of data: TrialSize.xlsm.
The outcome of this lecture is that you now know all the steps to mass balance size data for a mineral processing circuit.
Mass Balancing Water Flow5:10
In this lecture, we extend the mass balance for size flow to include water.
The steps are analogous to the previous lecture other than water flow is selected.
Whenever you balance water flow and solid flow it is implied that you also mass balance percent solids and total flow.
One option is to balance 0D which means you can just balance water flow and solid flow (together with percent solids and total flow).
Similarly, you can just mass balance water flow or solid flow.
The outcome of this lecture is that you now know how to include water flow (and total flow and percent solids) in the mass balance.

Summary of the Course3:04
In this lecture, we will summarise what you have learnt from the course.
You have learnt that:
Information Theory can be applied to the mass balance problem
for an Information Theory-based mass balance, standard deviations are not required
mineral processing flowcharts can be constructed in Excel.
You have learnt how:
to create stream templates
to specify whether units conserve size distributions
to specify the structure for a mass balance
to use the Mass balance ribbon system to run the mass balance.
Extend the Mass Balance to Plausible Inference3:15
In this lecture you will learn (at overview level) a major advantage of the Information Theory approach. That is it enables plausible inference of missing data. You will also learn the difference between deductive inference and plausible inference.
Integrating the Mass Balance Data with a Database4:23
In this lecture, you will be introduced to the concept of linking Excel Mass balance data with a database.
The primary database discussed is Access.
The advantages are:
It provides a basis for data transformation from one format to another.
It allows the data to be stored in a database and then data from different plant audits can be compared.
Acknowledgements2:22
You will be informed of who helped to make this course and the preliminary courses possible.
Bonus7:58

Requirements

A desktop version of Microsoft Excel 2010 or later is recommended. The course software was originally developed using Excel 2010. Excel Online is not suitable for the exercises.
An IBM-compatible computer is required to run the software and exercises used in this course.
No prior knowledge of VBA programming is required.
Familiarity with creating flowcharts in Excel, using Named Ranges, working with cell styles and data validation, and understanding Information Theory will be helpful.
The following related Udemy courses are encouraged for additional preparation: 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 Practical Introduction to Information Theory

Description

Mass Balancing in Excel for Mineral Processing

Learn how to perform mass balancing of mineral processing data using Excel flowsheets, VBA add-ins, and Information Theory.

Mass balancing is an important part of mineral processing plant analysis and optimization. It helps engineers develop a complete and consistent set of data that can be used to understand plant performance and support the development of mathematical models.

In this practical course, you will learn how to create an Excel-based flowsheet, configure mass balance tables, and perform mass balancing using an Information Theory approach. You will work with size distributions, assay data, solid flow, and water flow while exploring how this method compares with conventional least squares minimization.

The course focuses primarily on mineral processing applications, with concepts that are also relevant to chemical engineering.

What Will You Learn?

By the end of this course, you will understand how to:

- Explain the purpose of mass balancing and its role in mineral processing plant optimization.
- Understand the Information Theory approach to mass balancing and compare it with least squares minimization.
- Create flowcharts and mineral processing flowsheets in Microsoft Excel.
- Set up stream templates, unit templates, and mass balance tables.
- Configure and run a mass balance using the MMMassBal1D system.
- Perform mass balancing on assay data and particle size distribution data.
- Include solid flow, water flow, total flow, and percentage solids in a mass balance.
- Understand how missing data can be estimated through deductive and plausible inference.
- Explore how Excel mass balance data can be integrated with a Microsoft Access database.

What Does the Course Cover?

1. Fundamentals of Mass Balancing

Understand what mass balancing is, why it is performed, and how it contributes to the optimization of mineral processing plants. Explore one-dimensional data structures, including assays and particle size distributions, and learn about the relationship between these data and solid and water flow variables.

2. Information Theory and Mass Balance Methods

Discover how Information Theory can be applied to mass balancing and learn about the differences between this approach and least squares minimization. You will also examine missing data, inference, and some limitations of Excel Solver when dealing with more complex mass balance problems.

3. Creating Excel Flowsheets and Mass Balance Tables

Learn how to set up the MMMassBal1D system, work with the Excel Ribbon commands, create flowsheets, configure stream and unit templates, and generate the tables needed to perform a mass balance.

4. Performing Mass Balancing in Excel

Work through the process of entering experimental data and running mass balances using assay data and particle size distributions. You will also learn how to extend the balance to include water flow, total flow, and percentage solids.

5. Further Applications

Explore plausible inference for estimating missing data and gain an introduction to integrating mass balance data with a Microsoft Access database.

Who Is This Course For?

This course is particularly relevant to:

- Mineral processing engineers and professionals who want to perform mass balancing using an Excel-based system.
- Chemical engineers interested in mass balance calculations and plant data analysis.
- Advanced Excel and VBA developers interested in flowchart-based modelling and visual modelling.
- Mathematicians interested in applying Information Theory to practical engineering problems.
- Engineering students seeking to understand mass balancing in mineral processing and related applications.

What Are the Prerequisites?

You do not need prior VBA programming knowledge to take this course.

However, familiarity with the following topics will be helpful:

- Creating flowcharts in Microsoft Excel.
- Using Named Ranges in Excel.
- Working with Excel templates, cell styles, and data validation.
- Understanding Information Theory.
- Basic mass balancing concepts.

Related courses covering Excel flowcharting, Named Ranges, templates, Excel Solver, and Information Theory are encouraged as supplementary preparation, but the listed topics are not all mandatory prerequisites.

Software Requirements

- A compatible IBM-based computer.
- A desktop version of Microsoft Excel. Excel 2010 or later is suggested, as the software was originally developed using Excel 2010.
- The course uses Excel files and VBA add-ins as part of the mass balancing system.

Start learning how to perform mass balancing in Excel and explore the application of Information Theory to mineral processing data.

Who this course is for:

Mineral processing professionals who want to perform mass balancing using an Excel-based system.
Chemical engineers interested in applying mass balancing techniques to engineering processes.
Advanced Excel and VBA developers who want to explore how Excel can be used to solve complex problems through flowchart-based modelling, also known as visual modelling.
Mathematicians interested in applying Information Theory to practical problems.

Mass Balancing in Excel for Mineral Processing

What you'll learn

Explore related topics

Course content

Introduction8 lectures • 49min

Setup2 lectures • 8min

Creating your own Mass Balance5 lectures • 45min

Running the Mass Balancing4 lectures • 23min

Closing Remarks5 lectures • 21min

Requirements

Description

Who this course is for: