Season's Greetings!

Merry Christmas and a Happy New Year.
Best wishes to all of you for the coming year.

I'll be back blogging after the New Year.

Patricia

When your X axis are numbers

You get to be my guineau pigs. I played around and created some Excel videos about a year ago and put a few on YouTube and then I just never got back to it.  However, I am finishing up a self-study CPE course on Excel Charting and thought that perhaps creating a selfstudy video course would be a nice course since charting is so visual. So, anyway, this is my first crack at it. 

In the video, I talk about the problems of charting when your X axis are numbers instead of text. As you can see from the screenshot below, the chart has a few problems and does not display the selected data in Column A and B very well. To see how to solve the problem, click on the video.

When you play the video, click the icon on the far right to get it to full screen mode.

Let me know what you think. If you can't access the video here, check it out on YouTube.

Extracting Text of Different Lengths

Use the Search function to separate text

Most people are familiar with LEFT, RIGHT and MID as ways to separate text and they are useful -
but only when all the cells have the same number of characters.

If you want to separate employees' names or emails you can't use LEFT, RIGHT and MID by themselves because all the names and emails are different lengths. This is where the SEARCH command comes in.

In the example below, I want to separate my first name from my last name so to do that I need to combine the LEFT function and the Search function.

The actual function is =LEFT(A2,Search(" ",A2)-1.

Excel searches A2 for a space as denoted by the empty quotation marks, in the formula, and finds it between my first and last name so at this point the formula would read as =Left(A2, 9-1)

So, what does that mean? Excel searched and it found the empty space. This empty space is the 9th character. We subtract 1 to tell Excel we only want 8 characters. The LEFT function then retrieves the 8 characters to the left in cell A2 and returns my name Patricia.

That was the easy one. Retrieving the last name is a little bit more work. In this case, I am using MID and SEARCH.In case you are not familiar with MID, the syntax is =MID(cell reference, start_number, number of characters)

The formula is =MID(A2,SEARCH(" ",A2)+1,35).

Excel reads that as =MID(A2,10,35)

To break the formula down, I again told it to search for the empty space between the first and last name and to use that as the starting point. Then, I told it to go over 1 character and return the next 35 characters. I arbitrarily picked 35 since few last names would have more than 35 characters.

Below are examples, if you wanted to pull apart an email address.

We finally have enough fans to have our own Facebook URL. If you have a chance, check it out and click on the LIKE button while you are there.  If you Facebook, all of these blog entries also appear there as do my tweets.  It is like one -stop shopping!

http://www.facebook.com/cpaselfstudy

I am watching tiny snow flakes coming down as I type this. Have a good week and stay warm.

Document Inspector

Our CPE Course, Spreadsheet Controls Under Sarbanes-Oxley 404, is being piloted and hopefully will be for sale by the end of the month on our website cpaselfstudy.com . While writing the course, we took a look at what Excel features would be helpful for setting up controls and I wanted to tell you about the Inspect Document feature.

This is a new feature in Excel 2007 and what it does it is "inspect" your worksheet and tell you if you have hidden worksheets, personal information, comments, tracking changes etc.

So, from an audit and/or control perspective, this might be something you want to look at when reviewing a spreadsheet. From an end user's perspective, you might want to check this before distributing a workbook to make sure that there is nothing in the workbook that you don't want others to see.
It is very easy to use.

• Click on the Microsoft Office button
• Select Prepare.
• Click Inspect.

Excel inspect the worksheet and returns with its findings. In this case, it found 3 hidden rows and 1 hidden worksheet.

You then have the option of removing what Excel has found.

Disappointingly,the Inspect Document feature did not find  my invisible cell, (I changed the cell font color to white, so that the cell was invisible being white font on white background.); however, I think you will still find it useful.

The Document Inspector is also in Word and PowerPoint.

Converting Text with a trailing CR to a Number

,Don't you just hate it when you import data and see the dreaded CR at the end of a number. You know that Excel automatically treats any cell content as text if it is a mix of numbers and text. This of course is a huge problem if you need to actually use that cell value in a calculation.  Below is a way to convert the content by using the LEN function and the LEFT function.

• The LEN sounds like a useless function but it actually very powerful - it counts the number of characters in a cell =LEN(text). 
• LEFT is a text function and it extracts the specified number of characters =LEFT(text, number of characters).

The LEN function counts the number of characters in the cell and the LEFT function tells Excel to extract everything except for the last 2 characters in that cell. I then multiplied it by -1 to make it negative.

So, in the example above, if you break it down, Excel looks at the LEN (A20)-2 and evaluates that as 5-2 which of course is 3 so now it would read =LEFT(A20,3)*-1. Excel would treat that as =100*-1 and the resulting answer would be -100.



A lazy way to get rid of error messages

People spend a lot of time using IF statements and/or  IFERROR so that error messages do not show up in a spreadsheet but if you are in a hurry and need to present your data- here is a lazy way to fix it so that they don't display in a printed report.
Simply go to Page Setup and click on the drop-down beside Cell Errors as: and select

Changing a Cell from Text to Number

If you have imported data from different general ledger programs you might have run into the frustration of  finding that your numbers were imported into Excel as text.  An easy way to turn your text back into a number so that you can perform a mathematical function with it is to right-click on it and select Convert to Number.

Another alternative is to use Text to Columns.

First, select the cell containing the value and then select Text to Columns under the Data tab and at Step 3 select Date and then Finish.

Surprisingly, this will convert text back into a number.

Match Function

I'm putting some stuff together for a presentation I am doing for the Institute of Internal Auditor's Indianapolis chapter in November and thought I would talk about the MATCH Function. The MATCH function can be very useful if you want to compare to lists.
In the example below, I have a current inventory list and an obsolete inventory list. I want to compare the 2 lists and make sure that there are no obsolete parts on the current inventory list.

In Column A I have the current part numbers and in column D I have a listing of obsolete parts.
In column G, I want to double-check to make sure that an obsolete part is not on the current list in Column A. So, I select G2 through G6 as this is going to be an array formula and type =MATCH(A2:A6,D2:D6,0) and then I press Control +Shift+ Enter, also called CSE. G2 through G6 are populated either with N/A messages or as you can see in G3 a number.   The number 4 in G3 is telling us that A3 has a match and that it is the 4th value in the other list.
 If you look down the obsolete part list, you can see that the 4th value of XYZ-124 is the same as in the second row of the new inventory list.  Now, this gets the work done but it doesn't really look that great with all the N/As and if you had a long list it would be hard to differentiate the error messages from the numbers.

We can make this more elegant by putting the MATCH function inside of IFERROR, a new Excel 2007 function.  In the example below, I selected G2.G5 again and typed =IFERROR(MATCH(A2:A6,D2:D6,0)," ") and then pressed CSE. All the error messages now display as a blank so it is much easier to see the match.

Free Excel CPE Course

Sorry- I just realized that I told a lot of different people about the free Excel CPE  we are offering at CPASelfstudy.com but I forgot to blog about it here!  I know, I couldn't believe I did that either!

Anway, we are offering a FREE... totally FREE... 1 hour Excel CPE course. Everyone always seems to be short that one odd hour.

The course is entitled, "Audit and Analytical Uses for Excel 2007 Filters and Tables. It is a brief overview course that focuses on Filtering data, Conditional Formatting and using Tables. It also briefly touches on Pivot Tables. So far, the reviews have been good. So, if you are a CPA needing CPE credit and want to learn something that you can immediately start using in your job, definitely check it out.
And of course, feel free to tell your friends and associates about it.
The link to the course is http://cpaselfstudy.com/item_185/Audit-Analytical-Uses-for-Excel-Filters-Tables-Free.htm

Evaluating Formulas

In the past, whenever I have talked about debugging or evaluating a formula, I have always talked about the Evaluate Formula icon found on the Formula ribbon

It is very useful as it allows you to break apart complex formulas step by step. Click Evaluate to examine the value of the reference that is underlined. The result of that evaluation is shown in italics.

If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.  And then just keep going until you have worked your way through the entire formula.

If it is a long complex formula, I still think this is the best method but I learned a quicker method the other day on Chandoo's site and I think it is really useful if you are in a hurry or the formula is relatively short.

( If it is a long complex formula, I think it is easier to see the breakdown in the Evaluate Formula dialog box. - just my personal preference.)

 All you need to do is double-click on the formula, select the component that you want to evaluate and press F9. If you have additional formulas, select them and press F9 again. When you are done, press ESC to get out.

Two ways to do the same thing... and yes, if we look around, we will probably find a 3rd method too. Have a good day.

Using Data Ribbon to Find the Top 5 Items

Below is an excerpt from the new course we are offering entitled "Audit and Analytical Uses for Excel Tables and Filters. It is a free course and has 1 Hour of CPE associated with it. Check it out at http://cpaselfstudy.com/item_185/Audit-Analytical-Uses-for-Excel-Filters-Tables.htm

Finding the Top 5 Invoices

For audit purposes, assume that you wanted to manually select the 5 largest invoices for testing. In this case, you would sort the Sales_Amt. column  by clicking the Sort icon on the Data tab and selecting “Sort Largest to Smallest”. The top portion of the result is displayed below. Notice that all the related data is also moved.

That was the quick and dirty way and works if the data list is relatively small. If the list was larger, you would want to use a Number Filter to identify only the Top 5 invoices. In this case, you would click the drop down box next to Sales_Amt and choose “Number Filters”, then select “Top 10…” as shown below.

Using the arrow keys next to the number “10”, click on the down arrow until the number “5” appears. This will filter the list to the Top 5 items. It will now look like the figure below:

Migrating from Excel 2003 To Excel 2010

One of my CPE pilots sent me a link to a Microsoft PDF file that discusses migrating from Excel 2003 to Excel 2010. It is a short but nicely laid out article with lots of pictures!

I have discussed a couple of differences between Excel 2010 and Excel 2007 in earlier posts so I won't repeat myself but thank goodness File is back and Print Preview has moved back where it belongs under the File ribbon tab. It does not contain all the changes - just the basic ones that most people would be interested in.

http://officeimg.vo.msecnd.net/en-us/files/918/662/AF101882555.pdf

Thanks for the link Amy.

2 Way Lookup using Index Match

I taught an Excel functions class at the Indiana CPA Society last week and thought I would share one of the examples we went over. We used the INDEX MATCH  function to create a 2 way lookup.
It was a great class. If you missed it, look for it next year- It is called Functions Every CPA Needs To Know.
If you don't have time for the class, I am putting together the course and will be offering a self study version shortly so check at http://cpaselfstudy.com/category_14/Excel.htm  end of October. 
My Dashboards self study course is almost done. It should be avaible in another week or so as the CPE piloting is basically done.
2 Way Lookup

In this example, we wanted to lookup Unit Sales for a specified month (I1) and for a specified Product Line (I2).

We need Excel to match the contents of I1 which is the month of July and the contents of I2 which is TV product line with the table in cells A2.E13 and find the intersection point and return the answer.

If I asked you to look at A1.E13 and to find the unit sales for TVs for the month of July, you would look down Column A until you found July and then you would look across the columns until you found TV and you would find the intersection point of 763 which is the answer. This is that a 2way lookup does.

1. Click in cell I3
2. Select the Index function from the Lookup category
3. Click OK
4. Enter A1:E13 as the Array
5. Click in the Row_number section of the dialog box
6. Click in the Name box and click on the Match function
7. Enter I1 as the Lookup Value
8. Enter A1:A13 as the Lookup_array
9. Enter 0 as the Match_Type
10. Click OK
11. You will have an error message – but don’t panic
12. Go up to the formula bar and click on the fx button to bring the Index dialog box back
13. Click OK
14.Click in Col_num in the Index dialog box
15.Click in the Name box and select the Match function
16.Enter I2 as the Lookup_Value
17.Enter A1.E1 as the Lookup_Array
18.Enter 0 as the Match Type
19.Click OK

Notice the 8 and the 4 in the dialog box (over on the right)- the 8th row and the 4th column intersect at cell D8.  If you use a Data Validation drop-down for I1 and I2 so much the better.

For other software tips and articles, go to http://cpaselfstudy.com/pages/SoftwareTips.php






Pin Your Spreadsheet

GET PINNED!!!!

I actually got this tip idea from Francis Hays, "The Excel Addict" but I really liked it so I thought I would share it.  I learned how to pin my files awhile ago and of course promptly forgot it since I did not use it. (Let that be a lesson to you.)

Anyway, I have a couple of spreadsheets that I open almost on a daily basis so usually I can just go to File>Open or look in Recent Documents and easily find them. Sometimes, though I have opened a lot of other files that day and of course, then I have to go search for these files- which can be a pain.
What you can do is PIN the file and although it will move down the most recently used list, it will not disappear until you unpin it.

Click on the Quick Office Button.

Click on the gray pin (located to the right of the file name)

The pin will turn green to indicate that the file is 'pinned' to the list.

Click on it again and it will turn back to gray and be unpinned. 
How easy is that? Now that I have been reminded of this trick, I plan to use it.
Have a great weekend.

Subtotal Example with Filtering

Using the Subtotal Function
I wanted to spend a minute and show a subtotal example because of some emails I received. A lot of people seem to be only familiar with the Subtotal command on the Data tab or Data menu that automatically creates the subtotal for you . Obviously that is the fastest way but typically it requires sorting your data and sometimes you don't want to do that.

In those cases, you would want to use the SUBTOTAL function .
=SUBTOTAL(function_num, ref1, ref2, ...).

The beauty of the Subtotal feature is that it does not have to be at the bottom of the data- it can be anywhere on the sheet. In addition, you do not have to manipulate your data.
Below is a simple example, where I filtered sales by State and I wanted only the total for Massachusetts' sales. When I use =SUM(C6:C9) and select the filtered range, it added up all the sales of all states which was 1800.
When I used the Subtotal function on the filtered data =SUBTOTAL(109,c6:69), Excel returnd the total sales of the filtered data which was 900.

Enjoy the week. Fall may actually be here - crisp weather and the leaves are just starting to turn.

Excel Arrays - Audit Plan Uses

I hope that everyone had a great Labor Day.
We went up to Chicago and had a great time at the Field Museum and shopping as well as eating at some great places. The weather was perfect.
I've been busy redesigning and reformatting CPASelfstudy.com to make it easier to navigate so I have not put a lot of time into this blog lately however that should change shortly.

I did want to mention that the Missouri CPA Society published our article on Incorporating Excel Arrays into Your Audit Plan as that may be of interest to you. They have an online version of  The Asset that can now be accessed  -  http://theasset.dirxion.com/WebProject.asp?BookCode=jul10flx&from=2

Spreadsheet Design 101

So many people, ignore spreadsheet design but it is important if you are building a complex worksheet or one that you will be referrring back to over time.

Spreadsheet Design


Two things make a good Excel spreadsheet. Good data is obviously one of these things but the other that is often overlooked is good design. Good design helps to ensure a reduction in errors which is obviously key.

A nicely formatted spreadsheet is nice and clearly it is easier to understand; however, a nicely formatted spreadsheet and well designed spreadsheet are not synonymous. A well designed spreadsheet is concerned with the organization of data and assumptions rather than appearance although obviously appearance is a factor – it is just not the primary factor.

Good design helps to ensure:

• Navigability
• Ease of use
• Reduction of repetitive input which helps to cut down on errors
• Locate errors more easily

 Too often people dive into creating a spreadsheet without a thought as to the end product. How many of you have had to cut and paste or insert rows and columns for data that needed to be moved later or to make room for data that had not considered when you started? I would be surprised if everyone reading this failed to raise their hand!

Before touching that keyboard consider the following:

• Who will be using the spreadsheet?
• What is the purpose of the spreadsheet?
• What are the required inputs?
• What mathematical expressions are you thinking about using?
• What are the desired outputs?
• How are you going to test the model?

It is highly recommended that after considering the questions above, you sketch out mentally or on paper how the spreadsheet should be laid out. You should have a minimum of 3 separate modules or areas and usually you will have at least 4.  These days, rather than modules, think of sheets within the Excel file.

• Input Area All the user inputs and defined variables (constants) Including assumptions and documentation
• Work Area Calculations The raw data and formulas to process the inputs – nitty gritty stuff
• Output Summarization and display of data
• Reports Charts and tables -(Presentation)

  

Clearly you could have a number of modules in the work area depending upon the complexity of the problem you are examining. You should plan on a number of modules in your work area if you anticipate a complex spreadsheet.

 Design Points

As you begin to design a spreadsheet you need to consider the following design points

Documentation

Yes- that old thing. Even if you hate to document what is going on with the spreadsheet, at least put the date, your name and the title of the worksheet or project. No one ever thinks they have the time to sit down and document but at least if you set a standard design with an assumptions section you will have a starting point later.

For everyone who says they have no time to document consider how much time you have spent staring a spreadsheet that you created 7 or 8 months before wondering what the heck that number is or what the purpose of a cell’s formula is. Admit it – we all have. I have one individual who admits every time budgets roll around he cringes because he knows that he will have to spend time figuring out what he did the previous year because he didn’t bother to document it in the worksheet and he can never find those little scraps of paper with the notes he made at the time.

Natural Flow

Ensure there is a natural flow of information down each worksheet instead of having to jump forwards, backwards and sidewards.

Headings

Excel in many cases assumes that there is a heading above the raw data. It also makes things much easier to read. Bolding the headings increases readability too as opposed to italicizing which is often difficult to read.

  

Blank Rows and Columns

  

• Blank rows and columns seem to be a favorite among accountants for a cleaner look; however, be aware that a lot of Excels built-in features assume a blank row or column means no more data. In other words do not break up data that you may need to sort or filter with blank rows.

•  For design purposes some people recommend leaving at least 4 blank rows above your headings and data as these can be used for viewing Totals instead of having to scroll down the bottom of a large spreadsheet. Also blank rows at the top of a spreadsheet are useful for criteria for database functions and the advanced filter feature.

  

Absolute Cell References

• Use absolute cell references wherever possible. It is much easier to change a single cell and have the impact ripple through the spreadsheet than to have to find the cell, change it and then copy it across or down and then view the impact. I think that you can also see how changing just one number is more efficient and would help to reduce potential errors.

•   If you do use absolute cell references set them up in a separate section so that they are easier to locate.

Cell References

• Always use cell references instead of typing in numbers. It increases readability and helps reduce errors and repetitive data entry.

•  It is also helpful to have input cells in the same column – reduces errors in referencing them in formulas.

Sorting

If possible, sort your data as many Excel functions such as Vlookup assume that your data is sorted.

Dates

Ozgrid.com recommends using real dates for headings and to format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2001, 1/2/2001, 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road if you need to use them in formulas. Iff you don't need them -nothing is lost.

Use Single Cells

Keep formulas simple.
Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put into your spreadsheet; don’t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily by concatenating them; however, the same cannot always be said for the reverse.

Break Model Down

If the spreadsheet is complex, large or has a lot of linked cells break it down into manageable components. Errors increase exponentially as the complexity of the model increases. The frequency of use also impacts the potential error rate.

Test your spreadsheet

Test your model before you begin using it! 

• Test the formulas 
•   Use error checking formulas in adjacent cells – IF functions can be helpful
•  Test range names if you have used them
•  Test formulas and ensure they were copied correctly
•  Check the effects of rounding and number formatting
•  If using unusual functions check to make sure they were correctly applied.
• Test the charts and reports
• Compare results of data to previous versions or prior periods

 And test it again, after you make changes to it. Don't use the same spreadsheet year after year without testing it periodically after your have made revisions.
This last is an important point, that many people forget about - to their regret!

Subtotal - 9 versus 109

 =SUBTOTAL(9,range) versus =SUBOTAL(109,range)

I have been busy revamping CPASelfstudy.com's site design and working on some Excel Ebooks and unfortunately my blogging has fallen by the way side - temporarily.
I do plan to start writing again once or twice a week starting next week.

My husband has been showing me all the different things he has been doing with the subtotal function and filtering so that is probably what I will talk about next week. (And yes, we do need to get a life!)

One thing that puzzled me was that he was using a function =SUBTOTAL(109,range) instead of  the function I was familiar with which is =SUBTOTAL(9,range) to sum and subtotal.

Sorry- my original post on the differences between =SUBOTOTAL(9,range0) and SUBTOTAL(109,range()) was incorrect.

Thanks Jan for the correction :
SUBTOTAL(9,...) totals everything except cells with subtotals and filtered cells.

SUBTOTAL(109,...) totals everything except cells with subtotals and filtered cells and hidden rows.

Sorry- the link in the earlier post did not work for some reason. This is an article I wrote on Interactive Charts for the Journal of Accountancy awhile back; however, I thought many of you would find it useful.
It walks you step by step through how to create an interactive (dynamic) chart. If you have any trouble as you go through it make sure that your sheet name is the same as mine.
If you have a real interst in this area, check out my Ebook on Creating Interactive Charts at CPAselfstudy.com

http://www.journalofaccountancy.com/Issues/2006/Apr/ChartsInRealTime.htm

Charts in Real Time

Charts in Real Time

Joins - Links between Tables in a Database

Joins- Links Between Tables

To retrieve information from multiple tables, there must be a link between the tables. This is commonly known as the key field. It is a unique field within each record. For example, in our telephone address book example, the key field would be telephone number. For Access or other database program to link two tables together, it must create a link between these key fields which must be of the same data type in both tables.

Joins

There are several types of links or joins.

Equal join pulls records that have a match in both tables.
For example, if you were pulling information from a Customer Table and an Order table, if Customer A was in your customer table but had not placed an order, no information about Customer A would display but the information for Customer B would print since there is a matching record in both tables.

Left join pulls all records from the left (first) table if there are no matching records in the right table. In this case Client C would not display since he cannot be found in the order table. Conversely, if you were pulling from the right table, no information about Client D would appear since that company is not in your Client table.

Right join pulls all records from the right table and only matching records in the left table.

If tables in a query aren't joined to one another, either directly or indirectly, the database doesn't know which records are associated with which, so it displays every combination of records between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10X10). This result set of every possible combination is called a cross product or Cartesian product. This type of query can take a long time to run.

