Computer Users’ Group

February 16, 2005

Spreadsheets.

It was the spreadsheet program Visi-calc that turned the computer from a hobbyists’ toy into a business machine. Visi-calc then became Lotus 1-2-3 and the Lotus company started in business. There are others including Quatro Pro from Corel and Excel from Microsoft. Microsoft Works also has a spreadsheet. And there are some free ones that you can find by just typing spreadsheet into Google. You’ll also find tutorials that way. We’ll use Excel for our examples since it comes with Microsoft Office.


Spreadsheets are really very simple to learn and understand if one is only interested in basic functions. There are, however, advanced features that require mathematical understanding and detailed study to master. We won’t go there.


Consider a small store selling sporting goods and some team souvenirs.

Item

Cost

Price

Qty

Profit

 

Hats

$3.00

$9.00

13

 

 

T-Shirts

$5.00

$13.00

25

 

 

Sweat shirts

$7.00

$17.00

7

 

 

Banners

$4.00

$11.00

3

 

 

Footballs

$9.00

$23.00

2

 

 

Mugs

$3.00

$7.50

9

 

 

 

 

 

 

 

 

Open Excel and note that the columns are named by letters and the rows are named by numbers. Each cell has a column-row address as in G6

We started by creating column headers that we knew that we would need and then in each column we entered the appropriate item. You can enter it a row at a time or a column at a time or skip around.

In order to find the profit, we have to take the cost and subtract it from the price to find the profit on each item and then multiply that by the number if items sold to get the total profit. On the spreasheet, the price for hats is in column C, row 2 or C2. The cost for hats is in B2 and the quantity sold is D2. In the profit column, column E, row 2 we enter a formula =(C2-B2)*D2. (Note: It is not necessary to use upper case letters). We use the “=” to show that we are entering a formula. We could do that for each row increasing the row number each time but writers of spreadsheet programs tell us to just copy the top cell into the lower cells and it will adjust all references. So we highlighted cell E2 and pressed CTRL-C and then highlighted cells E3 through E7 and pressed CTRL-V. Now we have the profit for each item. To get the full total profit we added rows E2 through E7. We highlighted them and also included cell E8.and then clicked on the ∑ at the top of the page. That’s the mathematical symbol for summation. Here’s what we had.

Item

Cost

Price

Qty

Profit

 

Hats

$3.00

$9.00

13

$78.00

 

T-Shirts

$5.00

$13.00

25

$200.00

 

Sweat shirts

$7.00

$17.00

7

$70.00

 

Banners

$4.00

$11.00

3

$21.00

 

Footballs

$9.00

$23.00

2

$28.00

 

Mugs

$3.00

$7.50

9

$40.50

 

 

 

 

 

$437.50

 

