Arithmetic Operators

Excel uses the standard arithmetic operators and follows the order of operations as described below:

Order Arithmetic Operator Details

First Parentheses () Computations enclosed in parentheses are performed first.
Second Exponents ^ Computations involving exponents are performed next.
Third Multiplication and Division * and / Computations with multiplication and division are performed in the order in which they occur from left to right.
Last Addition and Subtraction + and - Computations with addition and subtraction are performed in the order in which they occur from left to right.

+ Addition

- Subtration

* Multiplication

/ Division

% Percent

^ Exponents

Length
20
10
16
17
15

Example
Serial No.
Formula 1
Area of Circle=pi *(Radiaus)2 2
3
4

Discount rate is 20 % Please fillup the table by using different formulas and functions

Example
Name of Items Price
Fridge PKR 18,500.00
TV PKR 7,000.00
AC PKR 29,000.00
Computer PKR 40,000.00

Example

Roll No
 4 100 0.2

(2+2*(8/2)) 10
(2+2*3) 8
4 10

With Cell Reference

Width Area Area
10 140 200 140 c21*d21

23 322 230 140

15 240 240 140

16 224 272 140

21 315 315 140

3.141593

Radius Area of Circle Formula

2 12.56 12.56637 12.56 3.14*(D30)^2
3 12.56 28.27433 28.274 PI()*(D30)^2
4 12.56 50.26548
5 12.56 78.53982

6 12.56 113.0973 3.14159265359

12.56
PKR 3,700.00 12.5663706144
12.5663706144
Discount Amount DUE
PKR 3,700.00 PKR 14,800.00 PKR 3,700.00 PKR 14,800.00
PKR 1,400.00 PKR 5,600.00 C39*20%
PKR 5,800.00 PKR 23,200.00
PKR 8,000.00 PKR 32,000.00

Lecture Lecture
Name Delivered Attended Percentage
127 119 93.700787402 94.44444444444 93.70079 F47/E47*100
106 99 93.396226415 94.44444444444 93.39623
115 105 91.304347826 94.44444444444 91.30435
124 112 90.322580645 94.44444444444 90.32258
102 100 98.039215686 94.44444444444 98.03922
 Functions Formula
IS predefined Facility Defined by User 10
Written in Predefined syntax is according to user requirements 20
May require parameters Doesnot require parameters 20
Identified by name has no particular name 20
All functions are formulas All formulas are not functions 60
70 70
H5+H6+H7+H8

1 Sum Function 2 PRODUCT Function