The point of this explanation is that you must consider what information you actually want and then decide upon which join to use. Equal joins are the most common however if you do need to use an outer join, make sure you place the tables in the correct order.

What is a Database?

What is a Database?


A database is an organized set or collection of related data that is stored in a file. The data in a database is entered in as records that consist of items or fields of data. Everyone has a database- take a look at your address book. An address book is a set of related information about a person (his name, telephone number and perhaps their address, fax number etc.). It is also organized - it is sorted alphabetically by name.


Each record is essentially a row of related information. Each field within the record contains information that pertains to that record. For example, a telephone directory can be considered a database. Each record has specific and unique information. The following fields or columns of information would typically be found in a telephone directory: first name, last name, address, and city and telephone number.

The following example shows a sample database:

Last Name, First Name, Address, City and Telephone are columns within the database table and are called Field Names. In other words, they identify the type of data shown in the columns below. First Name is the field name while John, Joan and Nancy are fields within individual records. Each row or record holds unique information. Only John Smith lives at 302 Milton Street in Indianapolis and has the telephone # 876-2256. In this case, the unique field would be telephone number.

A relational database allows you to create smaller and more manageable files that can be joined to extract or compare data on an as needed basis because the data in one file is linked to another via a unique field.

A database management system (DBMS) controls the structure of a database and access to the data. In other words, it creates, manages and protects that data as well as providing access, which also allows the user to integrate and share data across several files.

