Smart Tags

Hello again, I know it has been awhile but here's something that I'll bet you have seen but may not really understand the significance thereof...Smart Tags

Starting with Office 2002 (also called XP) Microsoft added a new feature to the Office Applications called Smart Tags. Upon completing an action (such as Paste or Insert) and Icon related to the action (such as a Paste Options Smart Tag (Clipboard icon on a button) in the event of a Paste action) appears on the screen. You can ignore the button, keep on working and it will go away. If you move your cursor over the button you will see a down arrow. Click on the down arrow and you will see a list of options. The idea is when you perform an action, Microsoft has decided for you what happens by default but the Smart Tag is to offer you other options. You ought to at least look at the options to see what your choices are, you may be surprised as to the number of options. For example, in Excel, the Paste Smart Tag has 9 (yep, 9)different options as to what may happen when you Paste!

For those of you who have "been around awhile" you may have known about the Edit>Paste Special command. After a cut or copy, you could choose Edit>Paste Special from the menu and you would be presented with many options as what to do upon Paste. I suspect MS's intent was to provide a way to have options related to an action more "up front" or obvious by using the Smart Tag concept (no personal communication with Bill Gates and his team).

Check it out...I believe it's always good to know what your options are!!

Now just make a decision!

Have fun in the meantime until next time, Joel

Statistics Linear Regression Excel 2007

OK, so the weather has been so nice lately...

Let's see, how about for you statisticians or wannabe stat kinda guys or gals...you all remember r (linear correlation coefficient) or r2 (coefficient of determination) from statistics? Anyway, something about how well points fit a line. If r or r2 = 1, there is a perfect fit of the data to a line. In plain english, r is equal to the cross product of the sum of difference of each data point from it mean for x and y values divided by the number of data point minus 1 times the product of the standard deviation of the x and y values. Of course the line equation is y = mx + b (m is slope of the line and b is the y intercept).

In Excel after you have created a line chart (really could be several other types but makes most sense with a line chart (remember linear regression...). In Excel 2007 select the chart and the Chart Tools Layout Tab, the Analysis Command Group has Trendline, More Trendline Options (at the bottom of the list). By default, what we want in this case anyway, is Linear. This will add the best fit linear trendline to the chart. But now, maybe the best part...At the bottom of the trendline options dialog box are the options to force a Y intercept value (many times 0) and show the line equation and r2 values on the chart..pretty cool. I just think of all of the calculations to do this...

Otherwise, not into stats...never mind...

Have fun in the meantime until nextime, Joel

Databases: Excel vs Access

This discussion comes up frequently about databases...

What software should I use to manage my data? In my opion, right up there with knowing about word processing (Word) and spreadsheets (Excel) I would want to understand database concepts and database software (Access).

Many people tell me Excel "does everything...", even database...yes, to some degree...Actually we could set up a "database" using a Word table and it could be used as a data source file for mail merge for example (but probably wouldn't).

Flatfile vs. Relational Database:

What most people don't realize is that it is not very efficient to store all of the information that you may be dealing with in a single table (flatfile). As Dr. Codd (developed relational databases in computers) would suggest, one would want to setup a relational database where one would have many related (linked) tables. This design cuts down on duplication and inconsistencey of information (as well as many other advantages such as referential integrity, had to throw that in).

Access is relation database software and allows one to design and develop a relational databases where Excel is basically a flatfile database. Access has several parts in the database file including Tables and Forms (for entering, editing, and viewing data), Queries (questions about the data in the database), Reports (compiling, viewing, printing data), and Macros and Modules (automation with Macro commands or VBA code contained in Modules). So it has features that go "way" beyond what Excel can do in term of managing a database.

So, if you are "serioius" about having a database, you might consider Access. BTW, Access can be easily interfaced with Excel to take advantage of any of the unique Excel features that could be applied to your Access data even though Access has it own analytical features such as Charts, Pivot Tables, Crosstab Queries, and many others.

PC Applications specializes in Access training and database design and development and can help you at any level of setting up your database(s) using Access.

So, not right or wrong but a matter of being efficient...how do you manage your data? Excel, Access, Index Cards?

Have fun in the meantime until next time...Joel