We then took a data base that we had downloaded form the GNIS (See http://www.rootsweb.com/~flpslc/21005.htm ) and imported it into Excel. The file is the Florida file FL_DECI_TXT which uses the pipe symbol | to separate columns (delimiter). The pipe symbol is on the same key as the backslash \.

We first went to OPEN and navigated to FL_DECI_TXT and in the Files of Type box we selected Text and clicked on open. The text import wizard told us it was a delimited text file and to press Enter. We were offered a list of delimiters and an option of Other. We chose Other and entered the pipe symbol. We then clicked on Next and finally, Open. What we got was a huge data base file of every named geographical place in Florida. Since we wanted to limit it to cemeteries, we decided to sort it by sorting on the column that lists feature type, column D. We then clicked on Data and then on Sort and in the select box we clicked on the arrow until we got to column D. We also clicked on My data has no header row since none of the columns has a title. When we clicked on OK. The entire table was sorted with column D in alphabetical order. We scrolled down to Cemeteries and selected every line containing a cemetery entry and pressed CTRL-C to copy it. We opened a new sheet and pasted the cemeteries in and closed the old file without saving it.

Column E has the county name and column C has the cemetery name. We went through Sort again this time selecting to sort on column E and then on column C to sort by county and then by name. We could have done that with the initial sort and sorted all three columns at once but I chose to delete the unwanted information just to free up memory.


That’s the procedure for bringing a delimited file into Excel. In Quatro Pro you can’t use the pipe symbol as a delimiter so you have to go through a Find and Replace replacing all pipe symbols with commas. Commas are the most common delimiters.


Identifying Subjects on Photographs.

Old photographs depicting people need some method of identifying them. Writing on the back is one way, making a left to right, front to back listing is another. Also, one can make a photocopy of the photo and write the subjects’ names on it and keep the two copies together. But there is a way to incorporate the names of the individuals and, indeed, as much relevant information as you want to present about each one in a single document that can be saved in digital form and emailed and/or published on the web. And you probably have all of the tools necessary to do it.


We started with a photograph of my mother, uncle and grandmother taken about 1916. It was sized to be 150 pixels per inch and was 606 pixels wide. It had been reduced in size from a 600 pixel per inch copy that I had scanned in. Photos that are too large tend to overflow the display so somewhere between 75 and 150 pixels per inch and a size of about 600 pixels wide. If you’re emailing it to others, keep to the low side because they may have a small monitor at 640 by 480 pixels. For your own use, do what fits comfortably on you display. You’ll need graphics program to resize images but if you’ve scanned the images in, you probably have one that will do it. If not, the free Irfanview program will do it and a lot more. http://www.irfanview.com/


Having the image properly sized, we used a graphics program to find the coordinates of the places we could point to display a name. We chose the center of the face of each individual. Using Paint that comes with Windows, we pointed to the center of my mother’s face and noted the coordinates. We moved the cursor to the right to come to the edge of her face to establish the radius of a circle that we could use as a ”hot spot”. It can be larger than the face but you must be careful not to overlap circles when people are close together. We selected a radius of 20 pixels. We then found the coordinates of my uncle and my grandmother. We used the radius of 20 for all. We opened a file called photo template.htm in notepad. It looked like this:

(Note: To use this file and the Name template.htm file below as templates, swipe across them to highlight all of the text and press CTRL-C. Then open Notepad and press CTRL-V to paste it in. Click on File and then Save as  and in the Save as type dialog press the arrow to select All Files  and enter Photo Template.htm or Name template.htm or names of your choice as long as the extension is .htm.)

Note: This has been updated to use the "title" attribute instead of the "alt" attribute so as to work with all browsers. You may use the alt attribute if your only users use Internet Explorer.

 

Updated Sept 2014 to add a to href as "a href" with and ending /a.

 

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

  <meta content="text/html; charset=ISO-8859-1"

 http-equiv="content-type">

  <title>name</title>

</head>

<body>

<map name="name" id="name">

<area title="name1" shape="circle" coords="x,y,r"

 a href="name1.htm" /a>

<area title="name2" shape="circle" coords="x,y,z"

a href="name2.htm" /a>

<area title="name3" shape="circle" coords="x,y,z" a  href="name3.htm /a">

<p align="center"><img style="border: 0px solid ;" alt=""

 src="name.jpg" usemap="#name"><br>

</p>

</map>

</body>

</html> 


We first clicked on file and then save as and entered the name Leonard in the file name box and pressed enter to save the file without overwriting the template file. The file should automatically be saved with the extension .htm. If you can see the extension, change it from .txt to .htm. If you can not see the extension, check the logo next to the saved file name. It should represent an Internet page and not a text page. If it does not have the proper extension, go to Tools/ File Types/View and scroll down and uncheck the box Hide known file type extensions. Then change the extension from txt to htm.


If you have a fewer or greater number of people in the image delete the 2 lines that include a name or highlight and copy multiple lines until you have the proper number. Renumbering them is not necessary; just keep track of where you are. Every where the word name appeared we replaced it with “Leonard”, name1 with “Harriette”, name 2 with “Charles” and name3 with “Ann (Nichols) Leonard”. We replaced the “x,y,r” after each name with the coordinates that we found for that range. The name in the name.jpg position should be the actual name of the image file. We clicked on save and pressed enter.

We have now created a file that has the identification of all of the subjects. Navigate to the file from My Computer and click on the file to open it in your browser. Verify that all of the names pop up as required; edit them in notepad if any don't work right. When you have the file open in your browser (only Internet Explorer works), click on Save as and in the Save as File Type  box scroll down to Web Arechive, single file (*mht). You can now attach that file to an email, post it on the web or save it in your photo archive.

If we also want to add genealogical or other information that we can bring up with by clicking on a person by can proceed as described in the following section. However, despite the promise of creating a single archive file as noted above, the target links do not get saved as part of the file and must be present on the computer on which the *.mht file is saved. So it's a great way to archive information but other ways must be found if we want to transfer the file to others. We'll present a couple of them.
 

We then opened name template in Notepad and immediately saved it as Harriette. Name template looks like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="content-type">
  <title>Harriette</title>
</head>
<body>
<table
 style="text-align:center; margin-left: auto; margin-right: auto; width: 40%; height: 136px;"
 border="1" cellpadding="2" cellspacing="2">
  <tbody>
    <tr>
      <td
 style="text-align: center; width: 218px; background-color: rgb(0, 204, 204);">
      <style ="font-weight: bold;"></style><big
 style="font-weight: bold;"><big>name1</big></big><br>
data<br>
data<br>
data<br>
data<br>
      </td>
    </tr>
  </tbody>
</table>
<br>
<br>
</body>
</html>

span>

We then changed Name1 to Harriette and replaced each data word with birth, marriage and death records. We can add as many lines as we want as long as each line ends in <br>. Then we saved the file.

We repeated that for Charles and for Ann, saved the file and then closed notepad.


We navigated to the folder holding these files and clicked on Leonard.htm and we demonstrated that pointing at anyone brought up the name and clicking brought up the genealogy. (Note: in the demo we couldn’t get this to work and found later that the # in usemap #name was missing. Be careful cutting and pasting.) We were then ready to combine this data into a single file. We went to File/Save as and in the dialog box we selected Web Archive Single File *.mht.


If we only want to save this file for our own use, we are done. If, however, we want to let others view this file we have to consider the available options. What we've created in a web type file that opens in a web browser. Since most Internet Service Providers offer each user a certain amount of web space to use, one solution would be to create a web site on which to post the photos and send a link to that site in emails to everyone you want to view them. We covered that here: http://www.rootsweb.com/~flpslc/cugjan172002.htm. Another option is to include the htm file we just created and the separate genealogies in a ZIP file that  could be emailed as an attachment to an email.
You can go here to see what a web site can look like when you've done all the above. Go here to see the result of our work or go here to see another page with more detail.

For photo preservation, scan your photos at the highest possible resolution so that the best possible copy will survive and then make a companion file of lower resolution to identify the subjects. Use some of the techniques here to save or share them.