I thought I would change topic for the moment and talk about some important concepts to understand before creating a database.

I am off on vacation starting tomorrow so I probably won't be blogging. Have a wonderful 4th of July.

Database Design


The core of good database design is planning. Before actually creating a database, you should have a good idea of the kind of data your database will contain and how that data should be broken down. It is recommended to plan your database out on paper before beginning the creation process.

When creating databases, there are rules that most designers follow which help them create consistent, efficient well thought-out databases. This set of rules is called normalization, which dictates that your database tables will eliminate inconsistencies and maximize efficiency. The goal of normalization is to reduce data to its simplest structure with minimum redundancy and maximum data integrity.
Some of the important goals of normalization are:

• All fields should be broken down so that data cannot be divided further. For example, the field Name could be broken down further to last name, middle name, and first name.

• Each table must have one unique key field called a primary key. That is to say, there must be one field that identifies a record and does not allow duplicates. An example of this would be a social security number or customer number.
• All fields must directly refer to the primary key. For instance, in a customer table, you would only include information related to that customer, such as name, address, etc. You would not include a field called “Product name” in a customer table.

• A field cannot contain more than one value.

Thus, a normalized database stores each piece of information in its own table, all fields are broken down to their lowest possible level and each piece of information can be referred to by the its primary key. This is an important concept and is the entire premise behind a relational database.

