=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.
Software examples that you can easily understand and immediately apply to your own business needs.
Wednesday, August 18, 2010
Thursday, August 5, 2010
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
Wednesday, July 21, 2010
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.
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.
Wednesday, July 14, 2010
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.
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.
Subscribe to:
Posts (Atom)
Ms. Excel- Resident Excel Geek