USING IMPORTED DATA IN EXCEL

By Ruth Starkins

We have learned how to do simple graphing in Excel, and how to model it.
However, in this class we will be making graphs of data that is imported
from the Internet. There are several ways to do this, and we will learn
one today, in preparation for an activity on atmospheric lapse rates. To
do this we will be using data from a site at the University at Wyoming
that includes data taken from radiosonde instruments at upper levels of
the atmosphere.

First, open a blank Excel workbook to record your data.

Next, we will go to the web page and locate the data we want. The web
page is <http://www.weather.uwyo.edu/upperair/sounding.html>

(If the Internet is on, you may go directly to the link by clicking on
it as you hold the control key) The page below should appear. For
today's activity we will be using data from 2 cities on this map: Little
Rock, Arkansas(LZK) and Miami, Florida(MFL). Find those two locations on
the map.

![](media/image1.png){width="5.992361111111111in"
height="4.459722222222222in"}

Notice that at the top of the web page there are buttons allowing us to
change our parameters. We will be changing the data for the date. Change
the date to 1975, January 22. The last 2 boxes allow us to choose a
range of times for our data. For this activity we will just be using
12Z.(We will discuss Zulu time(Z) at a later data.) So, make sure that
the "From" & "To" boxes both show 22/12Z for the 22^nd^ at 12Z time. The
box below shows the correct setup.

![](media/image2.png){width="3.25in" height="0.3784722222222222in"}

![](media/image3.png){width="2.627083333333333in"
height="1.8777777777777778in"}
![](media/image4.png){width="3.1256944444444446in" height="2.7125in"}

(There are several ways to import the data. The following explanation is
just one method.)

I have found that there can be glitches when we copy from the very top,
so highlight the data starting at the PRES for pressure. (To highlight,
use either the mouse or arrow keys to select the desired text.) Now, we
could highlight the data all the way down to the last data, but since we
don't need it all for this activity, and since there is missing data at
higher altitudes, we will just take the data up to almost 5,000 meters
in height. [Like this:]{.mark}

![](media/image5.png){width="5.992361111111111in"
height="4.459722222222222in"}

After highlighting the data, copy it to the windows Clipboard, either by
using the right mouse button menu, or by using the Edit commands in your
Windows screen menus.

Now, let us go back to the Excel spreadsheet we opened earlier.
Left-Click in the A3 cell and "Paste" the data there(again, either using
the right mouse button or Edit menu.) Be very careful at this point not
to click anywhere else in the spreadsheet until you complete the next
set of steps that will format the data properly for the spreadsheet. Go
to the Data menu at the top and find Text to Columns(shown below.) Click
on this and Excel will

![](media/image6.png){width="4.0in"
height="3.703472222222222in"}![](media/image7.png){width="2.297222222222222in"
height="1.64375in"}![](media/image8.png){width="2.297222222222222in"
height="1.64375in"}

> ![](media/image9.png){width="2.7090277777777776in"
> height="1.9416666666666667in"}

Your data should now all be lined up properly in nice columns on your
spreadsheet. If not, delete the data and go back and try it again. We
left the 1^st^ 2 rows blank for titles, so now put Miami and the date on
the first 2 lines. (Do this also for Little Rock when done.)

When you have this correct, go back to the web site, get the Little Rock
data for the same date and time, and paste it into Sheet 2 on the same
Excel spreadsheet, in the same cell locations as you did for the Miami
data. You now have 2 sets of data for the same date and time, for 2
different cities.

At this point, we could do many things with the data we downloaded.
Notice that we have data on air pressure, temperature, dew point,
relative humidity and wind speed (SKNT), among other things. We will be
using all of these to create graphs at some point. Today we will use the
height and temperature data to create lapse rate graphs and answer some
questions using them.

Let's now make the Excel charts that we need. We only need height and
temperature data today. Normally, we might cut out the rest of the data,
or paste those 2 pieces of data into a new spreadsheet. However, we are
going to do this differently to practice putting data into Excel.

Also, you should remember that in a previous lesson we discussed using
the independent variable as the x value, and the dependent as the y
value. In this case, we will not do that. Why? Well, the temperature
depends on height, which makes temperature dependent and height
independent. However, it is useful to be able to see the height as going
upward, which works better on the y-axis. We will therefore use height
as y and temperature as x. So, let's make a
graph!![](media/image10.png){width="2.2597222222222224in"
height="2.55in"}

![](media/image11.png){width="2.7506944444444446in"
height="2.7506944444444446in"}