Stock Charts

It's been awhile since I have written anything here. If you have been checking, my apologies - you know how the summer goes- between working, chauffering Kelsey to summer school and attempting to improve and expand my garden my time just evaporates. If you click on the RSS feed button, the blog will be delivered to your email and that will save you a trip here.

Today, I wanted to talk about some very basics in creating a stock chart. Yes, given the current stock market situation, I thought people might be interested in knowing how to do this even though it will be a depressing exercise if you track over time. However, some of you will find it useful.

Excel has four different types of stock charts.

• High-Low-Close
• Open-High-Low-Close
• Volume-High-Low-Close
• Volume-Open-High-Low-Close

The High-Low-Close chart is often used to illustrate stock prices. When you chart the data, the High and Low values are displayed with vertical lines while the open and the black bars represent close prices.

It is important to remember that you must organize your data in the correct order to create this and other stock charts. In other words, if you opt to create a High-Low-Close chart then your data needs to be ordered High Price, Low Price, Closing Price.
Creating Stock Charts


It is important to remember that you must have the correct number of series that the chart requires. Excel also requires that the data be in the proper order.

1. Select your data

2. Click Insert and Select Other Charts icon

3. Select the type of stock chart you wish to create




 

When the stock chart is created, a whole series of layouts and chart styles are displayed on the ribbon

