Excel - online resources for the IF function

There don't seem to be a lot of online tutorials for Excel's IF function, and those I've looked at explain it pretty much the same way our workbook explains it. However, you might look at the links below:

• tinyurl.com/i0th - video demo on our website
  
• Microsoft - with 4 examples, including nested IF functions - at the IF function
  
• MakeTips - =IF Function for Value or Calculation Based on Criteria
  
  NESTED IF FUNCTIONS
  
  We don't emphasize nesting IF functions. We just look at it briefly to get a taste of what it is. Finally, we look at its limitations, experiencing how messy it gets when you nest too many IF functions; this shows us why we need the VLOOKUP function as an alternative.
  
  
  
• About.com
  
• Techon theNet
  
• The Spreadsheet Page - nested IF functions and VLOOKUP
  
• Online Investing Hacks, "Make Nested Functions Work in Excel" - interesting if you have some background in computer programming
  
• Pearson Software Consulting - interesting if you have some background in computer programming
  

Test Wed., Dec. 3

On Wednesday, Dec. 3 the Excel test will include the following topics:
* - templates
* - linking
* - protection

On Monday we will begin the IF function.

Excel - protection(locking and unlocking cells)

In addition to our own video tutorial on this topic, there is also an excellent video tutorial on YouTube over 4 minutes long with audio at
http://www.youtube.com/watch?v=2BOzMX5Mujw

