1.

To begin, start Excel and open the file named Practice 5 in your Excel 2003 Practice folder.

You can use File->Open, or click the file open button on the standard tool bar.

The first goal with this worksheet is to find the total profit for the Eastern region. The named ranges Year, Region, and Profit have been defined in this workbook to make the formulas easier to work with.

2.

We can easily find the total profit for the eastern region by using the SUMIF function.

 The structure of the function is

SUMIF(range to be tested, test condition, range to be summed)

Select cell F2 and choose Insert->function from the menu bar to display the insert function dialogue box. Choose SUMIF from the math and trig category and click OK.

When you see the Function Arguments box, type Region in the range text field, “=East” in the Criteria text field, and Profit in the Sum range field. When you are finished click OK to enter the function. The SUMIF function will check the data in the Region range (C3:C23) to see if there are any values that meet the condition =East. If there are, SUMIF will sum the corresponding values in the Profit range.

Notice that you did not have to use an array formula in this case, as the SUMIF function is designed to handle this kind of situation quite nicely.

When you want to calculate the average profit for the West region however, you will run into a problem. Excel does not have an AVERAGIF function. We must create our own using an array formula, an IF function, and the AVERAGE function.

3.

Explanation

Look at this IF function.

If(Region = ”West”, Profit,“”)

The test condition checks to see if Region = “West”. If it does, a Profit value will be returned. If it does not, “” will be returned. But what does it mean to return “”?

 A pair of quotation marks with nothing between them will not be counted as a value in Excel. This means that if we nest this IF function inside an average function, the average function will ignore any “” it receives from the IF function.

=AVERAGE(IF(Region =“West”,Profit,“”))

This combined function will test to see if Region = “West”, and if it does, a Profit Value will be returned and incorporated into the average. If Region doe not =”West”, a pair of quotes with nothing between them will be returned and ignored by the AVERAGE function. For this to work it will have to be an array formula, so Excel will, row by row, apply the IF function to the corresponding cells in the Region and Profit Ranges.

4.

In the Practice 5 worksheet click cell G2, and type =AVERAGE(IF(Region="West", Profit,"")) into the formula bar. Make sure that you have the same amount of closing parenthesis as open parenthesis, and that there is an = equals sign in front. Press Enter to enter the formula.

You should get an error in cell G2 because the function was not entered as an array formula!

Excel will not treat Region and Profit as arrays of cells, resulting in a #Value error.

5.

To fix this, press the F2 key. This should display the formula in cell G2. Now, press Ctrl + Shift + Enter, to make this an array formula.

Now you will see the curly braces { } around the formula, and the correct result in cell G2.

You have just combined the AVERAGE function, and the IF function into an array formula to get this result. You have also taken advantage of defined range names to make the functions and formulas easier to understand.

6.

Save your work and exit Excel