![](media/image12.png){width="3.035416666666667in" height="0.38125in"}

![](media/image13.png){width="2.9451388888888888in" height="3.33125in"}

![](media/image14.png){width="2.876388888888889in"
height="2.7666666666666666in"}
![](media/image15.png){width="2.4618055555555554in"
height="2.7888888888888888in"}

![](media/image16.png){width="2.652083333333333in"
height="2.9993055555555554in"}

![](media/image17.png){width="2.7555555555555555in"
height="3.126388888888889in"}

**Lapse Rate Activity Name\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
Section\_\_\_\_\_**

As you know, temperature generally decreases with height. This change is
called the lapse rate. The average global lapse rate is about 0.6°C per
100 meters. However, lapse rates vary due to location and time. The data
from this activity shows different lapse rates in two cities, with a
cold front separating the cities; one of the cities is influenced by a
polar air mass, while the other is affected by a tropical air mass.

1.  Calculate the average lapse rate in °C/100 meters between the
    surface and \~ 5000 meters, for both Miami and Little Rock, using
    the data in your spreadsheet. If you know how to do this using Excel
    functions, you may do this. If not, figure it out with your
    calculator. (We will discuss functions in Excel at another time.)

> Lapse rates: Miami \_\_\_\_\_\_\_\_\_\_\_\_\_°C/100m Little Rock
> \_\_\_\_\_\_\_\_\_\_\_\_\_°C/100m

2.  How do the values calculated in #1 compare to the average
    value?\_\_\_\_\_\_\_\_\_\_\_

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

3.  Temperature inversions occur when temperature increases, or stays
    the same with increased height, instead of temperature decreases. On
    your graph, highlight any temperature inversions you see.

4.  Calculate the lapse rate in Miami and Little Rock for the highest
    approximate 1500 meters shown in the spreadsheet. . If you know how
    to do this using Excel functions, you may do this. If not, figure it
    out with your calculator. (We will discuss functions in Excel at
    another time.)

> Lapse rates: Miami \_\_\_\_\_\_\_\_\_\_\_\_\_°C/100m Little Rock
> \_\_\_\_\_\_\_\_\_\_\_\_\_°C/100m

5.  How do the values calculated in #4 compare to the average
    value?\_\_\_\_\_\_\_\_\_\_\_\_

\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

6.  You should see greater uniformity in the values in #5, compared to
    #2. What reason can you offer for the greater uniformity in lapse
    rates at higher elevations above the
    surface?\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

7.  What reasons can you give for the differences in the lapse rates in
    Little Rock and Miami?
    \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

8.  The atmosphere is more stable when air is prevented from rising
    quickly. Fast upward movement of air creates instability in the
    atmosphere and the development of clouds, perhaps producing
    precipitation. Which of the 2 locations graphed would have [the most
    stable air]{.underline} on the day
    shown?\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

9.  How could you use the information in this activity to find unstable
    atmospheric
    layers?\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

10. Generally, thunderstorms are produced where lapse rates are high.
    Where would you expect more showers - in polar or tropical air
    masses?\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_

**Virginia Standards of Learning**

**Sci6.1:**  The student will plan and conduct investigations in which
c) precise and approximate measurements are recorded; h) data are
collected, recorded, analyzed, and reported using appropriate metric
measurements; and i) data are organized and communicated through
graphical representation (graphs, charts, and diagrams).

**Sci6.6:**  The student will investigate and understand the properties
of air and the structure and dynamics of the Earth\'s atmosphere.

**LS.1:**  The student will plan and conduct investigations in which c)
data are organized into tables showing repeated trials and means; h)
continuous line graphs are constructed, interpreted, and used to make
predictions; and i) interpretations from a set of data are evaluated and
defended.

**PS.1:**  The student will plan and conduct investigations in which b)
length, mass, volume, density, temperature, weight, and force are
accurately measured and reported using metric units (SI-International
System of Units); h) data tables showing the independent and dependent
variables, derived quantities, and the number of trials are constructed
and interpreted; i)data tables for descriptive statistics showing
specific measures of central tendency, the range of the data set, and
the number of repeated trials are constructed and interpreted; j)
frequency distributions, scattergrams, line plots, and histograms are
constructed and interpreted; and k) valid conclusions are made after
analyzing data.

**PS.7:**  The student will investigate and understand temperature
scales, heat, and heat transfer. Key concepts include Celsius and Kelvin
temperature scales and absolute zero.