1 of 27

Session 03

Formulas in Excel

Muhammad Yasir

yasirbhutta@gmail.com

2 of 27

Formula

  • A formula is an equation that performs operations on a worksheet data

yasirbhutta@gmail.com

3 of 27

Structure of a Formula ….

​

    • All the formula have to begin with an equal sign [=]
    • The = sign is followed by the elements to be calculated [the operands]
    • Operands are separated by calculation operators

yasirbhutta@gmail.com

4 of 27

Structure of a Formula : Example

= A1 + A2

Operand

Operator

yasirbhutta@gmail.com

5 of 27

Operators

  • An operator specifies the type of calculation that you intend to perform on the elements of a formula
  • Excel offers three main types of operators –

​

    • Arithmetic- for basic mathematical operations
    • Comparison - compare two values
    • Reference -combine ranges of cells

yasirbhutta@gmail.com

6 of 27

Arithmetic Operator

OperationN

Operator

Example

Addition

+

=10+2

Subtraction

-

=6-2

Negation

-

=-9

Multiplication

*

=5*8

Division

/

=27/9

Percent

%

=550*3%

Exponentiation

^

=5^2 (same 5*5)

yasirbhutta@gmail.com

7 of 27

Comparison Operators

Operation

Operator

Example

Equal to

=

A1=A2

Greater than

>

A1>A2

Less than

<

A1<A2

Greater than or equal to

>=

A1>=A2

Less then or equal to

<=

A1<=A2

No equal to

<>

A1<>A2

yasirbhutta@gmail.com

8 of 27

Reference Operators

Operation

Operator

Example

Range operator

:

A1:A10

Union Operator

,

Sum(5,7,8)

yasirbhutta@gmail.com

9 of 27

Operand

  • A quantity upon which a mathematical operation is performed
    • A numerical value
    • A cell or range reference
    • A label
    • A name
    • A worksheet function

​

yasirbhutta@gmail.com

10 of 27

How Formulae work

  • The operations in a formula are performed from left to right – following the order of operator precedence

​

  • You can change the of precedence of operators by using parentheses ()

yasirbhutta@gmail.com

11 of 27

PEMDAS

  • Parentheses
  • Exponent
  • Multiply / Divide
  • Addition / Subtraction

​

yasirbhutta@gmail.com

12 of 27

Formula

=6/3-2+3*3

Answer = 9

yasirbhutta@gmail.com

13 of 27

Creating a Formula

  1. By placing an equal sign in the cell that is to hold the result and then pointing and clicking those cells that contain the operands
  2. By writing the cell addresses of cells that you want to use for your calculation in your result cell

​

yasirbhutta@gmail.com

14 of 27

yasirbhutta@gmail.com

15 of 27

Exercises

yasirbhutta@gmail.com

16 of 27

Add Two Numbers

Steps

  1. Enter 10 in cell A1
  2. Enter 20 in cell B1
  3. Write = sign in Cell C1
  4. Place mouse on cell A1
  5. Type + sign
  6. Go to cell B1
  7. Press Enter key

yasirbhutta@gmail.com

17 of 27

Subtracting Two Numbers

Steps

  1. Enter 60 in cell F6
  2. Enter 20 in cell G6
  3. Write = sign in Cell C1
  4. Place mouse on cell F6
  5. Type - sign
  6. Go to cell G6
  7. Press Enter key

yasirbhutta@gmail.com

18 of 27

Excel formula for Percent ...

=B5*C5

yasirbhutta@gmail.com

19 of 27

Excel formula for Percent

=100*20%

To get 20 percent of given number

20%= 0.2

yasirbhutta@gmail.com

20 of 27

Calculating 25 to the power 2

Steps

  1. Enter 25 in cell J43
  2. Enter 2 in cell K43
  3. Write = sign in Cell L43
  4. Type J43
  5. Then Type ^ sign
  6. Type K43
  7. Press Enter key

=J43^K43

yasirbhutta@gmail.com

21 of 27

Calculate Discount …

= (C3*C4)

yasirbhutta@gmail.com

22 of 27

Calculate Net Cost Price

= (C3-C5)

Net cost price= List price - Discount

yasirbhutta@gmail.com

23 of 27

Calculate salary in year 2

=C3+(C3*C4)

yasirbhutta@gmail.com

24 of 27

Use of Logical operator

yasirbhutta@gmail.com

25 of 27

Excel Help & Training

Overview of formulas in Excel

​

​

yasirbhutta@gmail.com

26 of 27

References

yasirbhutta@gmail.com

27 of 27

THANKS

yasirbhutta@gmail.com