- 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) |