Another, also quite good (it's always helpful to hear more than one way of explaining something) is at
http://www.youtube.com/watch?v=Fxm1MiTlYzM.

Excel 2003 -- links

Some of the Excel features you'll be learning are shown in our video tutorials at http://tinyurl.com/i0th.

* Linking cells between worksheets
* Cell Protection
      -- protect cells to prevent user input
* Functions
      -- IF
      -- VLOOKUP
* Conditional Formatting
      -- format cells which meet specified conditions
* Data validation
      -- validate data as it is entered
* Pivot Tables
      -- analyze an Excel database ("list") with a pivot table
* Scenarios
      -- create scenarios
      -- create a scenario summary

The videos show Excel version 2000, not 2003, but there are few significant differences.

How much are the fines in Canada for using illegally copied software?

The Canadian Alliance Against Software Theft (CAAST) is an industry alliance of software manufacturers who share the common goal of reducing software piracy. Their website describes a number of cases of companies that have paid large sums of money for installing illegal copies of software.

The CAAST website redirects you to the Buesiness Software Alliance (BSA) website. Click on Pressroom, then Archived News.

These are not, strictly speaking, fines. A group such as CAAST or the BSA make the offending company known that a court case would result in large fine, and offer to take a "settlement" instead of going to court. Offending companies nearly always comply.

my recommendations for great free software

This summary is not available. Please click here to view the post.

Software licenses

You don't buy a piece of software; you buy a license to use the software. This week we learn about software licensing- the various kinds there are, your rights and the rights of the person who owns the copyright, and especially the kinds of licenses that give you FREE software.

So long as there is lots of great free software available (you might be surprised at how much of it there is and how good it is), being a student or not having much money is a very lame excuse for getting involved in illegal (pirate) software. With good, free software, you can do just about everything you want to do and not have to worry about getting caught and having to pay huge fines.

In our course, as you know, we use Microsoft Office, for which the TDSB has paid all the required licensing fees. It's very expensive. Luckily, as individuals we can get free copies of OpenOffice (www.openoffice.org/), which works just as well as Microsoft Office. It
- includes the equivalent of Word, Excel, Access and Powerpoint
- also includes a math component and a drawing program
- includes templates and clipart ("gallery")
- can open/save in the MS Office file formats
- can save in PDF format
- it is supported by the giant software corporation, Sun, and has lot of documentation, tutorials and so on - one good starting point is OpenOffice.org's wiki
- there is a version for just about every platform: Microsoft Windows (98 - Vista), GNU/Linux ("Linux"), Sun Solaris, Mac OS X (under X11), and FreeBSD
Screenshots (http://www.openoffice.org/screenshots/) give you an idea of the program

One goal in this unit is to give you an idea of the huge variety of great software you can download legally and for free. Just to get a quick look at a selection of the kind of applications available, browse the list at http://www.opensourcewindows.org/, "a simple list of the best free and open source software for Windows."

plagiarized songs, etc.

One especially interesting area of copyright infringement is music. There are TONS of cases of people being sued for copying (plagiarizing) other people's songs. Here are some samples.

• Timbaland
  Avril Lavigne (she fights back)
  Madonna (she lost)
  Michael Jackson 1
  Michael Jackson 2
  Michael Jackson 3
  Robbie Williams
  "How Deep Is Your Love" (The BeeGees)
  Celine Dion
  "My Sweet Lord" (George Harrison)
  "Chronicles of Plagiarism in Indian Film Music"
  
  
• Other cases of allegedly plagiarized pop songs:
  http://www.google.ca/search?hl=en&q=plagiarism+song&btnG=Search&meta=
  Mahalo.com
  
  
• The article on plagiarism at Wikipedia has examples at
  http://en.wikipedia.org/wiki/Plagiarism
  
  
• Columbia Law Library Music Plagiarism Project provides descriptions of many cases of lawsuits involving alleged copyright infringement (not all the complainants won) --
  http://ccnmtl.columbia.edu/projects/law/library/song.html
  
  
• People have posted YouTube files showing similarities between songs (not all of them have gone to court)--
  http://www.youtube.com/watch?v=ryf0aG0X73g
  http://www.youtube.com/watch?v=UFdtntKPDYk
  Do you agree they're copies?
  
  
• How to avoid plagiarism in music (video)
  
  
• Writers and publishers in Canada tried to sue Staples for copyright infringment for allowing the public to use their photocopying services without controling what they were copying --
  http://www.thestar.com/article/276793
  

Netiquette for email

What is Netiquette?

Netiquette = Network Etiquette
= Using technology effectively to communicate with others
with knowledge, understanding and courtesy.

How should you use email so that you give the impression of being mature and professional?

See http://www.netmanners.com/email-etiquette/category/email-etiquette-101/

Macro in-class assignment #2 -- copying a macro created by someone else

We've learned how to create our own macros using Word's Macro Recorder. And when we learned how to print a macro by going to Tools / Macros / Macro / Edit, we were shown the programming code in BA (Visual Basic for Applications.

All this prepares use for the next stage of macros - using macros someone else has written. Some people are experts in VBA and have created some very useful macros. We can use them simply by copying and pasting their code into the VBA Editing window.

For some examples, look at word.tips.net, select a topic that interests you (Bullets and Numbering, Creating Documents, Customizing Word, etc.), and search for an interesting macro.

ASSIGNMENT -- Create a new document in Word. Create the macro by copying the code into the macro editor of your Word document. Test it to see that it works. Then clear the document, and copy the macro code into it. Add your name, teacher's name, Period, and a description of what the macros does. Print and hand in for marking.

Macro in-class assignment #1

In a new file named mymacro, create a macro named after yourself (e.g. BarackO).
Do not use a keyboard shortcut.

In the horizontal centre of the page, the macro, using the Courier New font at size 14 points, enters two lines of text:
- Your full name
- Period G
On a third line it uses the Insert menu to begin entering two more lines:
- today’s date in the format October 23, 2008
- the current time in the format 9:32:28 AM
It enters a blank line.
It finishes by switching back to Normal Style.

Run the macro to show its results on the page.
Below that on the same page, print the macro code from Sub to End sub (in Normal font).

Marks to date are online

You can see what your marks look like as of today by going to

http://tinyurl.com/5tf7lw.

There might be some small change before the final report card mark (one of your exercises may be collected). The final date for test, assignments, etc., is October 31.

You will receive your report card on Monday, Nov. 17.

Parent interviews are on Thursday, Nov. 27.

Macros

Macros are among the most useful tools you can use to increase your efficiency with Word (or Excel or Access or almost any major application).

What is a macro? -- A macro is a series of commands, key presses or mouse clicks that are recorded and performed automatically when a certain key is pressed or a certain command is entered.
For example, you will create a macro that with a single key combination indents a paragraph one inch from both margins and inserts a footnote. And you will create a macro that with another key combination automatically reformats an entire document to get rid of unwanted paragraph breaks.


NOTE: Be sure that your macro security setting (Tools / Macro / Security) is Medium. Your macros will not work if Macro Security is set to High.

 

Online Resources

• tinyurl.com/i0th -- our Jarvis page of demos has four macro tutorials
  
• What is a macro and why you should care -- Microsoft's own "Crabby Lady" introduces the idea of a macro
  
• homeandlearn.co.uk
  
• Brainbell: Create a simple macro in Word
  
• creating macros -- Valparaiso University
  
• Using Macros in Microsoft Word 2003 -- University of Reading (U.K.)
  
• Atomic Learning -- video tutorial
  
• Brown University Wiki -- advanced tutorial, although with no illustrations
  
• bettersolutions.com -- advanced tutorial
  

Forms - showing fields in order to check your work

While creating a Word form, you can check your work by toggling between seeing the data and seeing the fields.

First, make sure the form is unprotected.
Then press Alt-F9 to toggle between the two modes.

Oct. 7 - Forms: an enrichment activity

Students who finish the exercises on Forms before the rest of the class should visit the webpage below for an optional enrichment activity. The page explains how data is collected from forms and transferred into an Excel spreadsheet.

• See the article entitled Transfer data from Word forms to an Excel worksheet at TechRepublic.
  
• Open a document based on one of the templates containing forms that you have created.
  
• Fill in a form with data.
  
• Follow instructions on the above webpage to extract data from the form and import it into Excel.
  

Oct. 6 - Forms

This week we begin to look at Word forms.

We start by looking at simple fill-in fields to create a document reminiscent of a mail merge in that it consists of "boilerplate" text plus text inserted by a field - in this case a fill-in field rather than the kind of mail merge field you worked with earlier.

Then we look at other kinds of form fields that can be inserted using the form field toolbar.

Online Resources


• Two video tutorials at YouTube:   #1       #2
  
• Word Form Tutorial -- educationonlineforcomputers.com - Free Microsoft Office Training and Tutorials Blog
  
• Microsoft Word 2003 Forms -- Towson University (U.S.)
  
• Word 2003 Forms -- University of Reading (U.K.). Word format.
  

Styles

Styles are very useful - for example:
-to make changes throughout a long document
- to create a Table of Contents (TOC)

Online Resources

• tinyurl.com/i0th. Our online video tutorials for Word 2000 look somewhat different from Word 2003, but the basic ideas are the same.
  
• Use styles to format text
  
• An Extreme Tutorial on Styles
  
• VTC Online University -- video tutorial (click on Applying Styles / Modifying Styles / Creating New Styles
  
• Understanding Styles in Microsoft Word. Tutorial and links to all sorts of topics on Styles.
  
• Styles and Templates. A Word document.
  

Oct. 3 - Templates

Do take time to read carefully in order to become clear on the idea of a template.

After you learn how to create a template, it's crucial to understand the difference between editing or opening a template in order to make changes to it (File / Open / Files of type: Document Templates (*.dot))

and opening a document based on a template (double-click on the .dot file).

Online Resources

• tinyurl.com/i0th - Our tutorials for creating templates in Word 2000 are still good. There are no significant changes in Word 2003.
  
• Template Basics in Microsoft Word. Tutorial and links to all sorts of topics on Word templates.
  
• more advanced information on creating Word templates.
  
• VTC Online University -- Video tutorial (click on Creating Templates)
  
• bettersolutions.com -- advanced tutorial
  
• templates, forms and fields. Want even more info? This links to a 29-page tutorial, a Word document. It covers not only templates but also forms and fields, which we will study later.
  

Mon.,Sept. 22 - Finishing mail merge, beginning AutoCorrect

Mail Merge
At the start of this week we are finishing the exercises on mail merge. Please become familiar with the terms main document, data source document and merged document; it enables us to talk to each other much more efficiently.

AutoCorrect, AutoComplete, AutoText
In class we had had a demo in class of how AutoCorrect works (e.g. type 'teh' followed by a space and AutoCorrect immediately changes it to 'the') and learned how to create your own AutoCorrect entries, that is, your own shortcuts to automatically insert text. Now, for example, using AutoCorrect or AutoText you can type a few characters, such as jc1 followed by a space, and Word will replace it with Jarvis Collegiate Institute.

AutoCorrect, AutoComplete and AutoText are generally similar, with only small differences. This unit in your yellow booklet should take you less than one class to complete.

Internet Resources:

• tinyurl.com/i0th - our video tutorials for Word 2000 (no real change on this topic for Word 2003)
  
• simple tutorial on AutoText from Sam Houston State University (U.S.)
  
• simple tutorial on AutoCorrect from Sam Houston State University (U.S.)
  
• The Joys of Word 2003's AutoCorrect adapted from: Word 2003 For Dummies
  
• bettersolutions.com -- advanced tutorial on AutoCorrect
  
• bettersolutions.com -- advanced tutorial on AutoText
  
• a detailed tutorial in .pdf format on AutoCorrect, AutoFormat, AutoText and spelling and grammar checking from Durham University (U.K.).

Test on Operating Systems and Networks

There will be a test this week on the handouts on Operating Systems and Networks.

Word 2003 - Mail Merge

Our first topic in Word 2003 is Mail Merge.

One small problem is finding the Insert Merge Fields button. Move your mouse across the icons on the Mail Merge toolbar and wait for the little "tooltip" labels to pop up. The one you want is illustrated below:

Online Resources


• Tutorial by Microsoft in 4 steps. Assumes you have a list already in Excel.
  
• Word 2003 in Pictures - tutorial with many illustrations. Assumes you have a list already in Excel.
  
• Brainbell: Basic Mail Merge.
  
• bettersolutions.com - advanced tutorial
  

Welcome to the BAT3O1 course at Jarvis!

Welcome to the BAT3O1 course at Jarvis!












p*co*z_mi*mfour*ate