For thousands of years, people have tried to find hidden patterns by looking
at the same old information. Reading tea leaves and casting runes were ways
of looking at things so that we could try to understand them better. We pore
over a seemingly random distribution of symbols looking for a previously unknown
pattern, with a hidden meaning that might be of use to us in the future.
It's the same thing with graphing in spreadsheets. Every picture tells a story,
but be careful not to skew the picture. I've seen more lying using graphs and
statistics in the news media than I've ever seen watching the soaps. People
monkey with scales and make things look like they're flat when they're really
changing a lot and vice versa. Try to offer an illuminating view of the information,
but be careful about trying to persuade.
Types of Graphs
The basic graph types are column, line, and pie. There are more types, but they
are not as basic. I suspect that everyone has seen them all. I get a column
graph on my monthly electric bill. There are many examples available in the
links included below. Today, we're going to discuss a genealogical example that
is based on a chart type more commonly used for analyzing stock market performance—its
catchy name is Volume-High-Low-Close. This kind of graph has two scales—a "Primary"
and "Secondary" scale, so that you can graph different numbers that aren't on
the same scale.
Stock volumes run in the millions, while share prices are usually between $50
and $150. Graphing them together is easier if you use two scales. Well, we're
going to look at an example today where you might graph life spans against time
periods.
Location, Location, Location
One thing you'll need to decide is whether to put your graph on the same worksheet
with your data, or in a different worksheet in the same workbook. There's no
rule about this, just try it both ways until you get what you like. Sometimes
it's nice to look at the numbers and the graph next to each other, and sometimes
it's just not necessary to see the numbers, too. The graph tells the whole story-all
thousand words.
The point here is that you should visualize how you want the graph to look.
Right out of the can, Excel's graphs look pretty boring. You can do a number
of things to jazz them up. In particular, you can change the colors that are
used, you can use gradient fills (those look very professional), image or logo
backgrounds, and so on. You can also use images for the graphs, like money bags
or stick men. As you might imagine, it can get out of hand pretty easily. Make
it readable, then make it cute.
How You Make It—The Excel-Ecution Of A Graph
Select the data that you wish to graph. I generally just drag the mouse
to do this, but sometimes I select the column letters.
Click on the Chart Wizard icon. I used to think it was a picture of a factory
with smokestacks, but it's really a picture of a graph with a blue, yellow,
and red column on it.
Choose the graph type. If you don't know what you want, go next door and
ask them what they like to use.
Enter your titles, if you wish.
Specify your location—on this page or on its own page?
Start over if you don't like it.
Using The Life Spans Graph To Look For Patterns
"A common man marvels at uncommon things; a wise man marvels at the commonplace."
From what I know, Confucius never used Excel. Still, he understood the importance
of looking at the obvious. Next time you get a chance to use Excel, try the
following.
Label your columns: Name, Life Span, Birth, Death, Marriage. I used initials
instead of name in the example on my Web site to protect my guilty ancestors.
Beneath those headings, enter the birth, marriage, and death years of a dozen
or so of your ancestors. Try to use data that covers a long time period, preferably
going back to 1800 or before.
Use a formula to calculate the life spans. In the example on my site, I just
plugged in the current year as the death year for living people—not because
I think that we are all really close to going to our Judgment, but because I
felt like the formula and graph seemed to work better if the life span extends
to the present.
Then, select your data, click on the Chart Wizard icon, choose STOCK and VOLUME-HIGH-LOW-CLOSE
for the chart type. The rest of the Chart Wizard is going to produce a graph
something like the one on my Web site. Here's where you get to read the runes
and marvel at the commonplace. Using your sorting talents, sort on birth year.
Look at the graph. Sort on life span. Look at the changes in the graph.
Link Me Up (More Stars Is Better)
The Excel Logic Page
****
Xlogic, Aaron Blood, unknown, 27 May 2001.
A number of charting examples—more than you can shake a stick at.
This is an advanced page.
Beau Sharbrough's
Genealogy Articles ****
Beau Sharbrough, Beau Sharbrough, 28 May 2001, 3 June 2001
Specific examples demonstrating the concepts in this series of articles.
What Else?
Look for a correlation between life span and year of birth. Some families do,
and some don't. The ones that do, the ones that are living longer now than they
were two hundred years ago—ask yourself why? Ask what enables you and your
parents to live so much longer than your distant ancestors.
If you want to graphically compare your father's family to your mother's, you
might consider a sort I call Xeno's Comb Sort. There's an example of it in the
worksheet on my site, but basically you keep dividing by half and half and half
as in Xeno's Paradox about the Tortoise and the Hare. If you sort on these numbers,
your graph will resemble an ordinary comb. You may find that your mother's family
has always lived to be seventy, and that your father's has had an increasing
life span over six or seven generations. Just play with it, and marvel at the
commonplace.
Beau Sharbrough is the president of GENTECH, the founder of the GENTECH
and FGS Web sites, and a founder of the Lexicon Working Group. He would like
to hear from you at beau@sharbrough.net,
but due to the volume of e-mail received, he is unable to answer every e-mail
message received. Please note that he cannot assist you with your individual
computer problems. Visit Beau's Web site for information about speaking engagements.
Beau is the father of two college-age girls and is a proud graduate of Texas
A&M University.
Copyright 2001, MyFamily.com.
Ancestry Daily News
