ECDL Excel Tips

ECDL Excel Tips

Functions are formulas that simplify calculations

Sum

This function adds the contents of cells e.g. =SUM(A1:A2)

Always place an equals sign before any functions

You can also click on the Insert Function on the Formula Bar to show the Insert Function Dialog Box

1. On the Select a Category drop-down box choose Math & Trig

2. Under the Select a Function options list, scroll down until you find the Sum function

3. Click OK

4. Click and drag on the cells you want to add up

5. Click OK

Maximum & Minimum

The Max function shows the largest number in a range

The Min function shows the smallest number in a range

1. Enter a list of numbers in column A

2. Click on the Insert Function button beside the Formula Bar

3. Choose Statistical under Select a Category

4. Select Max in the Select a Function list

5. Click OK

6. Select the range of cells in the list

7. Click OK

8. Use the same procedure to calculate the smallest number in the list

Count

This function counts the cells that contain numbers in a range

COUNTA counts the number of cells with a value

COUNTBLANK counts empty cells

1. Create a list of numbers with some empty cells

2. Click on the Insert Function button

3. Select Statistical under Select a Category

4. Scroll down through the Select a Function list

5. Choose the COUNT function

6. Click OK

7. Select the list of cells containing values

8. Click OK

9. Follow the same steps to use the COUNTA and COUNTBLANK functions

Round

The Round function rounds the number to as many decimal points you want to show

1. Click on the Insert Function button

2. Choose Math & Trig from the Function Category list

3. Scroll down through the list

4. Select the ROUND function

5. Click OK

6. Select the cell value you want to round

7. In the Num_Digits text box, enter 2

8. This will round the value to two decimal places

9. Click OK

Average

The Average function calculates the average out of a range of cells

1. Click on the Insert Function button

2. Select Statistical in the Function Category List

3. Choose Average from the Select a Function list

4. Select the range of numbers in the list

5. Click OK

The IF Function

This is a logical function that compares the contents of a cell and displays the answer depending on whether it is true or false

=IF(Logical Test, Value if true, Value if false)

For example:

If the value of a cell is less than 6, add by 4, if not, add by 2

=IF(B1<6,B1+6,B1+2)

You can calculate interest on a savings account depending on its worth by entering in the following values:

In cell B4, we want to apply an interest of 2% if the amount saved is less than €40 and an interest rate of 4% if the amount saved is greater than €40

=IF(B3>40,B3*4%,B3*2%)

Relative Addressing

When copying cells with formula answers Excel automatically calculates cells in different positions relative to the original formula e.g. A1+A2 copies to the right B1+B2 and C1+C2

Absolute Addressing

Absolute Addressing keeps the cell reference the same when calculating formulas

It is expressed using the dollar sign: $D$4

D4 will stay the same as other cells will be involved in the formula

The Amount in each savings account is entered into Column A

The Interest Rate is in cell D4

The Amounts are multiplied by the Interest Rate in Column B

In cell B4 is the formula: =$D$4*A4

The Fill Handle is used to copy the formula down Column B

This is a short guide included in the ECDL Excel manual.