MS Excel 2003 Training 202- Data Consolidation

To summarize and report results from multiple worksheets, you can consolidate data from each worksheet into a master worksheet. The worksheets can be in the same workbook or other workbooks. When you consolidate data, you are assembling data so you can more easily update and aggregate it on a regular or ad hoc basis.

For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.

To consolidate data, you use the Consolidate command from the Data menu to display the Consolidate dialog box. You can use this dialog box in several ways to consolidate your data:

Position  Use this approach when the data in all worksheets is arranged in identical order and location.
Category  Use this approach when each worksheet organizes the data differently, but has the same row and column labels, which you can use to match the data.
3-D formulas  Use this approach when the worksheets do not have a consistent pattern you can rely on. You can create formulas that refer to cells in each range of data that you're combining. Formulas that refer to cells on multiple worksheets are called 3-D formulas.

Consolidate by position

Consolidate by position when the data in all source areas is arranged in identical order and location; for example, if you have data from a series of worksheets that were created from the same template, you can consolidate the data by position.
You can set up the consolidation to update automatically when the source data changes, but you won't be able to change which cells and ranges are included in the consolidation. Or you can update the consolidation manually, allowing you to change the included cells and ranges.

Consolidate by category

Consolidate by category when you want to summarize a set of worksheets that have the same row and column labels but organize the data differently. This method combines data that has matching labels from each worksheet.
You can set up the consolidation to update automatically when the source data changes, but you won't be able to change which cells and ranges are included in the consolidation. Alternatively, you can update the consolidation manually, allowing you to change the included cells and ranges.

Consolidate by using 3-D formulas

When you use 3-D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.) in formulas, there are no restrictions on the layout of the separate ranges of data. You can change the consolidation any way you need to. The consolidation updates automatically when the data in the source ranges changes.
Use formulas to combine data   In the following example, the formula in cell A2 adds three numbers that are located in different positions on three different worksheets.



Add data to a consolidation with 3-D references   When all of your source worksheets have the same layout, you can use a range of sheet names in 3-D formulas. To add another worksheet to the consolidation, just move the sheet into the range your formula refers to.



Other ways to combine data

You can create a PivotTable report

MS Excel 2003 Training 101 - SUMIF and COUNTIF

SUMIF

Adds the cells specified by a given criteria.
Syntax
SUMIF(range,criteria,sum_range)

Range   is the range of cells that you want evaluated by criteria.
Criteria   is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", or "apples".
Sum_range   are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.
Remarks
Sum_range does not have to be the same size and shape as range. The actual cells that are added are determined by using the top, left cell in sum_range as the beginning cell, and then including cells that correspond in size and shape to range. For example:



You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
Microsoft Office Excel provides additional functions that you can use to analyze your data based on a condition or criteria:

To count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function.
To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF function.
To analyze data in a list based on criteria, such as profit margins or product types, use the database and list management functions (DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, and DVARP).



COUNTIF

Counts the number of cells within a range that meet the given criteria.
Syntax
COUNTIF(range,criteria)

Range  is the range of cells from which you want to count cells.
Criteria  is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
Remarks
You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition.
To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function.
To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.

Example 1: Common COUNTIF formulas



Example 2: COUNTIF formulas using wildcard characters and handling blank values



Note To view the number as a percentage, select the cell and click Cells on the Format menu. Click the Number tab, and then click Percentage in the Category box.

MS Excel 2003 Training 101 - Conditional Formulas

Testing whether conditions are true or false and making logical comparisons between expressions are common to many tasks. You can use the AND, OR, NOT, and IF function to create conditional formulas.
The IF function uses the following arguments.



Formula with the IF function

 logical_test: The condition that you want to check.
 value_if_true: The value to return if the condition is true.
 value_if_false: The value to return if the condition is false.

What do you want to do?
Create a conditional formula that results in a logical value (TRUE or FALSE)
Create a conditional formula that results in another calculation or in values other than TRUE or FALSE
Create a conditional formula that results in a logical value (TRUE or FALSE)
To do this task, use the AND, OR, and NOT functions, and operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.).



Create a conditional formula that results in another calculation or in values other than TRUE or FALSE

To do this task, use the IF, AND, and OR functions.



Advanced If statement usage:
Using multiple criteria with the “AND” and “OR” functions

Sometimes you need to check more than one criteria with the If statement. For example:
Only if the student has grades greater than 80 in Math, greater than 80 in English and greater than 80 in History, only then write “good student”, else write “Average Student”.


The way to implement such a case is using the “AND” function which will include all the multiple criteria inside of it.
This “AND” function, is then put inside the criteria section of the If statement.
Thus for the above example, the whole function could look something like this:


=IF(AND(C1>80,D1>80,E1>80) , “Good student” , “Average Student” )

Note that the criteria part: AND(C1>80,D1>80,E1>80) will be met only if all three parts of it are met.


The function “OR” is used in a same manner, but its meaning is slightly different.
Instead of checking if all its parts is met (as is the case of the “AND” function), it checks if at least one of the criteria inside it is met. If it is, then the whole “OR” is considered met.


Hence, in the following formula:

=IF(OR(C1>80,D1>80,E1>80) , “Good student” , “Average Student” )

It is enough that C1 is really greater than 80 , or that D1 is really greater than 80 or that E1 is really greater than 80 (or some combination of these three), to have the if function write “Good student”. Only if none of these cells (C1, D1 or E1) are greater than 80 the If function will result writing “Average Student”.