List of Basic Formulas used in Ms Excel
Name Formula Used for
Sum =Sum(b1:b10) To do the addition
Min =min(b1:b12) To find the minimum value out of range
Max =max(b1:b12) To find the maximum value out of range
Average =Average(b2:b12) To find the Average of range
Averageif =Average(A2:A7,2012, B2:B7) Returns the average (arithmetic mean) of all numbers in
a range of cells, based on a given criteria.
Concatenate =concatenate(B2,c2) To join two fields data together with a space or a
=b2&” “&C2 comma between
=concatenate(b2,”,”,c2)
Left =left(a2,1) Extract one character from the left most side
Right =right(a2,1) Extract the right most character from data
Mid =mid(a2,2,1) Extract the second character from the data and display
only one character
Length =len(a2) Count the number of characters in a2 cell
Round =round(a2,2) Round the contents of cell to two decimal places
Naming cell Select Cell Range and Name it Naming, or providing absolute or relative refrencing
or cell by writing name in Name Box
range
Count ifs =COUNTIFS(a2:A9,”=2013”)
RANDBETWEEN Count the number of cells in a range, that meets a single
RANDBETWEEN Or multiple criteria.
Count =count(a2:a10) Count the range from a2 to a10
Count if =COUNTif(a2:a10,”yes”) Count how many yes are available in range a2 to a10
CountA =COUNTA(a2:a10) Look at to range a2 to a10 the number of cells which
are not empty
Sumif =SUMIF(b2:b10,”wheat”,c2:c10) Sum if word wheat is available in range b2:b10 and
sum the prices from c2:c10
Vlookup = Vlookup (b2,code,2,false) Vertical look up to look up b2 value in code range and
displaying second column with exact match
Hlookup =Hlookup(b2,code,2,false) Horizontal lookup to look up b2 value in code range
displaying second row with exact match
LOOKUP =LOOKUP(10251, A1:A6, B1:B6) Returns a value from a range (one row or one column)
or from an array
If function =IF(b2>=10,”high price”,”low If statement based on condition, if condition is true
price”) high price will be displayed and if condition is false low
price will be displayed
Match =MATCH (K10,$N$6:$O$13,0) The Microsoft Excel MATCH function searches for a
value in an array and returns the relative position of
Month =month(b2) thatdate
On item.
it will return the month value
Day =day(b2) On date it will return the day value
Year =year(b2) On date it will return the year value
Page 1
Minute =minute(b2) On time it will return the minute values
Hour =hour(b2) On time it will return the hour value
Text =text((b2-b1),”h:mm”) Subtract two time values and display it in hour minute
format
Product =PRODUCT(A2:A4) or The PRODUCT function multiplies all the numbers given
=PRODUCT(A2:A4,2) as arguments and returns the product.
Index =INDEX($M$4:$Q$9,M12,N12) The Microsoft Excel INDEX function returns a value
in a table based on the intersection of a row and
column position within that table
Page 2
