Learning Resources
Working with Ranges
Working with Ranges
A selection consisting of two or more cells is a range. The cells in a range can be adjacent or non-adjacent. An range is given by start cell address and last cell address separated by ‘:’.
Names can also be given to ranges. Various category of functions are
- Mathematical Functions
- Statistical Functions
- Financial Functions
- Date and Time Functions
- Logical Functions
- String Functions
- Database and List Management Functions
- Lookup & Reference
|
FUNCTIONS |
Results |
|---|---|
|
ABS |
Returns the absolute value of a number |
|
COUNTIF |
Counts the number of non-blank cells with in a range |
|
FACT |
Returns the factorial of a number |
|
MOD |
Returns the remainder from division |
|
POWER |
Returns the result of a number raised to a power |
|
PRODUCT |
Multiplies the arguments |
|
QUOTIENT |
Returns the integer portion of a division |
|
RAND |
Returns a random number between 0 and 1 |
|
ROUND |
Rounds the number to a specified number of digits |
|
SQRT |
Returns a positive square root |
|
SUM |
Adds its arguments |
|
SUMIF |
Adds the cells specified by a given criteria |
|
FREQUENCY |
Returns a frequency distribution as a vertical array |
|
MAX |
Returns the maximum value in a list of arguments |
|
MIN |
Returns a minimum value in a list of arguments |
|
MODE |
Returns the most common value in a data set |
|
PERCENTILE |
Returns the Kth percentile of values in a range |
|
VAR |
Estimates variance based on a sample |
|
FV |
Returns the future value of an investment |
|
IPMT |
Returns the interest payment for an investment for a given period |
|
PMT |
Returns the periodic payment for an annuity |
|
PPMT |
Returns the payment on the principal for an investment for a given period |
|
PV |
Returns the present value of an investment |
|
RATE |
Returns the interest rate per period of an annuity |
|
DATE |
Returns the real number of a particular date |
|
DAY |
Converts a serial number to the day of the month |
|
NOW |
Returns the serial number of the current date and time |
|
YEAR |
Converts a serial number to a year |
|
AND |
Returns a true if all its arguments are true |
|
IF |
Specifies the logical test to perform |
|
NOT |
Reverses the logic if its arguments |
|
OR |
Returns true if any of the arguments is true |
|
CONCATENATE |
Joins the several text items into one text item |
|
PROPER |
Capitalizes the first letter in each word of a text value |
|
REPLACE |
Replaces characters within text |
|
TRIM |
Removes spaces from text |
|
UPPER |
Converts text to uppercase |
|
VALUE |
Converts a text argument to a number |
|
HLOOKUP |
Extract the database records row wise |
|
VLOOKUP |
Extracts the data base records column wise |