Next up in our series of the Top 10 topics that you should know inside and out for the Texas High School UIL Computer Applications contest regional and state meets is….UPDATE QUERIES!  This list of 10 topics is focused more on advanced formulas and applications of the programs than it does on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  To date, the top 5 that we’ve covered have been:

1. IF Statements, Nest IF Statements, COUNTIF, SUMIF in Excel

2. Dates & Times in Excel

3. Select Queries in Access

4. Pivot Tables in Excel

5. VLOOKUP & HLOOKUP formulas in Excel

The update query is one of the most frequently tested query types on the Access portion of regional and state Computer Applications tests. Update queries allow you to update different fields in one or more different Access tables. Update queries offer some of the same features that many types of Access queries sport: ability to stipulate criteria, option to use the expression builder such that fields can be updated with the outcome of an input calculation, etc.

Keep reading →

This year, we’re putting together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  These 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  We covered the first item a while back in our blog post on IF statements…and conditional IF statements, SUMIF, COUNTIF, etc.  The second blog post introduced the second item: Using Times and Dates in Excel.   The third item was covered in our blog on using pivot tables in Microsoft Excel. Number four looked at Select Queries & Query Basics in Access. In this fifth installment, we’ll be looking at VLOOKUP and HLOOKUP formulas in Excel

VLOOKUP and HLOOKUP are “lookup” formulas in Excel that enable you to easily pull in data across different flat data sets. While VLOOKUP is more commonly used, both are frequently used to pull in data from one worksheet or workbook to another based upon a primary key that is common across the two data sets. For example, if you have a set of order information (date, quantity, product, revenue, customer ID) that includes a customer ID in it on one worksheet tab and a set of customer information in another tab that includes the same customer ID (along with customer name, email, address and phone number), you can use a lookup formula to pull in the customer name to the order information table (by finding the customer ID in the customer table and pulling in the customer name associated with that ID). The lookup formula in Excel thus allows you to compare flat files and pull data into a worksheet from a separate data file provided there is some unique field shared by the two data sets. In essence, it allows you to structure a select query in Excel.

Keep reading →

By popular demand, we’ve decided to put together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  This list of 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  We covered the first item a while back in our blog post on IF statements…and conditional IF statements, SUMIF, COUNTIF, etc.  The second blog post introduced the second item: Using Times and Dates in Excel.   Our third post was on Select Queries and Query Basics in Access.  The fourth item will keep us in Excel but turn our attention to pivot tables.

Pivot tables in Excel are similar to Crosstab queries in Access in many ways.  They are definitely one of the most powerful features of Microsoft Excel.  They allow you to cut, filter, manipulate and display summarized information based off of a stipulated data set.

Keep reading →

By popular demand, we’ve decided to put together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  This list of 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  We covered the first item a while back in our blog post on IF statements…and conditional IF statements, SUMIF, COUNTIF, etc.  The second blog post introduced the second item: Using Times and Dates in Excel.  This time, we’re turning our attention to Access for #4: Select Queries & Query Basics.

The select query is the most basic of queries that you’ll use in Access. Select queries allow you to select different fields from one or more different Access tables in order to combine data from different flat data sets. Select queries also offer some of the same features that many types of Access queries sport: ability to filter on different fields (based upon field value or any stipulated criteria), ability to hide/show included output fields in output date, ability to sort output table by any of the included columns, use of ‘Group By’, etc.

Keep reading →

By popular demand, we’ve decided to put together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  This list of 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  We covered the first item a while back in our blog post on IF statements…and conditional IF statements, SUMIF, COUNTIF, etc.  This blog post introduces the second item: Times and Dates in Excel.

Times and dates in Excel work the same way that they do in Access (with some minor differences – e.g. dates are set off by pound signs in Access formulas whereas they’re set off by quotes in Excel formulas).  To understand them, the key is grasping that every time and date (and time/date combination) is saved as a number in Excel (or Access).

Keep reading →

By popular demand, we’ve decided to put together a set of topics that coaches & competitors in the Texas High School UIL Computer Applications contest should know in order to be competitive at state and regional levels.  This list of 10 topics will focus more on advanced formulas and applications of the programs than it will on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  Thus, we’re starting with something that might qualify as relatively basic (IF statements), but rest assured that IF statements can get pretty complex pretty quickly when nested. For each of the topics in this list, we’ll refer you to some videos that we’ve pulled together to comprehensively explain the technique, formula or concept. IF statements (including standard IF statements, nest IF statements, COUNTIFs and SUMIFs) are covered in a series of 4 videos on IF statements that you can find here. While the full series is available for purchase, individual videos (easily accessible through the previews embedded below) are also available for purchase.

IF statements are basically formulas in Excel (related to IIF formulas in Access) that are structure to return one of two things depending upon whether a provided criteria is met or not.  There are three different parts to an if statement: IF(decision criteria, value if true, value if false).  Thus, let’s think of an if statement that is designed to return “>50″ if a cell value of A2 is greater than 50 and “<=50″ if it is not.  An if statement to accomplish this would have the following parts:

*  Decision Criteria: A2>50
*  Value if True: “>50″
*  Value if False: “<=50)
Putting these elements together, we end up with:  IF(A2>50, “>50″, “<=50″).
Keep reading →

Next up in our series of the Top 10 topics that you should master in preparation for the Texas High School UIL Computer Applications regional and state meets is the Crosstab Query in Access.  This list of 10 topics is focused more on advanced formulas and applications of the programs than it does on basics (e.g. basic math formulas in Excel, letter formats and other Word templates, etc).  To date, the past 6 that we’ve covered have been:

1. IF Statements, Nest IF Statements, COUNTIF, SUMIF in Excel

The crosstab query is basically Access’ answer to Excel’s pivot table. As such, tests that require you to create a pivot table in Excel that mirrors a crosstab query that you create in Access are not entirely uncommon. Crosstab queries allow you to take data and create a matrix of sort by organizing data in a grid that is setup using column headers, row headers and a value field. If you use summed revenue as your value field, years as your column headers and customers as your row headers, you’ll end up with a matrix that organizes your data such that each row is an individual customer and each column shows the sum of that customer’s revenue for the year indicated in the column header.

Keep reading →