A series or collection-based function is one that operates on a group of values. The values are stored in a collection called an array. 

1. Open the Red Oak High School1 workbook you started in lesson 7
2. In cell B12, type Average
3. In cell C12, type =AVERAGE(
4. To get the average of the first student's grade, select cells C3:C12 and press Enter
5. Click cell C14 to give it focus
6. On Standard toolbar, click the Paste Function button
7. In the Function Category, click Most Recently Used. In the Function Name, click AVERAGE; if you don't see AVERAGE, then in the Function Category, click Statistical, and in the Function Name, click AVERAGE. Click OK
8. Move the AVERAGE dialog box to the right to make sure you can see cell C14
9. Since the AVERAGE dialog is a modeless dialog box, which means you can have access to the main window behind it while the dialog box is opened, select cells C5:C12. Click OK. 
   Right-click cell C14 and click Copy
10. Select cells D14:J14 and press Enter. Notice that since the copying was done on a cell that has a function's result, only the formula was copied. Also notice the errors on cells I14 and J14; don't worry about those errors, they will find a solution in a few seconds. 

The Maximum in a Series

The MAX function is used to get highest of a series of value.

Practical Learning: Finding the Maximum Value of a Series

1. Click cell L2 and type Maximum
2. Click cell L3 and type =MAX(
3. Select cells C3:J3 and press Enter
4. Select cells K3 and L3
5. Drag their Fill Handle (the big point in the lower right corner of L3) down to cell K12:L12 

Counting the Number of Numeric Values in a Series

Imagine you want to perform some type of counting in a series of cells, Microsoft Excel provides various functions used to count items in a collection. Everything depends on what and/or how you want to count.

The COUNT function counts the number of cells that contain numbers in the range or group of cells involved in a collection.

Counting the Number of Non-Empty Cells in a Series

While the COUNT function counts the numbers, the COUNTA function counts the number of non-empty cells (regardless of what is in those cells, as long as they contain something) in the cells involved.

Business Functions

Introduction

An asset is an object of value. It could be a person, a car, a piece of jewelry, a refrigerator. Anything that has a value is an asset. In the accounting world, an asset is a piece of/or property whose life span can be projected, estimated, or evaluated. As days, months or years go by, the value of such an asset degrade.

When an item is acquired for the first time as “brand new”, the value of the asset is referred to as its Cost. The declining value of an asset is referred to as its Depreciation. At one time, the item will completely lose its worth or productive value. Nevertheless, the value that an asset has after it has lost all of its value is referred to its Salvage Value. At any time, between the purchase value and the salvage value, accountants estimate the value of an item based on various factors including its original value, its lifetime, its usefulness (how the item is being used), etc.

The Double Declining Balance

The Double Declining Balance is a method used to calculate the depreciating value of an asset.  To get it, you can use the DDB function whose syntax is:

DDB(cost, salvage, life, period, factor)

The first argument, cost, represents the initial value of the item and it is required. The salvage argument is the estimated value of the asset when it will have lost all its productive value. This argument also is required. The cost and the salvage values must be given in their monetary values. The value of life is the length of the lifetime of the item; this could be the number of months for a car or the number of years for a house, for example. The period is a factor for which the depreciation is calculated. It must be in the same unit as the life argument. For the Double Declining Balance, this period argument is usually 2.

Practical Learning: Calculating the Double-Declining-Balance

1. Start a new workbook and fill up Sheet1 as follows:
    
2. Save it as Depreciation
3. Double-click Sheet1 to put its label into edit mode. Type Double-Declining-Balance and press Enter
4. Click cell C8 and, on the main menu, click Insert -> Function...
5. In the Paste Function dialog box, in the Function Category list, click Financial. In the Function Name list, double-click DDB and move the DDB window so you can see the values on the worksheet
6. Click the box to the right of Cost and, on the worksheet, click cell C4
7. In the DDB window, click the box to the right of Salvage and, on the worksheet, click cell C5
8. In the DDB window, click the box to the right of Life and, on the worksheet, click cell C6
9. In the DDB window, click the box to the right of Period and, on the worksheet, click cell C7
10. In the DDB window, click the box to the right of Salvage and type 2
     
11. Click OK

The Straight Line Depreciation

Microsoft Excel provides another method used to calculate the depreciation of an item. This time, the depreciation is considered on one period of the life of the item. The function used is SLN and its syntax is:

SLN(cost, salvage, life);

The cost argument is the original amount paid for an item (refrigerator, mechanics toolbox, high-volume printer, etc). The salvage, also called the scrap value, is the value that the item will have (or is having) at the end of Life. The life argument represents the period during which the asset is (or was) useful; it is usually measured in years.

Practical Learning: Calculating the Double-Declining-Balance

1. Click Sheet2 and fill it up as follows:
    
2. Double-click Sheet2 to put its label into edit mode. Type Straigh-Line and press Enter
3. Click cell C8 and, on the main menu, click Insert -> Function...
4. In the Paste Function dialog box, in the Function Category list, click Financial. In the Function Name list, double-click SLN and move the SLN window so you can see the values on the worksheet
5. Click the box to the right of Cost and, on the worksheet, click cell C4
6. In the SLN window, click the box to the right of Salvage and, on the worksheet, click cell C5
7. In the SLN window, click the box to the right of Life and, on the worksheet, click cell C6
8. In the SLN window, click OK 
9. Click OK

The Sum of the Years' Digits

The Sum-Of-The-Years’-Digits provides another method for calculating the depreciation of an item. Imagine that a restaurant bought a commercial refrigerator (“cold chamber”) for $18,000 and wants to estimate its depreciation after 5 years. Each year is assigned a number, also called a tag, using a consecutive count. This means that the first year is appended 1, the second is 2, etc. This way, the depreciation is not uniformly applied to all years.

Year => 1, 2, 3, 4, and 5.

The total count is made for these tags. For our refrigerator example, this would be

Sum = 1 + 2 + 3 + 4 + 5 = 15

Each year is divided by this Sum, also called the sum of years, used as the common denominator:

This is equivalent to 1. As you can see, the first year would have the lowest divident (1/15 ≈ 0.0067) and the last year would have the highest (5/15 ≈ 0.33).

To calculate the depreciation for each year, the fractions (1/15 + 2/15 + 3/15 + 4/15 + 5/15) are reversed so that the depreciation of the first year is calculated based on the last fraction (the last year divided by the common denominator). Then the new fraction for each year is multiplied by the original price of the asset. This would produce (this table assumes that the refrigerator will have a value of $0.00 after 5 years):

Overall, Microsoft Office uses the following formula to calculate an item depreciation using the Sum-Of-The-Years'-Digits:

The function used to calculate the depreciation of an asset using the sum of the years' digits is called SYD and its syntax is:

SYD(cost, salvage, life, period)

The cost argument is the original value of the item; in our example, this would be $18,000. The salvage parameter is the value the asset would have (or has) at the end of its useful life. The life is the number of years the asset would have a useful life (because assets are usually evaluated in terms of years instead of months). The period parameter is the particular period or rank of a Life portion. For example, if the life of the depreciation is set to 5 (years), the period could be any number between 1 and 5. If set to 1, the depreciation would be calculated for the first year. If the Period is set to 4, the depreciation would calculated for the 4th year. You can also set the period to a value higher than life. For example, if life is set to 5 but you pass 8 for the period, the depreciation would be calculated for the 8th year. If the asset is worthless in the 8th year, the depreciation would be 0.
 

Practical Learning: Calculating the Depreciation Using SYD

1. Click Sheet3 and fill it up as follows:
    
2. Double-click Sheet3 to put its label into edit mode. Type Sum-Of-Years and press Enter
3. Click cell C8 and, on the main menu, click Insert -> Function...
4. In the Paste Function dialog box, in the Function Category list, click Financial. In the Function Name list, double-click SYD and move the SYD window so you can see the values on the worksheet
5. Click the box to the right of Cost and, on the worksheet, click cell C4
6. In the SYD window, click the box to the right of Salvage and, on the worksheet, click cell C5
7. In the SYD window, click the box to the right of Life and, on the worksheet, click cell C6
8. In the SYD window, click the box to the right of Life and, on the worksheet, click cell C7
    
9. Click OK
10. Notice that in our evaluation, we decided that the item would have a $0.00 value at the end of its life. Since this is still a big investment, it is very likely to still be very operational after 5 years. To experiment with different factors, let's assume that the item will have a value of $3500 after 5 years.
    On the worksheet, click cell C5. Type 3500 and press Enter
11. Now, imagine that we think that the item will have a value of $0.00 after 5 years but we want to know how much it will have depreciated the 3rd year.
    On the worksheet, click cell C5 and type 0
12. Click cell C7. Type 3 and press Enter
     
13. Compare with the value we got in our table above
14. Save the workbook