Microsoft Excel Notes (Formula)



There are nine (9) formula types in Ms Excel which are below.

Cell Stander Bar Title bar Formula Bar Format bar

Columns Menu bar Column Headers

Rows

Row Headers Workbook

Sheets

Addition Subtraction Multiplication Division

Explanation of formula

Formula means method or solution there are over 250 formulas in Ms Excel and every formula can apply on different place and different types. Here we will show you how to apply a formula? And which formula do which work? But always remember in start of every formula we must put equal sign (=) without this sign formula never work.

SUM formula is used to sum or add the numbers which you want.

=SUM(C2:C5)

We use (-) to subtract the numbers. Example is given below. (Minus Numbers)

=C7-C8

To multiply numbers we use (*) asterisk or star sign. Example is given below.

=B4*C4

We use Forward Slash (/) sign for division. We put division sign between two numbers or cells which we want to divide them. =B4/30

Note: A monthly Salary of a employee is 5000 so how much he get daily pay. So in C4 we enter =B4/30 because there are 30 days in a month so he get daily 166 rupees. If you want to know hourly pay too so divide the daily pay on hours e.g. =C4/8

Count formula is used to count those cells which contain numbers or digit only.

=COUNT(B2:B7)

Note: With count formula we just count number or digit cells. Not count text cells and also we put brackets in count formula. Between the brackets we give the range cells which we want to count. e.g. =COUNT(B2:B7)

COUNT A Formula is used to count those cells which contain digit, numbers and text also.

=COUNTA(D2:D7)

Note: COUNTA Formula is used to count those cells which contain digit and text both which are in range. In the above example we see there are 4 people who get salary but 2 persons are absent which is text so the result is 6 because we enter the count A formula. If you enter only count formula then the absent persons cold not count. Between the brackets we put the range which we want to count.

COUNTBLANK formula is used to count blank cells in range. This formula is count only blank

Cells which have no number and text. =COUNTBLANK(D2:D7)

Note: We are looking in D9 the result is 2 because there is only two cells which are blank means those person which salary is not paid. We can use it for much purpose. But always remember we put the range of our data between the brackets.

IF formula is used often time and for many purposes. For example we want if the student marks is greater than 40% pass him but if a student number is less than 40% failed him. So we use this formula here. Example is given below. =IF(H7>40,”Pass”,”Fail”)

