Functions:

Are specific block of words following with an equal sign (=) performing a specific and
particular task. They are as follows:
Sum:
For finding the sum of a range of numbers the following ways may help you do it easier
and faster which will be 100% accurate and exact.
1. =sum(select the cells to be added) Press enter or return key.
For example: =sum(C3:C10) enter.
2. =cell+cell+cell+cell+cell…. Press enter or return key.
3. A shortcut is also available in the standard menu i.e. Auto sum.
4. You can also access through the shortcut which is Alt + =.

Count:
This is a function used to count the cells whether how many numbers are there in
the table selected o how many text cells are there in the table or how many of those cells
are blank in the table. It can be done through the following ways:
5. =count( Select the table field of numbers only) press enter or
return key.
This above function is used to count the number of cells occupied by the numbers
of the table.
6. =counta( Select the whole table) press enter or the return
key.
This above function is used to count the number of all the cells occupied by the table
field.
7. =countblank(Select the whole table) press enter or the return
key.
The above function is used to find out the number of the blank cells in the table field.
Condition Class Funtions:
Count:
8. =countif(Select the whole table,”the specific number to be
counted”) press enter or the return key.
This command enables you to count the cells contain the specific number that you have
typed in the function. As follow:

Prepared by: Arafat Aryan. 1

 It can clearly be seen in the example above that in the function we have asked that how
many 456 are there in the cells and in the second diagram in the last cell it shows 4 which
is the answer or result of the function and it can also be understood by seeing the table.
9. =countif(select the table range,”<a specific number”) press
enter or the return key.
This function counts the number of the cells which consists the numbers smaller than
the specific number given by the user and it can also be used in this fashion to count the
number of the cells which contain the number it self and smaller than it then we will have
such a function
10. =countif(select the table range,”<=a specific number”)
press enter or the return key.
As the below example:

In the above example we have asked to show us the number of the cells which
contain the values smaller than 456 and which is 3.
11. =countif(Select the range of the table,”>specific
number”) press enter or the return key.
This function counts the number of the cells which consists of the numbers
which are greater than the specific number given by the user. And we include our specific
number also we would get the function in this way
12. =countif(Select the range of the table,”>=specific
number”) press enter or the return key.
Sum:
13. =sumif(select the range of the table,”specific number”)
press enter or the return key.
This function is used to find the sum of the specific number given by the user. As the
example will be clear to you:

Prepared by: Arafat Aryan. 2

 We can see in the above example that we ask the function to just add the number
456 and we can see that in the second diagram that it has sum up that in the last cell of the
second diagram.
14. =sumif(select the range of the table,”<specific number”)
press enter or the return key.
This function finds the sum of the numbers smaller than our specific number or we can
also include the specific number given by the user as on the next page:

15. =sumif(select the range of the table,”<=specific

number”) press enter or the return key.
As the example below:

You can see in the above example that by the help of the above function we have
just added the number smaller than 65.
16. =sumif(select the range of the table,”>specific number”)
press enter or the return key.
This function finds the sum of the number greater than our given specific number. Or
if you want to include the specific number in it you will get the function as this
17. =sumif(select the range of the table,”<specific number”)
press enter or the return key. As the below example:
Example:

Prepared by: Arafat Aryan. 3

 You can see in the above example that it has only sum up the number which are
greater than 45.
Average:
18. =average(select the number) press enter key or the
return button.
This function finds the average of a block of numbers. As follows:

In the above example we can see how the function finds the average. The function
can also be done if the cells are selected one by one.
Logarithm:
19. =log(number) press enter.
This function finds out the logarithm of the given number. If we don’t give the
base it will find the logarithm from the base 10 but if we would like to give it will take the
function as on next page:

20. =log(number,base) press enter. As the following examples:

