Case Study: VisualizingGaussian PlumeData with Excel

Source: Excel model and case study by Robert Gotwals, Morehead Planetarium and Science Center. The algorithm for the model is the classic "Gaussian plume" algorithm, which uses a statistically-based lookup method to calculate coefficient values for different atmospheric and meteorological conditions. More information on this model is available at this link.

Introductory Readings

Background Science

Smokestacks are everywhere! The purpose of smokestacks is to take the unwanted gases that result from industrial processes and emit them into the atmosphere. In some cases, the gases emitted are nothing more than water vapor in the form of steam. In the majority of the cases, however, these gases are something more dangerous to the environment. A good example is sulfur dioxide, or SO2. Sulfur dioxide in the form of a gas is produced in many industrial processes, especially those that burn coal as a way to produce energy. SO2 is particularly bad in that it mixes with water in the air to form H2SO4, or sulfuric acid, better known as "acid rain".

Any gas coming out of a smokestack is called an emission. The goal of air pollution control is to reduce the amount of emissions coming out of the stacks and being released into the environment. There are several ways to do this:

Reduce the amount of coal being burned in favor of cleaner methods of producing energy (hydroelectric, wind, solar, nuclear, etc.). These methods are often more expensive than burning coal, and create another set of problems (for example, what to do with the nuclear waste products!)

Put filters, electrostatic filters, scrubbers, and other technologies in the stacks to remove the harmful pollutants being emitted from the stack. These technologies are very expensive, thus increasing the cost of the energy to the industry. This additional cost is paid for by the customers using the products of that industry!

Increase the height of the stacks. This does not reduce the emissions, but puts the emissions higher up into the atmosphere, where the upper level winds can disperse the pollutants away from the ground. There is, of course, a limit to how high you can build these stacks. In addition, very tall stacks are ugly, dangerous to air traffic, and expensive to maintain.

What we are concerned about in this model is the concentration of pollution downwind from the stack. As the pollutants are emitted from the smokestack, they mix with the air and are carried downwind, away from the stack. How far they are carried depends on a number of factors:

Wind speed (in units of meters)

Height of the stack (real height and effective height, in units of meters)

Diameter of the stack opening (in units of meters)

Emission rate (the amount of gas coming out of the stack, in units of grams per second)

Gas exit velocity (the speed of the gas as it comes out of the stack, in meters per second)

Gas exit temperature (how hot the gases are, in degrees Celsius)

Ambient temperature (the temperature of the surrounding air, in degrees Celsius)

The atmospheric stability condition (a 1-6 measure of the meteorology of the surrounding air, from very unstable (1) to stable (6)

Given this data, we can calculate the concentration of the pollutant (in units of micro-milligrams per cubic meter, or mmg/m3) at various locations downwind from the stack, usually measuring from 0 kilometers (the base of the stack) down to 100 km (62 miles) from the stack.

What we wish the model to show us is the concentration of the pollution at various distances downwind from the stack, given different factors (wind speed, stack height, etc.). In other words, we wish to visualize the answer to this question:

How does the stability of the atmosphere affect the transport of emissions downwind from the smokestack?

Objectives of the Case Study

The objective of this case study is to investigate the effect of a single variable - atmospheric stability - on the downwind transport of the pollutants. We wish to know which type of atmosphere, from stable to unstable, has more effect downwind from the smokestacks.

Your visualization objective is to produce this visualization. There are two separate visualizations, one a surface plot and one a line plot.

About the dataset

In this case study, you are presented with a complete model that contains all of the formulas and data for the visualization. In a classroom environment, depending on the age of the students and the curricular goals, we would have the students build this spreadsheet from scratch.

The dataset is primarily the concentrations of the pollutant as a function of windspeed and downwind distance. In this case study, we will change the visualization by simply changing the single value representing the atmospheric stability.

Building the Visualization

Data Retrieval

Data Filtering

There is no data filtering for this case study, as you are presented with a working model minus the visualization of the computational results.

Data Rendering

In this activity, you are looking to visualize the downwind concentrations of the pollutation as a function of downwind distance (x axis) and windspeed (y-axis). We wish to visualize this data both as a surface plot and as a line plot:

There are two charts to make, a surface plot and a line graph. Each is described separately. It does not matter which one you do first"

Surface Plot

Highlight the data from A13 to A23 (the wind speed data) and then the data from E13 to O23 (the distance downwind data and the concentrations)

Insert a graph from the "Insert" menu. Choose a three-dimensional surface plot, then hit "Next"

The dataset for the surface plot needs to be displayed as columnar data, so choose the appropriate choice.

Highlight the "Data Range" box, delete that information, and then select the concentration data (E14 to O23). Click on the "Series" tab on this page.

You should now see a list under "Series". We need to individually set these to the distances downwind. Click on "Series 1", click into the "Name" box, and type a "0". Now click on "Series 2", enter "0.5", and continue doing this for the entire downwind range.

Highlight and delete the "Category X labels", and highlight the windspeed data (A14 to A23). Click on "Next".

On the "Chart Options" page, type your title. The x-axis is the windspeed, the y-axis is the distance downwind, and the z-axis is the concentration. Label your graph appropriately.

Click on the "Finish" button, and your graph appears in the spreadsheet.

With the graph still selected, click on "3-D View" under "Chart". Rotate the chart until you can see the graph from the side.

Line Graph

Highlight the data from A13 to A23 (the wind speed data) and then the data from E13 to O23 (the distance downwind data and the concentrations)

Insert a graph from the "Insert" menu. Choose a line plot, then hit "Next"

The dataset for the surface plot needs to be displayed as row data, so choose the appropriate choice.

Highlight the "Data Range" box, delete that information, and then select the concentration data (E14 to O23). Click on the "Series" tab on this page.

You should now see a list under "Series". We need to individually set these to the distances downwind. Click on "Series 1", click into the "Name" box, and type a "0". Now click on "Series 2", enter "0.5", and continue doing this for the entire downwind range.

Highlight and delete the "Category X labels", and highlight the windspeed data (A14 to A23). Click on "Next".

On the "Chart Options" page, type your title. The x-axis is the windspeed, the y-axis is the distance downwind, and the z-axis is the concentration. Label your graph appropriately.

Click on the "Finish" button, and your graph appears in the spreadsheet.

Analysis of the Visualization

Once you have created your visualizations, you should perform an analysis of the behavior of the plume as a result of atmospheric stability. To do this, change the value of the stability in Cell E8 from one to six. You are looking to examine not only the downwind distance that the plume travels but also the concentration of the downwind pollution. Which atmospheric condition causes the plume to travel the farthest? Which plume causes the plume to have the highest concentration?