Note: Remember this sign (>) is used for greater than this sign ( than 80% so give him A+ Grade, if per% is > than 70% give him A Grade, If per% is > than 60% give him B Grade, if per% is > than 50% give him C Grade, if per% is > than 40% give him D Grade other wise fail him.

=IF(H3>=80,"A+",IF(H3>=70,"A",IF(H3>=60,"B",IF(H3>=50,"C",IF(H3>=40,"D","Fail")))))

AVERAGE formula is used to find out the average of result, salary or it is used for other purposes too. Here we use it in a result which is below.

=AVERAGE(B2:B7)

For entering date automatically we use TODAY formula. When we enter this formula the date which is your system today’s date will enter automatically.

=TODAY()

In Ms Excel for time we use =NOW() formula which give us the current time i.e. your system current time. When you use this formula in a cell a system current time will shown automatically. =NOW()

We use & formula in deferent place for deferent purposes. We can join tow cells with & formula we can put our own words between the double quote (“ “) for example the total expense are 70000 so we can use here & formula when the expense increase or decrease always put Rupees.

We can also use & formula to join two cells text or digit e.g. =C8&”Rupees” OR =A2&B2

OR

YEAR formula is used to get or show you just the year from the date. For example your date of birth is 05/03/1986 so how much year you old are? We use here YEAR formula.

=YEAR(D14)

Note: The years of your age are 25.

Month Formula is used to get or shows just the month of the date. This show you your months that how many months you old are?

=MONTH(D14)

Note: The Month of your age are 7 and years are 25.

DAY formula is get or shows you just the day of the date.

=DAY(D14)

Note: Days in your age are 25 years are 25 and months are 7.

DAYS360 formula is used to count days between two dates, for example your date of birth is 5/3/1986 we use this formula on you date of birth and todays and the result will shows us that how many days are in your age. =DAYS360(E12,C12)

Example

Note: The days in your age are 9203 days.

This formula returns the sum of the products of corresponding range or arrays or used to calculate the total values of working days.

=SUMPRODUCT({6,10,5,20,3,40},{100,100,200,200,300,300})

Note: It will not give us result if we put the cells range.

This formula is used to sum the values which are laying in one list and subtract other values from their total as well as bellow.

=SUM(C2:C5)-D3

Note: This formula adds the salary first and then subtracts the paid balance.

This formula is used for multiplication. We can multiply two digits with each other by using this formula. =PRODUCT(B3,D3)

This formula is used to indicate the maximum and minimum value in one cell.

=MAX(B2:B8)&MIN(B2:B8)

Note: In B9 cell is shows us the max and min values.

This formula gives us a true result when condition is true and gives us false result when the condition is false. =AND(A1>B1)

When all condition are true it gives us true result if any condition or all conditions are false it gives false result. =AND(A2>=50,B2>=50,C2>=50)

OR formula gives us true result when a condition is true and gives us false result when a condition is false. If one condition is true it gives us true result.

=OR(A1>=50,B1>=50,C1>=50)

This formula gives us false result when condition is true and gives us true result when condition is false. It is an opposite of and formula. =NOT(A5=B5)

This formula is used to multiply the same two values by each other.

=POWER(B3,2)

Note: its means that the value which is in B3 cell multiplies by 5. Example: 5x5=25

This formula is used to return the factorial of a number. The factorial of a number is equal to 1*2*3*……*number =FACT(B3)

Note: its means that the value which is in D3 cell is the factorial of B3 cell as bellow.

Example: x2x3x4x5=120 or 1x2x3x4=24

This formula is used to change negative number or digit to positive digits.

=ABS(B3)

Negative Positive

Hour formula is used to get the current hour from system time or written time only.

=HOUR(D3)

Minute formula is used to get the current minute or time from written time only.

=MINUTE(D3)

Second formula is used to get the second from the time which is you written or given.

=SECOND(D3)

This formula is used to convert small letters or text to capital letters.

=UPPER(B3)

This formula is used to convert the capital letters to small letter.

=LOWER(B3)

PROPER formula is used to make capital the first character of the word or sentence.

=PROPER(B3)

This formula is used to show you the standard numeric code (ASCI Code) for characters as bellow. =CODE(“A”)

This formula is opposite of CODE formula which show us the letter of standard numeric code as below. =CHAR(65)

REPT formula is used to repeat one letter several times.

=REPT(B2,5)

This formula used to the compares two word that they are same or not if they are same the result will be true. =EXACT(A2,A6)

This formula is used to remove extra space between the words.

=TRIM(B2)

This formula is used to put dollar sign with a value.

=DOLLAR(B2)

This formula is used to find the position of a character in a text of sentence.

=FIND(“p”B3,2)

T formula is used to create a linked copy of text into another cell. Remember when we make any kind of change in original text the liked copy will change automatically.

=T(B3)

Note: The T formula is only applied on text only not on numbers.

N formula is used to create a lined copy of number into another cell. Remember when we make any change in the original number the linked cell will change automatically.

=N(B3)

This formula is used to join two cells word in one cell.

=CONCATENATE(B2,C2)

Note: The word which is located in D5 cell is the combination of these two cells. (B2,C2)

This formula is used to count the character of the word or sentence.

=LEN(B2)

Note: The word which is located in B2 cell has 8 characters.

This formula is used to find out the remainder value after division.

=MOD(B2,3)

Note: The value which is located in B2 cell is divided by 3 (Divisor) and the remainder after division is 2 which is in D3 cell.

This formula is used to return the nearest ODD integer.

=ODD(B2)

Note: The value which is located in D4 cell is the nearest odd value for 1.5 (B2) cell.

This formula is used to return the nearest EVEN integer.

=EVEN(B2)

Note: The value which is located in D4 cell is the nearest even value for 1.5 (B2) cell.

This formula is used to increase one number before decimal if the after decimal is greater then or equal to 5. =ROUND(B2,0)

Note: The value which is in C4 cell is increased one number from B3 cell, because the number which is after decimal in B2 cell is greater then 5.

This formula is used to increase one number before decimal if the after decimal is less then or greater then 5. =ROUNDUP(B2,0)

Note: The value which is in C4 cell is increased one number from B3 cell, because the number which is after decimal in B2 cell is less then 5.

This formula is used to find out the square of the value.

=SQRT(B3)

This formula is used to get only right side words from a sentence or name i.e. last name.

=RIGHT(B3,6)

Note: its first we put the cell which we

Want and then the comma after comma

We give the number of words.

LEFT formula is opposite of right formula. It gives us the left side i.e. (First name) of a name or sentence. First we enter the formula and then the character numbers.

=LEFT(B3,4)

Note: After B3 cell we put comma and then the strength of character which is 4 its means I just want to get 4 characters from this name form left side.

This formula is used to get the center or mid word or characters of a name or sentence i.e. middle name. Which is bellow. =MID(B3,5,6)

Note: In this formula we get the mid name we enter the B3 cell and then comma after comma we enter 5 its means start from left after five words and then enter the numbers of words which we required. (6)

This formula is used to know what is the day of the current or the written number. 1 is for Sunday, 2 for Monday , 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday and 7 is for Saturday. =WEEKDAY(B2)

Note: in B4 cell 2 is shows us that the day is Monday.

DCOUNT formula is used in Excel Database. This formula is used to count same number, digit or text in database. =DCOUNT(A1:D12,C1,G1:J2)

Note: The value which is in G5 cell is the count of doctors in B Column.

Any name or positions you want to count if just type it in G2, H2, I2 or J2 cell which is in right side shown on top.

DSUM formula is also used in Excel database which sum the same name, number or digit in database. =DSUM(A1:D12,C1,G1:J2)

Note: The value which is in G5 cell is the sum of doctor’s salaries.

Any other salary you want to sum just enter the name or post in the G2 or H2 cell which is located in right side of the page on top.

This is the most important formula of database function which is used often time in data base.

=DGET(A1:D12,”Post”,G1”G2)

Note: this formula works like Google internet search engine. When ever you put any name or any part of name in the G2 cell which is lactated on right side of the page the post, salary and contact no will shown automatically. Just changing name in the G2 cell all the required data of the person which you type their name shows automatically. You can also apply it on deferent place and different type.

Example:

Note: When we put the CNIC No in the C6 cell the person Name, Father Name, Contact No, Agency, Caste, Address and his ration Yes, No is shows automatically.

This formula is used to count the same word in database. When we put the post in H2 i.e. Doctor so the total number of this post shows us. =DCOUNTA(A1:D12,B1,G1”J2)

This a database formula which shows us the Maximum salary of the given same name or post

Which in shows bellow in H2. =DMAX(A1:D12,”Salary”,G1”J2)

DMIN is a database formula which shows us the MINIMUM salary of any same name or post which is given bellow. =DMIN(A1:D12,”Salary”,G1”J2)

DAVERAGE is a database formula which gives us the average of employ or employees.

=DAVERAGE(A1:C13,Salary”,F1:H2)

SUMIF is used to for many purposes we can use it here for Addition its means if the value is greater then or less then or equal so then sum it. =SUMIF(A23:A26,”>10000”,B23:B26)

Vlookup is the most important and powerful formula of Ms Excel. In bellow example if we change the name of A2 cell for example we give here Sun so the next cell which is B2 automatically changed into Mon similarly the next all cells will change there arranged days name because on all of the cells applied the same formula but only the A2 will change to B2, B3, B4 ...

Validation box is used to create a drop down box of selected list simply open a workbook in Ms Excel go to Data menu then Validation then validation criteria and select “List” source the list and then select the list and ok your drop down box ready to use. As bellow.

Vlookup formula is used to look a value or list in other cells which I want to show you bellow.

Enter the vlookup formula then put the brackets and select per% cell then comma then select the Array list which is K and L columns and then put the column Index no which is 2 because there are two columns array and we put the Dollar sign between the K$1 cell its necessary to use this formula to all of below cells.

In the bellow we have applied which is Addition, Subtraction, Multiplication, Division, CountA, Count, Count Blank, Maximum and Minimum.

Email us:

afridispk@

Or visit our website

afridispk.

Also available on

afridispk.

To get more notes on Ms Excel and also the tutorial of all these formula and much more

Formulas contact with us…. Afridispk@ all the above database and much

more are available on our website keep remember me in your prayers. Hope all of you

will get much help from these notes. Auto CAD, notes are also available on the web with

2D and full 3D commands and very soon Ms Word, Ms Power Point and Ms Access note

coming on the web.

In the name of my dear son. Haris Afridi

-----------------------

Logical

Formula

Text

Formula

Data Base

Formula

Lookup Reference

Formula

Statistical

Formula

Math & Trig

Formula

Date & Time

Formula

Financial

Formula

Ms Excel

Formulas

Addition + (Plus sign) =A2+B2

Multiplication * (Asterisk) =A2*B2

Subtraction - (Minus sign) =A2-B2

Division / (Forward Slash sign) =A2/B2

Percentage % (Percentage sign) =A2*(5+20%)

Information

Formula

SUM Formula (+)

Subtraction Formula (-)

Multiplication Formula (*)

Division Formula (/)

COUNT Formula

COUNT A Formula

COUNTBLANK Formula

IF Formula

COUNT IF Formula

MAX Formula

MIN Formula

PERCENTAGE (%) Formula

GRADE Formula

AVERAGE Formula

DATE Formula

TIME Formula

& Formula

YEAR Formula

MONTH Formula

DAY Formula

DAYS360 Formula

SUM PRODUCT Formula

SUM with Subtraction Formula

PRODUCT Formula

MAX & MIN Formula

AND Formula

COMMA AND Formula

OR Formula

NOT Formula

POWER Formula

FACT Formula

ABS Formula

HOUR Formula

MINUTE Formula

SECOND Formula

UPPER Formula

LOWER Formula

PROPER Formula

CODE Formula

CHAR Formula

REPT Formula

DOLLAR Formula

FIND Formula

T Formula

TRIM Formula

EXACT Formula

N Formula

CONCATENATE Formula

LEN Formula

MOD Formula

ODD Formula

EVEN Formula

ROUND Formula

ROUND UP Formula

SQRT Formula

RIGHT Formula

LEFT Formula

MID Formula

WEEKDAY Formula

DCOUNT Formula

DSUM Formula

DGET Formula

DCOUNTA Formula

DMAX Formula

DMIN Formula

DAVERAGE Formula

SUMIF Formula

VLOOKUP Formula

VALIDATION Formula

VLOOKUP in GRADE Formula

Multiple Formulas Applied

BUDGET PLANNER (Simple)

PROPERTY DEALER (Simple)

INVOICE (Simple)

TIME CARD (Simple)

LOAN PAYMENT CALCULATOR (Simple)

If have any question?

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download