Data Analytics Exam 2.1

Excel Formulas

HLOOKUP

Performs a horizontal lookup by searching for a value in the top row of the

table and returning the value in the same column based on the

index_number

INDEX

returns either the value or the reference to a value from a table or range

TRANSPOSE

Returns a transposed range of cells

VLOOKUP

Performs a vertical lookup by searching for a value in the first column

XLOOKUP

Performs a lookup (either vertical or horizontal)

CONCAT

used to join 2 or more strings together

FIND

Returns the location of a substring in a string (case-sensitive)

LEFT

Extract a substring from a string, starting from the left-most character

LEN

Returns the length of the specified string

LOWER

Converts all letters in the specified string to lowercase

MID

Extracts a substring from a string (starting at any position)

NUMBERVALUE

Returns a text to a number specifying the decimal and group separators

PROPER

Sets the first character in each word to uppercase and the rest to lowercase

REPLACE

Replaces a sequence of characters in a string with another set of characters

RIGHT

Extracts a substring from a string starting from the right most characters

SEARCH

Returns the location of a substring in a string

TRIM

Returns a text value with the leading and trailing spaces removed

UPPER

Convert text to all uppercase

VALUE

Converts a text value that represents a number to a number

DATE

Returns the serial date value for a date

DATEDIF

Returns the difference between two date values, based on the interval specified

DAY

Returns the day of the month (1 through 31) given a date value

DAYS

Returns the # of days between 2 dates

HOUR

Returns the hours (btw 1 and 23) from a time value

MINUTE

Returns the minutes (0 -59) from a time value

MONTH

Returns the month (1-12) given a date

NOW

Returns the current system date and time

SECOND

Returns the seconds (0-59) from a time value

TIME

Returns a decimal number given an hour, minute, and second value

TODAY

Returns the current system date

WEEKDAY

Returns a number representing the day of the week

WEEKNUM

Returns the week number for a date

YEAR

Returns a four digit year (1900 - 9999)

CEILING

Returns a number rounded up based on a multiple of significance

FLOOR

Returns a number rounded down based on a multiple of significance

INT

Returns the integer portion of a numberRA

RAND

Returns a random number greater than or equal to 0 and Less than 1

RANDBETWEEN

Returns a random number that is between a bottom and top range

ROUND

Returns a number rounded to a specified number of digits

ROUNDDOWN

Returns a number rounded down

ROUNDUP

Returns a number rounded up

SIGN

Returns the sign of a number

SUBTOTAL

Returns the subtotal of the numbers in a column in a list or database

SUM

Adds all numbers in a range of cells

SUMIF

Adds all numbers in a range of cells based on a criteria

SUMIFS

Adds all numbers in a range of cells, based on a single or multiple criteria

AVERAGE

Returns the average of the numbers provided

AVERAGEIF

Returns the average of all numbers in a range of cells, based on a given criteria

AVERAGEIFS

Returns the average of all numbers in a range of cells, based on multiple criteria

COUNT

Counts the number of cells that contain numbers as well as the number of arguments that contain number

COUNTBLANK

Counts the number of empty cells in a range

COUNTIF

Counts the number of cells in a range, that meets a given criteria

COUNTIFS

Counts the number of cells in a range , that meets a single or multiple criteria

MEDIAN

Returns the median of the numbers provided

MIN

Returns the smallest value from the numbers provided

RANK

Returns the rank of a number within a set of numbers

STDEV

Returns the standard deviation of a population based on a sample of numbers

AND

Returns TRUE if all conditions are TRUE

IF

Returns one value if the condition is TRUE or another value if the condition is FALSE

IFERROR

Used to return an alternative value oif a formula results in #N/A

IFS

Specify multiple IF conditions within 1 function

NOT

Returns the reversed logical value

OR

Returns TRUE if any of the conditions are TRUE

ISBLANK

Used to check for blank or null valuesS

ISNA

Used to check for NA error

ISNUMBER

Used to check for a numeric value

FV

Returns the future value of an investment

IPMT

Returns the interest payment for an investment

ISPMT

Returns the interest payment for an investment

NPER

Returns the number of periods for an investment

NPV

Returns the net present value of an investment

PMT

Returns the payment amount of an investment

PV

Returns the present value of an investment

RATE

Returns the interest Rate for an annuity