Flowchart-based Mass Balancing in Excel

Free Download Flowchart-based Mass Balancing in Excel
Published 7/2024
MP4 | Video: h264, 1280×720 | Audio: AAC, 44.1 KHz, 2 Ch
Language: English | Duration: 2h 25m | Size: 1.66 GB
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 INTRODUCTIONThe 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 IntroductionThe 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 StructureWhilst 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 BalancingInformation 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 MinimisationWhilst 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 DataFor 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 SolverIn 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.SECTION 2 SETUPIn this section, you will learn how to set up the Mass balance system. Here, the mass balance system is called MMMassBal1D whereas MMMassBal is a more generic term applicable to all data structures.You will learn how to copy the files, and have a brief look at a testcase.Lecture 9 Setting up the Mass BalanceIn 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.Lecture 10 Opening the TestcaseIn this lecture you will open up a testcase and have a look at data that is to be mass balanced.The various worksheets that you will explore are:An example flowsheetThe dataThe restructured data (in a format suitable for mass balancing) You are encouraged to peruse the testcase workbook prior to commencing other lectures.SECTION 3 CREATING YOUR OWN MASS BALANCEIn this section you will learn how to create a mass balance. You will also examine the Mass Balance navigation using Ribbon commands.You will look more deeply at a testcase, and identify the steps required to create a mass balance.These steps are:Create a flowsheet (or flowchart)Create a stream templateCreate the mass balance tablesLecture 11 Understanding the Ribbon SystemIf 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.Lecture 12 Brief Discussion of TemplatesThe 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 templateThe unit templateThe 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.Lecture13 Creating a FlowchartThe 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.Lecture 14 Creating a TemplateIn this lecture you will learn how to change the stream template for different 1D data structures.Lecture 15 Creating the Mass Balance TablesMMMassBalance1D 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.SECTION 4 RUNNING THE MASS BALANCINGIn this section, you will learn how to run the mass balance system.This consists of 4 stages:Inputting mass balance dataPerforming a mass balance with assay data.Performing a mass balance with size data.Introducing water into the balance.Lecture 16 Inputting Mass Balance DataThe main input at this stage is inputting the experimental data. There are a few ways this can be done:Input manuallyInput via cut and pasteUse 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.Lecture 17 Performing a Mass Balance with Assay DataIn 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 dataSpecify the structurePerform the balanceUnload 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:InitialiseRunThe 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 – InitialisationLoad the data – RunSpecify the structurePerform the balance – InitialisationPerform the balance – RunUnload the data – InitialisationUnload the data – RunOn 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.Lecture 18 Performing a Mass Balance with Size DataIn 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: size distribution testcase.The outcome of this lecture is that you now know all the steps to mass balance size data for a mineral processing circuit.Lecture 19 Mass Balancing Water FlowIn 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.SECTION 5 CLOSING REMARKSIn this section, we will summarise what you have learnt from the course. You will also be introduced to extension concepts such as how mass balancing can be extended to: infer missing data and integrate the Excel Mass Balance data with an Access database.Lecture 20 Summary of the CourseIn 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 problemfor an Information Theory-based mass balance, standard deviations are not requiredmineral processing flowcharts can be constructed in Excel.You have learnt how:to create stream templatesto specify whether units conserve size distributionsto specify the structure for a mass balanceto use the Mass balance ribbon system to run the mass balance.Lecture 21 Extend the Mass Balance to Plausible InferenceIn 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.Lecture 22 Integrating the Mass Balance Data with a DatabaseIn 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.Lecture 23 AcknowledgementsYou will be informed of who helped to make this course and the preliminary courses possible.Lecture 24
Who this course is for
Mineral processors seeking an easy to use Mass Balance System.
Chemical Engineers seeking an easy to use Mass Balance System.
Advanced Excel and VBA developers seeking to know how Excel can be used for a complex problem. Specifically, those developers who are interested in Flowchart-based modelling – which can also be called Visual Modelling.
Mathematicians interested in the application of Information Theory to practical problems.
Homepage

https://www.udemy.com/course/flowchart-based-mass-balancing-in-excel/