Free Office Apps

It has been over a year since I actively blogged.  I have begun writing the 6th edition of the Informatics and Nursing textbook, so will blog news as I learn.  The most amazing change in the horizon is the advent of free office apps other than Google Drive.

Microsoft now provides access to free versions of Word, Excel, PowerPoint, and OneNote with Office.com.  Microsoft also has a free version of Outlook and provides email, calendar, and people (contacts).  Create a user name and password to access the resources.

The free Microsoft apps allow you to download the files you create or save them to your Dropbox account.

Apple also provides free apps for users and 1 GB of file.  You create a free login to iCloud first at http://www.icloud.com.  When you open you iCloud site, you will see Contacts, Notes, Pages, Numbers, Keynote, and Settings.

Enjoy exploring these new features! 

Excel Formatting an Axis

Have you ever made an Excel chart where you needed to change the value of the axis?  For example you wanted to show a chart that depicts 100% of the people you surveyed, but when you created the chart the axis value was greater than 100%.  A greater than 100% would not make any sense.

The procedure is easy but a little tricky to find. 

Right-click on the axis you need to edit to get the Format Axis menu shown below. Change the axis value to 100% (or the value you want to display.   Notice that it is possible to edit the minimum value, too (although it is often best for that value to begin with 0.  When you are finished, save and close the window. 

FileMaker Pro Import Problems

Am working on a tutorial for my eBook and using spreadsheets and databases for data analysis.  I created a file with 85 pretend records in Excel to import into FileMake Pro.  I copied the field names in FileMaker Pro, but I could not get the field names to match up exactly!  I did not see a choice to match up the field names - only to import or not.  Am sure that it is because I have used the application for less than a year.  My work around was to change the tutorial to import a file into a new table.

Will have to go back at another time to see if I can figure out what was happening.  

Microsoft Office 2013 Insights

I began using Microsoft Office 2013 yesterday.  My experience validates what I have been reading - meaning the most significant change is the link for using cloud computing.  When you open an application, such as Word or Excel, you are prompted to sign into SkyDrive. Once you have signed into SkyDrive, all of the cloud files are available, just like those on the hard drive.

The startup menu looks more like Word or Excel for the Mac - which by the way looks more like other Apple Pages and Numbers.  In other words, you have colorful full size templates and links to recent files from which to choose.

Other notable differences include new icons for the Office apps.  Outlook was significantly easier to set up, even though I use an Microsoft Exchange server.  Outlook finally got some much needed attention.  It has a cleaner look.  

Dartmouth Medical Atlas

The Dartmouth interactive medical atlas at http://www.dartmouthatlas.org  provides an outstanding  way to depict data.  Working with a graduate student this morning on her end-of-life and ICU research paper prompted me to explore the website again. The embedded picture below shows percent of deaths associated with ICU admission by area in the country.  You can mouse over any of the sites to see the average percent of deaths.

The data can be downloaded into PowerPoint or an Excel spreadsheet, too. 

eBook Progress

I have been writing an eBook, Introduction to Data Analysis for Healthcare Professionals: Spreadsheet and Database Applications, for several weeks. As noted previously, the plan is to have the book in the Apple iBooks store at no charge.  I plan to use iBooks Author, so for the moment, am writing the book using MS Word.  To prepare the text for iBooks Author, I will save the text as a text file (.txt).  Once the final draft is completed, I will copy the text into iBooks Author.  The next steps will be to add the graphics and video files. I will design the videos as the last.The videos will be created using ScreenFlow.

I am considering publishing a version in the Amazon bookstore as an ePub file.  I will copy the text from MS Word into Apple iWork Pages and save the file in the ePub format. Of course the ePub book will not have the interactive features that are possible with iBooks Author. 

I have chapters about spreadsheets and databases in a good working draft form.  I have started a chapter on using FileMaker Pro tutorials.  I will be covering the following:

• Spreadsheet software
• Microsoft (MS) Excel
• Apple iWork Numbers
• Google Drive Spreadsheets
• Free spreadsheet software (desktop and cloud apps)
•  Database software
• FileMaker Pro (available for  MS Windows and Mac PCs - about, includes history
• FileMaker Go
• MS Access - about, includes history
• Free database software (desktop and cloud apps)
• Spreadsheet tutorials for using
• MS Excel
• Apple iWork Numbers
• Google Drive Spreadsheets
• Database tutorials for using
• FileMaker Pro for the Mac (MS Windows users should be able to find equivalent menus)
• FileMaker Go
• MS Access

I welcome any suggestions for the eBook. 

Using Excel to Create a Concept Map

Debby and I were working on the graduate handbook yesterday - while she also had Elluminate Live open chatting with the grad students taking pathophysiology.  She has the students create concept maps for topics the students are studying in patho.  She was commenting on the impressive creativity that the students are demonstrating.  One of the apps the students use was Excel.  At first I was surprised, but then reflected on the fact the Excel has the same drawing tools found in Word and PowerPoint.  I did a little research and found a website that has a tutorial on how to use Excel to create a concept map.  The site is http://www.internet4classrooms.com/excel_concept_map.htm

Microsoft Office 2010

I upgraded to Microsoft Office 2010 on my Windows computers the past couple of weeks.  Users are prompted to open a website with resources to learn the new features.  As an example, there is a video showing the new slicer and sparklines featured in Excel and the ability to embed the code in a website as noted below. There will be additional information about Office 2010 in the 4th edition of our Nursing and Informatics textbook.

Chapter 9 Learning Exercises Corrections

The textbook print errors in the learning exercises for chapter 9 (page 158) stumped more students recently.  A faculty member at North Georgia College & State University reported the problem to the textbook rep, Tanya.  Of course, the problem is universal.  I didn't catch the errors in the final manuscript review :(

I wrote an email to the faculty member and copied it to the textbook rep.  I created the corrections for questions 1, 3, and 5 in Excel.  The link to download the spreadsheet is at http://hercules.gcsu.edu/jsewell/textbook/Chapt9Corrections.xls

Click the tabs on the bottom of the spreadsheet to view each of the three exercises. To view the formulas in the Excel spreadsheet, tap the the Ctrl+~ keys. 

The 4th edition of the textbook will include downloadable files used in the textbook for all of the office applications. I will make sure to check the formulas in the final manuscript review!

Excel Skills

I am working on creating identifying Excel skills at the basic, intermediate, and advanced levels.  Please comment/edit the following:
Basic Spreadsheet Skills
·         Design a simple table
·         Name a worksheet tab
·         Apply a template
·         Insert a new worksheet
·         Create a simple formula (Add, subtract, multiply, divide)
·         Use basic functions (SUM, AVG, MIN, MAX, COUNT)
·         Resize columns and rows to display data
·         Sort cell data
·         Use search and replace
·         Freeze rows and columns
·         Use automatic data entry
·         Use spell-check
·         Use a chart
·         Save a workbook
·         Print a worksheet

Intermediate Spreadsheet Skills
·         Customize the spreadsheet menu
·         Design spreadsheets using data validation features
·         Customize a spreadsheet using conditional formatting features
·         Create/modify a chart
·         Import/export data in text format
·         Link spreadsheet data from other sources
·         Apply principles of effective spreadsheet design
·         Create complex formulas
·         Create/modify a pivot table
·         Create a form
·         Create a report
·         Link and embed tables into word processing documents
·         Use data protection
·         Password protect a workbook
·         Demonstrate competency using two spreadsheet applications
·         Share/collaborate with others on a spreadsheet design

Advanced Spreadsheet Skills
·         Create formulas that use logical and statistical operations
·         Use advanced data analysis tools
·         Create a dashboard
·         Create a new template
·         Create macros
·         Create new functions using Visual Basic expressions
·         Demonstrate competency using more than two spreadsheet applications

Excel Tools & Templates for Quality Initiatives

The ASQ (American Society for Quality) provides many free Excel tools and templates for quality initiatives in nursing and healthcare. Examples include:

• FMEA (Failure mode & effects analysis)
  
• Fishbone diagram (Cause & effect)
  
• Flowchart template
  
• Gantt chart
  
• Pareto chart
  
• Histogram
  
• Scatter diagram
  

Each tool/template includes instructions, an example, information about the particular tool, and links to additional learning resources about quality. The quality tools and templates can be used for learning activities to supplement chapter 9 on spreadsheets, chapter 24 on administration, and chapter 25 on research. The tools reference The Quality Toolbox, Second Edition, ASQ Quality Press, 2004 by Nancy R. Tague.

Thanks to TJ for sharing!

Creating an Excel Chart with a Double Axis

My students are always challenging me and exceeding my expectations in the process. After asking the students to create a combination bar and line chart using Excel, TJ created an awesome chart with a double axis. She gave me permission to share on this blog. Note that the left axis shows the number of falls or pressure ulcers and the right axis shows a comparison with the percent of RNs.
Do learn how to replicate this chart, use the Help button in Excel and search for Secondary Vertical Axis.

Using Nested Formulas to Calculate a GPA with Excel

I received an email request from a former colleague this week and am posting part of the email with her permission.


"I love your textbook!  I think we'll be using it for our BSN informatics course!!

On another note - I'm pulling my hair out on making a GPA spreadsheet like we used at ___!  I'm going gray over this.  Could you help me out please?  I'll be eternally grateful. :-)"

What she is asking is how to create formulas in Excel to calculate a GPA. Although the formulas may initially look intimidating, they are fairly straightforward. The formulas use If/Then statements. The formulas are based upon the A, B, C, D, F grading scale, where A is worth 4, B is worth 3, C is worth 2, D is worth 1, and F is worth 0 quality points.

My answer is noted below. I will also attach a spreadsheet with the formulas. Note that the formulas below are nested meaning that each one includes several arguments, that is, one for each grade.

	A	B	C	D	E	F
1	Course	Name	Credits	Grade	Quality PTs	QP/Credits
2	ENG	College Composition I	3

=IF(D2="A",C2,IF(D2="B",C2,IF(D2="C",C2,IF(D2="D",C2,IF(D2="F",C2," ")))))

The formula above in Cell E2 is for quality points.  What it says is that if D2 (Grade) is "A", E2 (Quality Points) is the same value as C2 (Credits) - same for "B", "C", "D", and "F".
 =IF(D2="A",4*E2,IF(D2="B",3*E2,IF(D2="C",2*E2,IF(D2="D",1*E2,IF(D2="F","0","")))))

The formula above in Cell F2 is if D2 (Grade) is "A", multiply 4 times E2 (Quality Points),  - same for "B", "C", "D", and "F" except "B" = 3, "C" = 2, "D" = 1, and "F" =0.

The letter grades are in parenthesis to tell Excel that the letter is a text field.

To download a copy of the spreadsheet, go to http://hercules.gcsu.edu/jsewell/textbook/GPA_spreadsheet.xls

Creating a Combination Column & Line Chart

In healthcare we often want to compare data from different sources to see if there is any type of relationship; for example, comparing staffing with patient outcomes.  An activity for the Thede & Sewell Informatics and Nursing textbook, chapter 24, asks the learner to create a chart that compares the number of patient falls and pressure ulcers with staffing using the percentage of registered nurses. The mixed type of chart is not addressed in the textbook and may pose as a challenging task.  The task is easier than it might appear. It is a two step process.

If using Excel 2007

1. Highlight the entire chart and then select the Column Chart type.
2. Right-click on the Percentage of Registered Nurses column and select Change Series Chart Type from the menu - and select Line with Markers

The resulting chart should like like the graphic below.

A website at http://www.techonthenet.com/excel/charts/column_line.php may be helpful for earlier versions of Excel.