=log(100000)
5.
Or
=log(8,2)
3
Antilogarithm:
21. =log10(number) press enter.
This function finds out the antilogarithm of the given number. As the following
example:
=log10(2.4)
0.3802
Ln
22. =ln(number) press enter
For finding the ln of a number the above function can be useful.
Pi Value
23. =pi( ) press enter
For getting the value for pi which is 22/7 fractionally and in decimal it is 3.14 which can
be displayed by the above function in excel.
24. =sqrt(number) press enter

Prepared by: Arafat Aryan. 4

 The above function can be useful it you want to find a number’s square root.
For example:
4‘s square root is 2.
Trigonometrical Functions:
25. =sin(angle) press enter.
This function finds out the sine of a given angle. For example:
=sin(180)
-0.80115.
26. =cos(angle) press enter.
This function finds out the cosine of a given angle. For example:
=cos(90)
-0.44807.
27. =tan(angle) press enter.
This function finds out the tangent of the given angle. For example:
=tan(450)
0.935809.
28. =max(select table range) press enter.
This function finds out the maximum number between entire numbers of the field.
As the following example:

In the above example the last cell of the second table shows that 321 is the
maximum number of the table.
29. =min(select table range) press enter.
This function finds the minimum number of the table. As shown in the example:

In the above example the last cell of the second table shows that 38 is the minimum
number of the table.
30. =upper(text) press enter.
This function capitalizes the text given or selected in the bracket area. As in the
below example:

After pressing enter you will get the result like this:
31. =lower(text) press enter.

Prepared by: Arafat Aryan. 5

 This function changes the text from the capital form to the small text from as
shown in the example:

And the result will be:

32. =proper(text) press enter.
This function makes the text of our proper i.e. capitalization, punctuation etc. As
the below example:

As we can see it the first letter is no capital it will get captivated in the result

like this:
33. =concatenate(text1,text2) press enter.
This function removes the space of two cells and combines them into one cell or in
other word we can say it joins two cells. As follows:

The result will be:

We can also use the below function:

34. =concatenate(text1,” “,text2) press enter.

35. =replace(old text,start from, Remove number of

letter,”new text’) press enter.
This function replaces or edits one text to another one. As shown in the following
example:

In this above example we want to replace teacher word with the teaching word and
the result is:

Percentage:
To find the specific percentage of a number we can use the function which will be
like as shown below:
36. =number *(multiplied by) percent/100 press enter or the
return key.

Prepared by: Arafat Aryan. 6

 To make it more clear let’s take an example of it. Suppose we have the table of some
workers of a company and we are asked to find the 10% of their monthly salary, so it will
be done as the following way:
For finding the 10% of the salary which is 5000 we will multiply it with the fraction
10/100 it means 10% of the total which is 100% (5000). And after pressing the enter key
we will get the answer of one cell and to get the 10% of all the salaries select the columns
of the 10% of the salary titled column till the last worker and then by keeping the Ctrl key
press d i.e. Ctrl+d you will get the 10% of all the salaries given in the table as the following
picture shows:

Now the other function is to find out the specific percentage of a number and add it
with the number itself. The function for this task will be:
37. =number+number* (multiplied by) percent/100 press
enter key.
Let’s take an example. Ok for that we will take our pervious table of salaries and
perform the function on it as shown in the pictures below:

Ok so the function for this will

be salary + salary * 10/100 and
after pressing the enter key and
selecting all the cells of the
desired column the answer will
be as the figure below shows:

The next function is to decrease a specific percent of a number and view the remaining
number. The function for this will be:
38. =number – number * (multiplied by) percent/100 press
enter key,
Or =number + number * (multiplied by) – percent/100 press
enter key.
Now let’s have an example for it also which will be as the following figures:

Prepared by: Arafat Aryan. 7

 The function will be like the first figure and result will be as shown in the second figure on
next page:

The answer will be same if we use the second function of this type also do it with your
self. And now let’s do our task by giving some conditions to the function and tell it fulfill
our task after reading and taking decision about it. The function will now be:
39. =if(name of condition=”specific name of
condition”,number + number * (multiplied by) percent/100,
number)
This above function says that =if(the condition=”name of a specific condition”so
increase the number in the way to plus the percent of it with it, if this condition does not
goes according to a condition so then just print the number it self). To make this clear let’s
take the example of above table and use the above function as given below:

