MS Excel 2007 Formulas List

• Formulas list of Excel 2007?

MATHEMATICAL AND RELATIONAL OPERATORS
Operator	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division
%	Percentage
^	Powers and roots
=	Equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
< >	Not equal to

 

S. No	Functions	Description	Syntax	Example
a	Functions without arguments
1	Rand	Generates a random number between 0 and 1	=Rand()	=Rand()*3; (generate a random number between 0 and 3)
2	Pi	Generate the value of pi to 14 decminal place	=Pi()	=Pi()
b	Functions with 1 argument
1	Average	Produce the average of the data in a range of cells	=average(Cx:Cy)	=average(C1:C12)
2	Max	Produce the greatest value in a column of cells	=max(Cx:Cy)	=max(C1:C12)
3	Hour	Returns the number of hours past midnight for the specified time	=hour(Cx) =hour(time)	=hour(A34) =hour(1:35 PM)
4	Minute	Returns the number of minutes past the hour for the specified time	=minute (Cx) =minute (time)	=minute(A34) =minute(1:35 PM)
5	Sqrt	Produce the square root of its argument	=sqrt(Cx) =sqrt(number)	=sqrt(Cx) =sqrt(9)
c	Functions with 2 arguments
1	Round	Rounds a value to a specified digit to the left or right of the decimal point	=round(Cx, number) =round(value, number)	=round(A22, 2); (round to 2 decimal places) =round(123.45,0); (round to 0 decimal places)
2	Countif	Counts the number of cells in a range that meet a specified criteria	=countif(Cx:Cy, ">criteria")	=countif(C1:C12,">150")
d	Functions with 3 arguments
1	If	Provides the basis for a decision; if condition is met one answer is returned; if condition is not met, another answer is returned	=if(condition,"a nswer1","answer2)	=if(A1>0,"yes","no); if the value of A1 is positive, Excel returns the answer "yes": otherwise, Excel returns the answer "no"
2	Sumif	Produce the sum of the cells in a range if any cells in a second range meet a selection criterion	=sumif(Cx:Cy,c riterion,Dx:Dy) Cx:Cy: the range of cells to meet the criterion Dx:Dy: the range of cells from which sum will be calculated	=sumif(C1:C12, >150,D1:D12)
e	Functions with one or more arguments
1	And	Returns a logical TRUE response if all of its arguments are true; otherwise returns false	=and(condition1, condition, condition3….)	=and(A1>0,A2>1,A3>3)
2	Or	Returns a logical TRUE response if one are more arguments are true; otherwise returns false	=or(condition1, condition, condition3….)	=or(A1>70,A1<80)
3	Sum	Totals the data in a column of cells	=sum(Cx:Cy,Dx,Dy)	=sum(C1:C12)