Scribd
Upload
3 views

Excel Lecture 4

The document provides a comprehensive guide on using formulas and functions in Excel for accounting purposes. It covers how to insert formulas, use cell references, edit formulas, and apply basic functions like SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, and COUNTBLANK. Additionally, it explains the differences between relative, absolute, and mixed cell references, and includes practical examples for calculating salaries in a sample employee list.

Uploaded by

ahmedjojo6088
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views

Excel Lecture 4

The document provides a comprehensive guide on using formulas and functions in Excel for accounting purposes. It covers how to insert formulas, use cell references, edit formulas, and apply basic functions like SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, and COUNTBLANK. Additionally, it explains the differences between relative, absolute, and mixed cell references, and includes practical examples for calculating salaries in a sample employee list.

Uploaded by

ahmedjojo6088
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 15

Computer-Based Business Applications

Part1: Accounting using Excel


2nd Class
2

Chapter 3

Formulas and Functions


2.1 Inserting Formulas
• All formulas in Excel have one thing in common: They all begin with the equal
sign (=).
• Once you type the equal sign in the cell, Excel will understand that you need
to apply a specific formula.
• You can enter formulas in excel in two ways:

1. Manually: using the keyboard, type = sign, and then enter your formula.
• Formulas should be typed without spaces, but if you type spaces, Excel eliminates them
when you press Enter.
• While you are displaying formulas in the worksheet, you will not see the results of those
formulas.
2. Formula that is ready in Excel (in this case, Excel adds the opening equal
sign for you).
2.1 Inserting Formulas
• Formula that is ready in Excel (Excel adds the opening equal sign for you):
• In Home Tab, go to Editing group, click on the Drop-Down Arrow beside
AutoSum button. OR, click on Insert Function button in the formula bar.
• OR, In Formulas Tab, go to Function Library group, click on the Drop-Down
Arrow below AutoSum button. OR, click on Insert Function button.
• Now, the Insert Function dialog box will display.
• Click the name of the category of the function that you want,
(Most Recently Used is the default category),
Then select the function that you want.

• NOTE: if you click the AutoSum button ,


You will insert the SUM function in the selected cell.
How to insert a Formula

Manually Home Tab “Insert Formula Tab Formula Tab


Function”
button in
Editing Group Formula bar Function Function
1) Type the equal Library Group Library Group
sign in the cell in
which you need to
apply the formula.
2) Click the cells
which are used in
the formula. Formula that is ready in Excel

3) Press Enter.
Note: you can apply AutoSum function by pressing from keyboard Alt + =
2.2 Using Cell References in Formulas 6

• When you create a formula, you can reference a cell’s identifier rather than typing the
number that appears in that cell.
• A cell reference identifies a cell’s location in the worksheet, based on its column letter
and row number.
• If the data in a cell changes, the result of any formulas that reference the cell
change as well.
2.3 Using Cell References in Formulas 7

• Types of Cell References:


What Happens After Copying the Formula
Reference Type Structure
The Column letter “A” The Row number “1”
Relative A1 will change will change
Absolute $A$1 will NOT change will NOT change
Mixed $A1 will NOT change will change
Mixed A$1 will change will NOT change

• To insert a relative reference, you can: Type the cell address within the formula; OR Click a cell to include it
in the formula rather than typing the cell reference.
• You use relative cell references when you want the reference to automatically adjust when you copy or fill the
formula across rows or down columns in ranges of cells.
• By default, new formulas in Excel use relative references.
2.4 Editing Formulas
8

• As with numeric and text entries, you can edit the contents of formulas either in their
cells or on the Formula bar.
• To edit a formula in its cell, Double-Click the cell or press F2 to position the
pointer in that cell.
• If you select the cell, formula will appear in the formula bar; and you can edit it there.
• While editing Formulas, Excel displays each of the cell references in the formula in
different colors to outline the cell or cell range in the worksheet itself.
• While editing a formula, if you decide to back to the original formula and start
over, just press Esc button on the keyboard.
2.6 Using Basic Functions
9

• The Formulas tab in Excel 2016, provides access to a library of formulas and
functions.
• Functions provide an easy way to perform mathematical work on a range of cells,
quickly and conveniently. This section shows you how to use some of the basic
functions in Excel:
• SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, and COUNTBLANK.
2.6 Using Basic Functions
10

Sum To calculate the summation for a range of cells

Alt + +/=

Average To calculate the average for a range of cells

Count To count the number of cells in a range that contain numbers

Max To calculate the maximum value for a range of cells

Min To calculate the minimum value for a range of cells


2.6 Using Basic Functions- SUM
11

• Open Delta Co. Employees List file.


• To calculate the total amount of salaries, select the cell G8, Then:
• Type =SUM(G4:G7), OR =SUM(G4,G5,G6,G7), then Press Enter.
• OR: Type =SUM(; Then, click on the cell G4 and drag the mouse cursor to select the
range from G4 to G7.
• OR: click on the AutoSum button from Home tab, Editing group, or from
Formulas Tab, Function Library group. Now, the SUM function is inserted and
calculating (by default) the total from the above adjacent cell “G7” up to the first cell
include data “G4”. Finally, Press Enter.
2.6 Using Basic Functions- Average 12

• Open Delta Co. Employees List file.


• To calculate the total amount of salaries, select the cell G9, Then:
• Type =average(G4:G7), OR = average(G4,G5,G6,G7), then Press Enter.
• OR: Type “=average( ” ; Then, click on the cell G4 and drag the mouse cursor to
select the range from G4 to G7.
• OR: insert Average Function as we learn in section “2.1 Inserting Formulas”. Now,
the AVERAGE function is inserted. Notice that (by default) the range from the
above adjacent cell “G8” up to the first cell include data “G4” is selected. Edit the
range to become G4:G7, then press Enter.
2.6 Using Basic Functions- Minimum 13

• Open Delta Co. Employees List file.


• To calculate the least salary, select the cell G10, Then:
• Type =min(G4:G7), OR = min(G4,G5,G6,G7), then Press Enter.
• OR: Type “=min( ” ; Then, click on the cell G4 and drag the mouse cursor to select
the range from G4 to G7.
• OR: insert MIN Function as we learn in section “2.1 Inserting Formulas”. Now, the
MIN function is inserted. Notice that (by default) the range from the above adjacent
cell “G9” up to the first cell include data “G4” is selected. Edit the range to become
G4:G7, then press Enter.
2.6 Using Basic Functions- Maximum 14

• Open Delta Co. Employees List file.


• To calculate the least salary, select the cell G11, Then:
• Type =max(G4:G7), OR = max(G4,G5,G6,G7), then Press Enter.
• OR: Type “=max( ” ; Then, click on the cell G4 and drag the mouse cursor to select
the range from G4 to G7.
• OR: insert MAX Function as we learn in section “2.1 Inserting Formulas”. Now, the
MAX function is inserted. Notice that (by default) the range from the above adjacent
cell “G10” up to the first cell include data “G4” is selected. Edit the range to become
G4:G7, then press Enter.
2.6 Using Basic Functions- Count 15

• The COUNT function used to determine how many cells in a range contain a
number.
• The COUNTA function counts all nonblank cells in a range, whether they include
text or numbers. The COUNTBLANK function counts the number of blank
cells in a range.
• use Delta Co. Employees List file.
• In B12, Type =count(B1:B11), then Press Enter.
• In B13, Type =counta(B1:B11), then Press Enter.
• In B14, Type =countblank(B1:B11), then Press Enter.

You might also like