Week #6: (2/29-3/4) Technical Applications of Excel

[rjagodowski]

Today we will investigate Microsoft Excel, with emphasis on it's formula, tables, graphing abilities & sorting especially in relation to technical applications. We'll also present the csv (comma separated value) file format.

Here is the "Quick Reference Card" for the "Get to Know Excel 2010" exercises.

Quick Reference Card - Get to know Excel 2010 - Create your first spreadsheetpdf.pdf

(106.36 KiB) Downloaded 145 times

Some PowerPoint presentation from MS on Excel can be found here: http://office.microsoft.com/en-us/suppo ... 01726.aspx. You should watch the "Get to Know Excel 2010 - Create your first spreadsheet" PowerPoint presentation. This presentation has several videos embedded in it.

Key topics for today will include:

Common Short-Cut key sequences.
Numbers - Formatting, with emphasis on Scientific and Engineering Notation (see link below).
Graphs - Multiple lines on a single graph, Graph & Axis TItles, Fonts, Major/minor Axis formatting, Scaling, Legends, Colors.
Copying Excel Tables & Graphs into MSWord documents.
Sorting
CSV files
Open Source alternatives.

Some shortcut keys: Ctrl+: c = copy, x = delete, v = paste, z = undo last action, y = redo last action, ~ (in Excel) show formulas in all cells


Shortcut Keys from Microsoft: http://support.microsoft.com/kb/126449.

Engineering Notation in Excel:
Helpful links:
Here is a helpful link for formatting numbers in scientific and engineering notation: http://people.stfx.ca/bliengme/ExcelTip ... tation.htm. The Excel file for this link is available here:

EngineeringNotation.xls

(29.5 KiB) Downloaded 151 times

Here's a link to Charts & Graphs in Excel.


You'll probably notice that most examples, demos, tutorials and such generally are related to business operations. However, Excel can definitely be useful for technical applications.

The attached file demonstrates the Maximum Power Transfer Theorem. We'll open this file in class to demonstrate how Excel can graphically show the change in expected results with a single data point change. This is sometimes called a "what if" scenario. "What if" I change this value to 3300? Once the spreadsheet has been created, "what ifs" are just a few key strokes away.

Maximum Power Lab - Power Calculations.xlsx

(16.45 KiB) Downloaded 158 times

Make note in the graph how the horizontal axis is a logarithmic scale, allowing a wide range of x- values (Resistance) to be presented in a concise format.


Sorting data in Excel:

A quick overview: http://spreadsheets.about.com/od/excel2 ... 07sort.htm

All the details: http://office.microsoft.com/en-us/excel ... 73947.aspx

The important thing to remember when sorting multiple rows & columns of data is to select ALL of the data to be sorted in order to preserve the relational aspects. For instance, first names with last names, or names with addresses or names & addresses with phone numbers.

Here's a practice file:

Name Sort File.xlsx

(11.19 KiB) Downloaded 142 times

Date and Time information can also be processed in Excel. Here are some links to show the time related functions.

The TODAY() and NOW() functions: http://spreadsheets.about.com/od/excelt ... nction.htm

Comma Separate Value files:

A common data file format is csv or comma separated value format. Excel (and most programs) will import this type of format, and many will export it as well. A csv file is a basic text file which contains no program specific formatting. If you save an Excel file in csv format, the contents of each cell in each column will be separate by a comma. When the end of row 1 is reached, a new line is formed in the file beginning with the contents in row 2.

If you open a csv file with a word processor, you might see something that looks like this:

1,2,3
,4,5
6,7,8
a,,c

If you don't see a value before a comma, that is because that cell would be empty. Here is the csv file. Open it in a simple text editor and then open it in Excel, to see how it looks. Notice how small (in bytes) the file is.

csv Test.csv

(26 Bytes) Downloaded 145 times

Data Acquisition equipment often create & store their data in csv files, so it's important for a technician to recognized this file format and understand how to open and manipulate the date.

Here is a sample csv data file:

csv Example.csv

(23.22 KiB) Downloaded 147 times

Download this file and open it in Excel. We will perform some Excel data filtering and statistical analysis on this file and generate a graphical output. The result will look like this file:

csv Example.xlsx

(151.27 KiB) Downloaded 157 times

Open Source Office Alternatives:

Libre Office: http://www.libreoffice.org/

Open Office: http://www.openoffice.org/

Google Docs: http://www.docs.google.com/

[nacolon]

I read all about sorting in excel
Completed 6