Here is another example:

I made up data. When I used real data, it was too sad.

SUMIF

SumIf is a great function that a lot of people don’t know up about. Basically it combines two very popular functions -the SUM function and the IF Function. SumIf tests specified cells and if those cells meet certain conditions or “criteria” the selected cells are then summed up.


Think of the possibilities: You may wish to sum up receipts by different lockboxes or sum up the value of past-due invoices. Or you may just want to add up negative numbers in a column quickly or add up the returns or overtime for a particular day.

I am using a very simple example to illustrate SumIf. In the example below, I tested to see if any of the house prices in cells A3: A6 exceeded 150,000 so that I could sum the related realtor commissions. In looking at the example, you can see that there are two houses that meet this criterion (at row 4 and 5) and that the corresponding realtor commissions are in Column B.

The house prices at row 4 and 5 met the specified criteria so the corresponding values in Column B, (12,500 and 10,000) were added up to calculate total commissions on houses with prices over $150,000.


So, basically, the IF component checked to see if any houses were over $150,000 and then the SUM component added up the corresponding realtor commissions associated with those houses in Column A.


Two notes of caution of a SUMIF.

*Notice that I started my range at A3 instead of A2.It is important to remember not to include your column headings as part of the data range when you select your data as this may result in an incorrect answer. If you have a terrible memory and don’t like to fuss around then select the entire column by clicking on the column letter so that you don’t have to worry about it.

*If you use IFs frequently, you may have already noticed the quotation marks around the criteria. SumIF is not considered a logic function. It is considered a Math and Trig function so the syntax is a bit different and quotation marks are needed if you type the criteria in as I did.

Excel is usually forgiving and will automatically insert them if you forget. If the criterion used is a cell reference, such as B12, then you would not need to use the quotation marks; however, the contents of the cell would need to include the operator if there was one.

An alternative way to use SUMIF  if shown below.  This is an example of a rollup. I have personnel data and I want to add up salaries IF they meet the shift criteria that I have in cell G2.

 Here I have told Excel to add up all the salaries in Column F if the Shift information in Column E meets the criteria specified in cell G2. In this case, I want to add up all the salaries for everyone on Shift 1.

(To make it even more useful, use a data validation list in cell G2 containing the different shift numbers).