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