SUM(Num1,Num2
Sum(Cell 1 :Cell10 SUM(Num1,Num2 PRODUCT(Num1,Num2
and Sum(Cell 1 :Cell1 PRODUCT(Cell 1 :Cell10
Sum(Cell1,Cell2,Cell3………
and 20 and
Sum(Cell1,Cell2,Cell3……….. 1200 PRODUCT(Cell1,Cell2,Cell3………..
A

740 4 20 20 435 55 1 12 222

1616 1 1 15 7 45 2 1200 6660
1616 2 2 63 87 98 3 1200 4.987569E+47

263 3 3 36 65 999 4 PRODUCT(k25,k26)

4 4 78 45 145 5 12
5 5 12 452 632 10
10 10 45 105 185 1200 PRODUCT(K23:K28)
25 25

Maximum Function POWER Function

MAX(G23:G29) 78 POWER(Number, Power)

MAX(G23,G24,G25,G26,G27,G28,G29) 78 POWER(2,2)
4
Minimum Function POWER(E37,2)
MIN(G23:G29) 12 144
MIN(G23,G24,G25,G26,G27,G28,G29) 12

Average Function

Average(Num1,Num2,……..) 4.166667
Average(Cell1:C20) 4.166667

SQRT Function 3 SQRT(9)

SQRT(NUMBER) 3.4641 SQRT(E38)

Example

Number Square Cube Square root

4 16 4096 2
9 3
16 4
25 5
MAX Value 5
Min Value 2

Concatenate Function
Join Two or More Strings

CONCATENATE(A1,A2)

Alpha
bet
Alphabet

Fact Function
Returns the factorial of a number.

Syntax 3 6
Fact(Number) 5 120
0 1
4 24
Countblack Function Tech on the net 1
32
countblank(a1:a10)
123abc

Lower Function

Lower(Text)
NOUMAN nouman
Upper Function

Upper(Text)
nouman NOUMAN

MOD Function

MOD Function returns the remainder 20 12 mod(12,2) 0 12

after a number is divided by a divisor 8 2 mod(e98,e99) 0 2 2

2 20 2 20
Mod(number, divisor) 3 3

2 0
MEDIAN function

Returns the median of the given numbers.

The median is the number in the middle
of a set of numbers.

Syntax 1 2
MEDIAN(number1,number2,...) 2 4
3 6
4 8
5 10
6 12
above 3 8 3.5 7
Today Function
Returns current system date
Today() 7/15/2025

7/15/2025
Now Function
Returns current system date and time
7/15/2025 10:38
Now()

7/15/2025 10:38

ABS Function
The Excel ABS function returns the absolute value (ie. the modulus) of any supplied number.
ABS(Number)

ABS( -1 ) 1 36
ABS( -6.5 ) 6.5 2
ABS( 6.5 ) 6.5
ABS( 0 ) 0
ABS( 2 - 4.5 ) 2.5
 Medical Conveyance Total Income
Sr. No. Name Basic Pay Allowance House Rent Allowance Salary Tax Net Salary
1 A 10600 4240 4770 1590 21200 1272 19928
2 B 15000 6000 6750 2250 30000 1800 28200
3 C 16690 6676 7510.5 2503.5 33380 2002.8 31377.2
4 D 13625 5450 6131.25 2043.75 27250 1635 25615
5 E 22895 9158 10302.75 3434.25 45790 2747.4 43042.6

Note:
Medical 40% of basic bay
House Ren45% of basic pay
Con-Allowance 15% of the basic pay
Income Tax 6%
10 2 20 20 20
20 4 40 40 80
30 6 60 60 180
40 7 70 70 280
 COUNTIF function
The Countif function counts the number of cell in a range, that meets a given criteria.

Syntax

COUNTIF(range, criteria)

Data Data
apples 32 3 2
oranges 54 2
peaches 75 3
oranges 32
apples 86
oranges 22
Formula
COUNTIF(C14:C17,"apples") 2

COUNTIF(C14:C17,"oranges") 2
year data

YEAR DATA
1999 10.5 5 COUNTIF(C27:C32,">=2001")
1998 2
2000 7.2
2001 200 2 COUNTIF(C27:C32,"<=2000")
2002 5.4

2003 6.1
2005 7
2004 9
SUMIF function
The Sumif functiom adds all the numbers in a range of cells , based on a
given criteria.

Syntax

SUMIF(range, criteria, [sum_range])

product cost
2013 Oranges
2012 Bananas
2012 Apples
2013 Bananas
2013 Oranges
2011 Apples
2013 Pears
2009 Oranges

Date Region Units Total Amnt

$490,688.00 24/06/2012 North 186 $50,592.00

1/6/2012 East 356 $96,832.00
9/9/2012 West 907 $246,704.00 $490,688.00

$428,128.00 26/06/2012 South 190 $51,680.00 $490,688.00

22/04/2012 North 717 $195,024.00
$246,704.00 22/03/2012 West 550 $149,600.00
$149,600.00 19/12/2011 East 942 $256,224.00
$31,824.00 31/10/2011 North 901 $245,072.00
$428,128.00 2/10/2011 West 117 $31,824.00

490688
SUMIF(K22:K30,"North",M22:M30)

$490,688.00
Range
SUMIF(K22:K30) SUMIF(K26:K34,"south",M26:M34)
51680
In the 'Range' argument select the cells that include your criteria.

Criteria
SUMIF(K22:K30,"North"

In the criteria argument enter your criteria i.e "North" Region.

Note: that writing "=North" is same as writing "North".

Sum_Range

SUMIF(K22:K30,"North",M22:M30)

In the Sum_Range argument select the cells that should

be added after evaluation the criteria.

Fimnd Total Amount earned from NorthRegion 490688

Date Units Total Amnt

24/06/2012 186 $50,592.00
1/6/2012 356 $96,832.00
9/9/2012 907 $246,704.00
26/06/2012 190 $51,680.00
22/04/2012 717 $195,024.00
22/03/2012 550 $149,600.00
19/12/2011 942 $256,224.00
31/10/2011 901 $245,072.00
2/10/2011 117 $31,824.00

Example 1: Suppose we have a table as below and now our goal is to find the total units sold in East Region

SUMIF(E66:E74,"East",F66:F74)

1298

Example 2: In the same table as above, write a formula to find the total amount earned before the date “01/01/2012”.

SUMIF(E66:E74,"<1/01/2012",F66:F74)

31824

SUMIF(D66:D74,">1/01/12",G66:G74)
246704
343536
Example 3: In this example we have a table of performance of some schools at local events.
Here our task is to find the total number of awards won by School C in all the events.
Number Sin Cos Tan Sqrt Factorial
13 0.420167 0.907447 0.463021133 3.605551 6227020800
10 -0.544021 -0.839072 0.648360827 3.162278 3628800
15 0.650288 -0.759688 -0.8559934 3.872983 1.30767E+12
30 -0.988032 0.154251 -6.4053312 5.477226 2.65253E+32
12 -0.536573 0.843854 -0.63585993 3.464102 479001600

6227020800

X Y X2 Y2 XY X2Y X2Y2
3.5 1.1 12.25 1.21 3.85 13.475 14.8225
4.3 1.2 18.49 1.44 5.16 22.188 26.6256
2.1 1.5 4.41 2.25 3.15 6.615 9.9225
3.3 1.7 10.89 2.89 5.61 18.513 31.4721
5.2 2.3 27.04 5.29 11.96 62.192 143.0416
Total 122.983 225.8843
Average
 IF function

Syntax
225 855
IF(logical_test, value_if_true, [value_if_false]) 630

Units Rate
Name Previous Units Current Units Unit Consumed Electricity Charges Surcharge 001-100 Rs.2.25 Per Unit

Ali 100 400 300

855 128.25 above 100 Rs. 3.15 Per Unit

Hasan 200 800 600

1800 270

Asad 100 400 300

855 128.25
Calculate 15% Surcharge of
electricity charges

Sulman 50 200 150

382.5 57.375

Uzair 350 400 50

112.5 16.875

IF(logical_test, value_if_true, [value_if_false])

300
IF(D10<=100,D10,100)*2.25+IF(D10>100,D10-100,0)*3.15

>=90 Very Good 90 Very Good

>=80 Good 80 Good
F 70 F
88 Good
65 F
 Rate
Rs.2.25 Per Unit Unit Consu Electricity Charges Surcharge

Rs. 3.15 Per Unit 300 855 128.25

600 1800 270

te 15% Surcharge of
ectricity charges 300 855 128.25

150 382.5 57.375

50 112.5 16.875
 Test Score Grade Roll No Test Score Grade
>=90 A 1 90 A
80-89 B 2 82 B
70-79 C 3 75 C
60-69 D 4 68 D
50-59 Fail 5 55 Fail

IF(logical_test, value_if_true, [value_if_false])

IF(D11>=90,"A",IF(D11>=80,"B",IF(D11>=70,"C",IF(D11>=60,"D","Fail"))))
 Range Rate
1-100 2
101-200 7
201-300 9
301-400 10
>400 14

Name of C Consumed Units Bill in Reupees

Bilal 125 375
Saeed 202 918
Naeem 425 3150
Zeeshan 525 4550
Sarfraz 203 927 IF(C9<=100,C9*2,IF(C
200)*9+100*7+100*
400)*14+100*10+10
4550 125 375
202 918
425 3150
525 4550
203 927
IF(C9<=100,C9*2,IF(C9<=200,(C9-100)*7+100*2,IF(C9<=300,(C9-
200)*9+100*7+100*2,IF(C9<=400,(C9-300)*10+1E+102*7+100*2,IF(C9>400,(C9-
400)*14+100*10+100*9+100*7+100*2)))))
00,(C9-
 CR C R
10 2 20 20 20 20
20 4 80 40 80 40
30 6 180 60 180 60
40 7 280 70 280 70

Syntax

COUNTIF(range, criteria)

Data Data
apples 32
2
oranges 54
2
peaches 75
5
oranges 32
apples 86
oranges 22

YEAR DATA

1999 10.5 4
1998 2 5
2000 7.2 1
2001 200

2002 5.4

2003 6.1
2005 7

2004 9
<32 1
32 2
SUMThe Sumif functiom adds all given criteria.
Syntax
SUMIF(range, criteria, [sum_range])

Test Sc Grade
>=90 A
80-89 B
70-79 C
60-69 D
50-59 Fail
IF(logical_test, valu
range])

Date Region Units Total Amnt Total Amount

24/06/201North 186 $50,592.00 $490,688.00
1/6/2012 East 356 $96,832.00 $353,056.00
9/9/2012 West 907 $246,704.00 1,574
26/06/201South 190 $51,680.00
22/04/201North 717 $195,024.00
22/03/201West 550 $149,600.00
19/12/201East 942 $256,224.00
31/10/201North 901 $245,072.00
### West 117 $31,824.00

$490,688.00 1574
$353,056.00

Roll No Test Score Grade

1 90 A
2 82 B

3 75 C

4 68 D

5 55 Fail
l_test, value_if_true, [value_if_false])
se])