In this example we are saying that if any worker has the position of the Teacher so
increment his salary by 10% and if the condition is not true then print the exact salary for
the workers. As the answer will be:

As we can see that the name Shams whose position is Teacher his salary is incremented
by 10% of his salary as shown in the above figure (The red number). And as we can see
that all the other numbers which did not fit to the given condition have their own numbers.
We can also decrease the salary of a worker by using the above function and just a little
bit change will occur to it and it will be as below:
40. =if(name of condition=”name of specific
condition”,number – number * (multiplied by) percent/100
press enter key.
We can see that we have used the old decreased by percent function under a condition
which is to decrease the number by a percent or if not print the number it self. As this
below example will make it clear to you on next page:
In this example we want the function to decrease the salary of the worker who is a cook
by 10% of his original salary. This will be as following:

Prepared by: Arafat Aryan. 8

 And the result can be as below:

It can clearly be seen that the number in red color is decreased of 10% of its original
salary which is of name Safi and position holding cook. We can also do the both above
example in a single function i.e. we can use one function to fulfill two conditions for us as
in the below example it is clearly defined:
In this below example we want to decrease one worker’s salary by 10% who is a
coordinator and decrease other worker’s salary by 10% who is a manager and for this we
will get the function as the combination of the above two functions i.e.
41. =if(name of the condition=”name of the particular
condition”number +number *(multiplied by)
percent/100,if(name of the condition=”name of the particular
condition”number +-- number *(multiplied by)
percent/100,number or ….so on with many and many other
conditions following) press enter key.
The function for the example will be like below figure:

And after pressing the enter key we will get the following result:

In the above figure we can see that the conditions are accurately applied to the both
coordinator and manager and one is increased by 10% of his original salary and the second
one is in the same way decreased by 10% of his original salary. This example shows that in
one function we can use many and many conditions as done in the above example.

Prepared by: Arafat Aryan. 9

 The next function is to find out about a person’s specific day’s salary i.e. one day’s
salary, one week’s salary etc which can be done by the following ways:
42. =salary/ (Divided by 30) press enter
This
example shows that a man named Arafat who
is the director according to position and who
has the salary of 4500, after applying the
function you will get that his one day salary is
150 and so on of others, which will be achieved by press enter after writing the function as
it is shown on the next page which will clearly show what change has the function brought.

The result of the above example clears all the

story of the above function.
This coming function is to find out the salary
of a person in a week. The function for this
will be:

43. =salary/30 *7 press enter

This function finds out the salary of a person in a week. As in the example below:

And:
The other function is to find out the salary of a person in one hour. The function for
this will be:
44. =salary/30/number of hours press enter
This function can be clear to you by the following example:
In this example we have asked the
function to find out the salaries of the
following workers in an hour per day. As
follow:

The result will be:

The next function is to find out the salary according to the absentees done by the
workers.

The function for this will be:

Prepared by: Arafat Aryan. 10

 45. =salary – salary/30 * number of absentees done press
enter
This function can be used to decrease a workers salary by the number of the days he
has been out of his job. As the example below clears it:

And the result will be:

Date & Time Functions:

46. =now() press enter
This function prints the current date and time in the selected cell.
47. =datevalue(“Type the date according to which in
computer”) press enter
The function can find the day, month and year from a date value. The function
respectively for day, month and year will be:
48. =day(select the date value) press enter
49. =month(select the date value) press enter
50. = year(select the date value) press enter
In the same way time has these functions for itself which are as follows:
51. =timevalue(‘Type the time as in computer ”) press enter
Minute, second and hour can also be found out from the time value obtained from the
above function in the following ways:
52. =minute(select the time value) press enter
53. =second(select the time value) press enter
54. =hour(select the time value) press enter
The coming functions are used to do the same above task but of the current time and date
that are:
55. =day(now( )) press enter
56. =month(now( )) press enter
57. =year(now( )) press enter
58. =minute(now( )) press enter
59. =second(now( )) press enter
60. =hour(now( )) press enter
This above last function for hour shows the hour of current setup time on the
computer of 24 hours.

Prepared by: Arafat Aryan. 11

 Multi Condition Functions:
These coming functions are used to the function in which you want to give multiple
conditions to satisfy an item. As the function is:
61. =if(and(1st condition=”specific condition”,2nd
condition=”specific condition”) salary + -- salary *percent
/100) press enter
The example which can clear this function is as follows:
In this example we have
asked the function to
increase the salary of
those workers who are
driver of position and
having grade d by 10%
and if not preview their own salaries as it shows below:

You can clearly see that the salary of that

worker is increased by 10% in red color as
shown: The note about this above function that
the specific conditions name which are given
in the function should be of same person
whose salary should be increased by 10%.

The other function for the same purpose is as follows but the difference is that this
below function can take different specific conditions but the upper one couldn’t. This
above sentence means that it can perform the task on both the given conditions which is not
possible in the if(and statement but can easily be done in if(or statement. This function is as
follow:
62. =if(or(1st condition=”specific name of condition”,2nd
condition=”specific name of condition (can be
different)”),salary + -- salary * percent /100, salary) press
enter.
This above function can perform that task as explained above. The following example
will clear its task:

And the result will be:

Prepared by: Arafat Aryan. 12

 Profit & Loss Functions:
The function through with the profit and loss of something can be found are as
follow:
63. if(sale>purchase ,”Profit”) press enter
64. if(purchase>sale ,”Loss”) press enter
65. if(purchase= sale ,”Equal Balance”) press enter
And to find the above three things that are loss, profit and equality of loss and profit
at with the help of one function the below function can be useful for this:
66. if(purchase<sale ,”Profit”,if(purchase>sale ,”Loss”,
if(purchase=sale , ”Equal Balance”))) press enter.
The above functions will be clear after the below example:
For this purpose first find out the difference between the prices which can be done if you
minus the sales price from the purchased price and the result will be the difference of the
two rates. As below:

This above table and the rates in the result’s column clearly shows that the values in
positive are the profit and the negative ones are loss and the zero having cells are the equal
balance.
To find the loss, profit and equality use the functions as follows:

The cells which show false means that those are not profit may be loss or equality.

Prepared by: Arafat Aryan. 13

 The cells which show false in this table means that they are not loss maybe profit or
equality.

The cells which show false in this table means that there is no equality of balance but
maybe profit or loss.

This above table shows the entire caption for the exact equity i.e. profit, loss or
equality of balance. As it is shown in the last column of the above table. The below coming
functions show that the condition which we have given whether it is true or false. The
function can be:
67. =if(condition=”specific condition name”,2nd
condition=”specific 2nd condition name”) press enter.
This above function shows the status of our condition whether it is false or true as
shown in the below example:

This example shows that the worker

who is a driver and having the grade D is
true and the others which do not satisfy our
condition are false.

The below function coming up performs the average task in a different way as shown
below in the example:

And the function for this will be:

Prepared by: Arafat Aryan. 14

 68. =average(quiz1 & quiz2 range)+average(quiz3 & quiz4
range)+average(quiz5 & quiz6 range)+(TestA value+TestB
value+TestC value)*10/100 press enter
The next function is to decrease a number by a given percentage as the example
shows:

The function for this will be:

69. =fee – fee*scholarship/100% press enter
The next coming function is to calculate the amount of the salary of a person along
with his expenses given in percent.

The function for this will be:

70. =salary + -- salary *(Sum of expenses) press enter
To clear this above function let’s take an example as follow:

The next coming function is just what you have done in the above function but you just
have to minus the days of absentees of the employees from the salary. The function for this
will be:
71. =(salary + salary*(sum of expenses) – (salary +
salary*(sum of expenses)/30* number of absentees press
enter
The above function will be cleared after you see the below example:

72. =if(1st condition=”specific name of 1st condition”,2nd

condiion*percent/100,if(1st condition =”specific name of 1st
condition”,2nd condition*percent/100, and so on the after last
condition press enter.
This above function is used to decrease one person’s amount of money by the percent
given in the function. This is also a multi conditional function. This will be clear if you
peer the example on the next page:

Prepared by: Arafat Aryan. 15

 73. =if(condition=”Specific name of condition”,Start date+
number of days,if(condition=”specific name of condition”Start
date+number of days)) press enter
The above function can calculate the end date of an already started condition or task.
You can clearly get this if you practice the below example. And note when you apply the
formula after that you will get a number you will be shocked to see that actually it is the
date value that you can change from the format cells menu to the original type of it. Let’s
get to our example:

As you can see that the English class which is of two months duration is added to 60
and the Computer class which is of one month duration is added to 30 days respectively.
The next formula is to find out the duration of a given start and end date.

The function for this will be:

74. =days360(start date,end date) press enter
Look the below example it will get clear to you:

And you will get:

If you have a cell containing text having a great distance from each other you can
make them arrange properly through the below function:
75. = trim(cell or text) press enter
To round off a decimal number you can use the below functions:
76. =round(decimal number) press enter
77. =roundup(decimal number) press enter
78. =rounddown(decimal number) press enter
To check the value of the data you have entered in the cell you can use the following
functions:
For number checking:
79. =n(select cell) press enter
80. =isnumber(select cell) press enter
For text checking:
81. =t(select cell) press enter
82. =istext(select cell) press enter
For blank cell checking:

Prepared by: Arafat Aryan. 16

 83. =isblank(select cell) press enter
For logic checking but only can be applied on a cell containing the value TRUE or FALSE:
84. =islogical(select cell) press enter
To check for the values that are they exact or the same just same nothing in difference you
can use the following function:
85. =exact(Text1,Text2) press enter
To copy a text or number or to clean a number from the commas you can use the following
function:
86. =clean(value) press enter
Registry Functions:
87. =code(value) press enter
As we know that every character displayed on the screen of a monitor of a computer
has its own code that CPU knows it to know that you can use the above functions. And to
convert a code into its original form you can use the below function which is:
88. =char(value) press enter
The above two functions will get cleared to you after the below example:

And you will get: And the 2nd function: and

89. =power(number,power) press enter
You can find a number at a certain power by the help of the above function. For
example:
To find 2 powers 3 you can easily get 8 by help of the above function.

90. =len(value) press enter

This above function can be useful for finding the length of a text or number.
91. =product(select range) press enter
This above function is used to find the product of a range of numbers or two.
92. =roman(number) press enter
This function can be useful in the conversion of English digits into the Roman
Numbers.
93. =mode(Select range) press enter
This function can show you the number which is being repeated several times in the
data range.
94. =mod(number, divisor) press enter
This function is used to find the remaining number after the division of the given
number to its divisor.
95. =fact(number) press enter
This function can be useful for calculating the factorial of a given number.
96. =find(“text”,text within,to count from the number or
start counting from the number of the text with in the text)
press enter
This function is used to count the number as a position occupied by a letter. For
example if you want to find the letter “f” in the text “Arafat” So use the formula and you

Prepared by: Arafat Aryan. 17

 will get the result 4. You can also use the following functions in order to find from right or
from left. These are:
97. =left(“text”number text) press enter
98. =right(“text”, number text) press enter
Here are some more functions which can be useful:
99. =if(date=today(),”Last Day”) press enter
100. =if(date=today(),”over”) press enter
101. =if(date=today(),”not over”) press enter
102. =number * 100%/total press enter
103. =percent * profit/100%

Prepared by: Arafat Aryan. 18