[PDF] 400+ Excel Formulas List Excel Shortcut Keys PDF - Download Here PDF

Preview

Summary

Excel formula for computer. Learn to do work fast...

Description

(https://yodalearning.com/)



alearning.com/tutorials/excel-

excel(https://yodalearning.com/tutorials/category/excel/)

formulas-pdf/) alearning.com/tutorials/excel-

Excel Formulas & Functions in PDF nctions+in+PDF+List:https://yodalearning.com/tutorials/excelList (tg://msg? whatsapp://send? formulas-pdf/)

By Yoda Learning (https://yodalearning.com/tutorials/author/yoda/) formulas-pdf/) 26%23038%3B+Functions+in+PDF+List:

June 14, 2020

g.com/tutorials/excel-formulas-pdf/)  192096

 150

24 Mins read

(whatsapp://send? (tg://msg? (https://yodalearning.com/tutorials/excel(https://yodalearning.com/tutorials/excel  text=Excel+Formulas+%26%23038%3B+Functions+in+PD text=Excel+Formulas+%26%23038%3B+Functions+in+PDF+List:https://yodalearning  formulas-pdf/) formulas-pdf/) https://yodalearning.com/tutorials/excel-formulas-pdf/ formulas-pdf/)

Excel Formulas PDF and Functions: What do you Understand by theAdvanced Excel Formulas andBasic Excel Functions? This Blog will give you the Excel formulas PDF/list of the Key Functions of Excel. Excel Formulas PDF is a list of most useful or extensively used excel formulas in day to day working life with Excel. These formulas, we can use in Excel 2013. 2016 as well as 2019. The Excel Functions covered here are: VLOOKUP, INDEX, MATCH, RANK, AVERAGE, SMALL, LARGE, LOOKUP, ROUND, COUNTIFS, SUMIFS, FIND, DATE, and many more. Let’s dive right in… Compatibility Excel Formulas & Functions Cube Excel Formulas & Functions Database Excel Formulas & Functions Date & Time Excel Formulas & Functions Information Excel Formulas & Functions Logical Excel Formulas & Functions Lookup & Reference Excel Formulas & Functions Text Excel Formulas & Functions Most Common Excel Formulas & Functions Engineering Excel Formulas & Functions Financial Excel Formulas & Functions Math & Trigonometry Excel Formulas & Functions Statistical Excel Formulas & Functions Must Check: 249+ Excel Shortcuts PDF to Work Faster (https://yodalearning.com/tutorials/ms-excel-shortcut-keys-forbeginner/)

CLICK HERE TO DOWNLOAD EXCEL FORMULAS PDF LIST  (http://[thrive_2step%20id='36567'%5D%20%5B/thrive_2step%5D)

Excel Formulas PDF List Compatibility Excel Formulas & Functions Functions

Excel Formulas

Description Joins several text items into one text

CONCATENATE

=CONCATENATE(text1,text2,…)

item. Easier to use ‘&’ instead of the function usually.

FLOOR

BINOMDIST

CHIDIST

=FLOOR(number,signicance)

Returns the individual term binomial =BINOMDIST(number_s,trials,probability_s,cumulative) distribution probability

=CHIDIST(x,deg_freedom)

CHIINV

=CHITEST(actual_range,expected_range)

CONFIDENCE

=CONFIDENCE(alpha,standard_dev,size)

FTEST

=LOGINV(probability,mean,standard_dev)

LOGNORMDIST

=LOGNORMDIST(x,mean,standard_dev)

NORMDIST

NORMINV

Returns the one-tailed probability of the chi-squared distribution Returns the test for independence Returns the condence interval for a population mean

=FTEST(array1,array2)

LOGINV

MODE

Rounds a number down, toward zero

==MODE(number1,number2,…)

 Returns the inverse of the lognormal cumulative distribution Returns the cumulative lognormal distribution Returns the most common value in a data set

=NORMDIST(x,mean,standard_dev,cumulative)

=NORMINV(probability,mean,standard_dev)

NORMSDIST

=NORMSDIST(z)

NORMSINV

=NORMSINV(probability)

PERCENTILE

=PERCENTILE(array,k)

Returns the normal cumulative distribution

Returns the inverse of the normal cumulative distribution Returns the standard normal cumulative distribution Returns the inverse of the standard normal cumulative distribution Returns the k-th percentile of values in a range

PERCENTRANK

=PERCENTRANK(array,x,signicance)

Returns the percentage rank of a value in a data set

POISSON

=POISSON(x,mean,cumulative)

Returns the Poisson distribution

QUARTILE

=QUARTILE(array,quart )

Returns the quartile of a data set

RANK

=RANK(number,ref,order)

STDEV

=STDEV(number1,number2,…)

STDEVP

=STDEVP(number1,number2,…)

TDIST

=TDIST(x,deg_freedom,tails)

TINV

=TINV(probability,deg_freedom)

VAR

=VAR(number1,number2,…)

VARP

=VARP(number1,number2,…)

FINV

Returns the rank of a number in a list of numbers Estimates standard deviation based on a sample Calculates standard deviation based on the entire population Returns the Student’s t-distribution Returns the inverse of the Student’s t-distribution Estimates variance based on a sample Calculates variance based on the entire population

=FINV(probability,deg_freedom1,deg_freedom2)

FORECAST

=FORECAST(x,known_y’s,known_x’s)

BETADIST

=BETADIST(x,alpha,beta,A,B)

Returns the inverse of the F probability distribution

Returns a value along a linear trend Returns the beta cumulative distribution function Returns the inverse of the cumulative

BETAINV

=BETAINV(probability,alpha,beta,A,B)

distribution function for a specied beta distribution

COVAR

=COVAR(array1,array2)

Returns covariance, the average of the products of paired deviations Returns the smallest value for which

CRITBINOM

=CRITBINOM(trials,probability_s,alpha)

the cumulative binomial distribution is less than or equal to a criterion value

EXPONDIST

=EXPONDIST(x,lambda,cumulative)

Returns the exponential distribution

POISSON

=POISSON(x,mean,cumulative)

Returns the Poisson distribution

FDIST

=FDIST(x,deg_freedom1,deg_freedom2)

Returns the F probability distribution

GAMMADIST

=GAMMADIST(x,alpha,beta,cumulative)

Returns the gamma distribution

GAMMAINV

=GAMMAINV(probability,alpha,beta)

Returns the inverse of the gamma cumulative distribution

HYPGEOMDIST

Returns the hypergeometric =HYPGEOMDIST(sample_s,number_sample,population_s,number_pop) distribution

NEGBINOMDIST

Returns the negative binomial =NEGBINOMDIST(number_f,number_s,probability_s) distribution

TTEST

=TTEST(array1,array2,tails,type)

Returns the probability associated with a Student’s t-test Calculates variance based on the

WEIBULL

=WEIBULL(x,alpha,beta,cumulative)

entire population, including numbers, text, and logical values

ZTEST

=ZTEST(array,x,sigma)

Returns the one-tailed probabilityvalue of a z-test

Cube Excel Formulas & Functions Functions

Excel Formulas

Description Returns a key performance indicator (KPI) name, property, and measure, and displays the name and property

CUBEKPIMEMBER

in the cell. A KPI is a quantiable =CUBEKPIMEMBER(connection,kpi_name,kpi_property,caption) measurement, such as monthly gross prot or quarterly employee turnover, used to monitor an organization’s performance. RReturns a member or tuple in a cube

CUBEMEMBER

=CUBEMEMBER(connection,member_expression,caption) hierarchy. Use to validate that the member or tuple exists in the cube. Returns the value of a member property in the cube. Use to validate

that a member name exists within the CUBEMEMBERPROPERTY =CUBEMEMBERPROPERTY(connection,member_expression,property) cube and to return the specied property for this member. Returns the nth, or ranked, member CUBERANKEDMEMBER

in a set. Use to return one or more =CUBERANKEDMEMBER(connection,set_expression,rank,caption) elements in a set, such as the top sales performer or top 10 students.

Denes a calculated set of members or tuples by sending a set expression CUBESET

=CUBESET(connection,set_expression,caption,sort_order,sort_by) to the cube on the server, which creates the set, and then returns that set to Microsoft Ofce Excel.

CUBESETCOUNT

CUBEVALUE

=CUBESETCOUNT(set)

Returns the number of items in a set.

Returns an aggregated value from a =CUBEVALUE(connection,member_expression1,…) cube

Database Excel Formulas & Functions Functions

Excel Formulas

Description Extracts from a database a single

DGET

=DGET(database,eld,criteria)

record that matches the specied criteria Adds the numbers in the eld column

DSUM

=DSUM(database,eld,criteria)

of records in the database that match the criteria

DAVERAGE

=DAVERAGE(database,eld,criteria)

DCOUNT

=DCOUNT(database,eld,criteria)

DCOUNTA

=DCOUNTA(database,eld,criteria)

DMAX

=DMAX(database,eld,criteria)

DMIN

=DMIN(database,eld,criteria)

Returns the average of selected database entries Counts the cells that contain numbers in a database Counts nonblank cells in a database Returns the maximum value from selected database entries Returns the minimum value from selected database entries Multiplies the values in a particular

DPRODUCT

=DPRODUCT(database,eld,criteria)

eld of records that match the criteria in a database Estimates the standard deviation

DSTDEV

=DSTDEV(database,eld,criteria)

based on a sample of selected database entries Calculates the standard deviation

DSTDEVP

=DSTDEVP(database,eld,criteria)

based on the entire population of selected database entries Estimates variance based on a

DVAR

=DVAR(database,eld,criteria)

sample from selected database entries

Calculates variance based on the DVARP

=DVARP(database,eld,criteria)

entire population of selected database entries

Date & Time Excel Formulas & Functions Functions

Excel Formulas

Description

DATE

=DATE(year,month,day)

DATEVALUE

=DATEVALUE(date_text)

DAY

=DAY(serial_number)

HOUR

=HOUR(serial_number)

Converts a serial number to an hour

MINUTE

=MINUTE(serial_number)

Converts a serial number to a minute

MONTH

==MONTH(serial_number)

Converts a serial number to a month

NOW

=NOW()

SECOND

=SECOND(serial_number)

TIME

=TIME(hour,minute,second)

TIMEVALUE

=TIMEVALUE(time_text)

TODAY

=TODAY()

YEAR

=YEAR(serial_number)

Returns the serial number of a particular date Converts a date in the form of text to a serial number Converts a serial number to a day of the month

Returns the serial number of the current date and time Converts a serial number to a second Returns the serial number of a particular time Converts a time in the form of text to a serial number Returns the serial number of today’s date Converts a serial number to a year Calculates the number of days

DAYS360

=DAYS360(start_date,end_date,method)

between two dates based on a 360day year Returns the serial number of the date

EDATE

=EDATE(start_date,months)

that is the indicated number of months before or after the start date Returns the serial number of the last

EOMONTH

=EOMONTH(start_date,months)

day of the month before or after a specied number of months

NETWORKDAYS

=NETWORKDAYS(start_date,end_date, [holidays])

Returns the number of whole workdays between two dates

Returns the number of whole NETWORKDAYS.INTL

=NETWORKDAYS.INTL(start_date,end_date, workdays between two dates using [weekend],[holidays]) parameters to indicate which and how many days are weekend days

WEEKDAY

WEEKNUM

WORKDAY

=WEEKDAY(serial_number, [return_type])

=WEEKNUM(serial_number, [return_type])

=WORKDAY(start_date, days, [holidays])

Converts a serial number to a day of the week Converts a serial number to a number representing where the week falls numerically with a year Returns the serial number of the date before or after a specied number of workdays Returns the serial number of the date before or after a specied number of

WORKDAY.INTL

=WORKDAY.INTL(start_date,days,weekend,holidays) workdays using parameters to indicate which and how many days are weekend days Returns the year fraction

YEARFRAC

=YEARFRAC(start_date,end_date,basis)

representing the number of whole days between start_date and end_date

Information Excel Formulas & Functions Functions

Excel Formulas

Description

CELL

=CELL(info_type, [reference])

formatting, location, or contents of a

Returns information about the cell ISBLANK

=ISBLANK(value)

ISERROR

=ISERROR(value)

ISNONTEXT

=ISNONTEXT(value)

ISNUMBER

=ISNUMBER(value)

ISTEXT

=ISTEXT(value)

ERROR.TYPE

=ERROR.TYPE(error_val)

INFO

=INFO(type_text)

Returns TRUE if the value is blank Returns TRUE if the value is any error value Returns TRUE if the value is not text Returns TRUE if the value is a number Returns TRUE if the value is text Returns a number corresponding to an error type Returns information about the current operating environment

ISERR

=ISERR(value)

Returns TRUE if the value is any error value except #N/A

ISEVEN

=ISEVEN(number)

Returns TRUE if the number is even

ISLOGICAL

=ISLOGICAL(value)

ISNA

=ISNA(value)

ISODD

=ISODD(number)

ISREF

=ISREF(value)

N

=N(value)

NA

=NA()

TYPE

=TYPE(value)

Returns TRUE if the value is a logical value Returns TRUE if the value is the #N/A error value Returns TRUE if the number is odd Returns TRUE if the value is a reference Returns a value converted to a number Returns the error value #N/A Returns a number indicating the data type of a value

Logical Excel Formulas & Functions Functions

Excel Formulas

AND

=AND(logical1,logical2,…)

FALSE

=FALSE

IF

=IF(logical_test, [value_if_true], [value_if_false])

Description Returns TRUE if all of its arguments are TRUE Returns the logical value FALSE

Species a logical test to perform

Returns a value you specify if a IFERROR

=IFERROR(value, value_if_error)

formula evaluates to an error; otherwise, returns the result of the formula

NOT

=NOT(logical)

OR

=OR(logical1,logical2,…)

TRUE

=TRUE

LOOKUP

=LOOKUP(lookup_value, array)– 2 types

Lookup & Reference Excel Formulas & Functions

Reverses the logic of its argument Returns TRUE if any argument is TRUE Returns the logical value TRUE

Looks up values in a vector or array

Functions

ADDRESS

Excel Formulas

Description

=ADDRESS(row_num, column_num,

Returns a reference as text to a single

[abs_num], [a1], [sheet_text])

COLUMN

=COLUMN([reference])

COLUMNS

=COLUMNS(array)

HLOOKUP

INDEX

INDIRECT

cell in a worksheet Returns the column number of a reference Returns the number of columns in a reference

Looks in the top row of an array and =HLOOKUP(lookup_value,table_array,row_index_num, [range_lookup])

returns the value of the indicated cell

=INDEX(array,row_num,

Uses an index to choose a value from

[column_num])– 2 types

=INDIRECT(ref_text,a1)

a reference or array Returns a reference indicated by a text value

MATCH

Looks up values in a reference or =MATCH(lookup_value,lookup_array,match_type) array

OFFSET

=OFFSET(reference,rows,cols,height,width)

ROW

=ROW([reference])

ROWS

=ROWS(array)

Returns a reference offset from a given reference Returns the row number of a reference Returns the number of rows in a reference

VLOOKUP

Looks in the rst column of an array =VLOOKUP(lookup_value,table_array,col_index_num, and moves across the row to return [range_lookup]) the value of a cell

CHOOSE

=CHOOSE(index_num,value1,value2,…)

GETPIVOTDATA

Chooses a value from a list of values

Returns data stored in a PivotTable =GETPIVOTDATA(data_eld,pivot_table,eld,item,…) report Creates a shortcut or jump that opens

HYPERLINK

=HYPERLINK(link_location,friendly_name)

a document stored on a network server, an intranet, or the Internet

TRANSPOSE

=TRANSPOSE(array)

AREAS

=AREAS(reference)

Returns the transpose of an array Returns the number of areas in a reference

Retrieves real-time data from a program that supports COM automation (Automation: A way to work with an application’s objects RTD

=RTD(progID,server,topic1,topic2,…)

from another application or development tool. Formerly called OLE Automation, Automation is an industry-standard and a feature of the Component Object Model (COM).)

Text Excel Formulas & Functions Functions

Excel Formulas

EXACT

=EXACT(text1,text2)

LOWER

=LOWER(text)

PROPER

=PROPER(text)

TRIM

=TRIM(text)

Removes spaces from text

UPPER

=UPPER(text)

Converts text to uppercase

CHAR

=CHAR(number)

CLEAN

=CLEAN(text)

CODE

=CODE(text)

DOLLAR

=DOLLAR(number,decimals)

FIXED

=FIXED(number,decimals,no_commas)

PHONETIC

=PHONETIC(reference)

REPT

=REPT(text,number_times)

SUBSTITUTE

Description Checks to see if two text values are identical Converts text to lowercase Capitalizes the rst letter in each word of a text value

Returns the charact...