Excel Advantages

0

Excel application has many advantages and we are going to discuss each and every benefit and drawbacks of the excel here.

Excel application mainly very useful for calculation purpose. It is best application to do any kind of reports which was invented by Microsoft team. It is one of the MS Office applications.

How To Use If And VLOOKUP Together

12

How To Use IF and VLOOKUP Together

As discussed in the previous posts, VLOOKUP function has feature to mingle with other excel function or formulas. You are going to learn the way how we can use the IF condition with VLOOKUP Function. IF condition also very powerful formula in the excel and other programming languages.

IF Condition:

IF condition is logical function. For example if you want to test one cell whether there is a value or not, you can use this function and know the result, if condition has two variety of options 1) True Block, 2) False Block and one condition. When condition is true then True Block executes. Similarly condition is false then False block executes. Hence VLOOKUP can be used in this either in True block or in False block depending upon our requirement. We have already given you about the VLOOKUP functionality.

Syntax : =IF(Condition, True_Block, False_Block)

VLOOKUP Function:

Since we have already discussed about VLOOKUP in detailed manner, I am going to provide you simple example on the VLOOKUP function. It provides you the right side value as result based on given left side value. And it works column wise. Means we can give the table array as columns only.

Syntax: VLOOKUP(LOOKUP_Value,Table_Array,Column_Index_Number,Exact_Match (False or 0), Approximate_Match (True or 1))

 

How to use VLOOKUP and IF Condition in single formula

 

See Also:

Vlookup function
Vlookup Errors in Excel – Excel Formula

 

 

 

 

 

 

 

 

 

 

Vlookup Errors in Excel – Excel Formula

72

Vlookup Error

Vlooup errors are 3 types  (1) Vlookup  #N/A error, (2) Vlookup #VALUE Error and (3) Vlookup #NAME error . Here in this article we are going to discuss all these vlookup errors in depth and you are going to learn how to fix these errors instantly to get the correct results. Before you go into this article you must know about the what is vlookup function, what is the definition of the vlookup and how to when you use the vlookup once you learn these tricks then you please read this article. Else it is very difficult for you to understand. 

1)  Vlookup #N/A Error :

If you check vlookup syntax, it has 4 variety of ingredients are available in that 1st one is Lookup Value, 2nd Table Array, 3rd column index number and 4th exact match or approximate match

If Lookup value related value is unavailable in the given range then this error can be thrown by Vlookup formula. When you get #N/A in the vlookup, you have to check whether you have the given correct lookup values or any misspelled in the lookupvalue. Once it is done, still are you getting #N/A error?

Then you need to use the Ctrl + F option and give the lookup value related word to check whether the lookup value related word is available or not. If there are no such values then you vlookup is providing you correct error.

Sometimes due to unwanted spaces in the lookup value text, this error can be thrown, at that time you need remove the unwanted spaces from the lookup value related column in the data.

This error is known as logical error because we have write the correct formula still we did not get the result lack of look value in the data.

Troubleshooting VLOOKUP #N/A error is we need to either correct the lookup value related word like remove the trailing or leading spaces from the word. If that particular word is unavailable we can use the iferror with vlookup or isna with vlookup to bypass the error from the formula results

Example:

If you see in the below example lookup value Rajesh is unavailable in the E column but E column related value is the Lookup value. Hence we got this #N/A error in the vlookup functions

Vlookup #NA Error
Vlookup #NA Error

2)  Vlookup #VALUE Error:

This error occurs when we write the syntactical mistake in the vlookup formula . Hence when writing the vlookup, we need to ensure that are writing correct syntax else it throws the #VALUE error in the result. This can be occurred very rare scenarios because we all know that vlookup is very simple yet powerful formula. Once we get habituated with this formula, we never ever write the incorrect syntax for vlookup. Only when we are in the initial stage, that time there is a chance to write this kind of mistakes

 Fixing #VALUE error in VLOOKUP formulas:

We need to check where exactly missed our syntax and correct it. Then it works fine.  In order to do that first lookup for lookup value if that is given then go for the table array whether we have given that correctly or not, if that is also correct then check whether you have given the column index number or missed. And last finally you check whether you mentioned the exact match or approximate match. If you check these all parameters it should not have any syntactical error then obviously this error will not be appeared in your result

Example:

If you see in formula bar, you can find I have missed out the Lookkup value at first block. Hence we got this error #VALUE, if we mention correctly then result can be  populated.

Vlookup #VALUE Error

 

1)  Vlookup #NAME Error:

When you are writing the vlookup function and if you miss any letter from the vlooup spelling and write the formula then obviously you get this #NAME error.

Hence Vlookup #NAME Error can be fixed by just checking he spelling of the vlookup function.

 

Example:

if you see the formula bar in the below picture, I have written the unwanted space in the vlookup formula like vlook up. That is why we got this #Name error in the below scenario.

Vlooup #NAME Error
Vlooup #NAME Error

 

These all errors are common error in the vlookup function and you can bypass the #N/A error by writing the IfError function. If you write the vlookup function within the IFERROR function, you can catch the vlookup error and bypass the error message

How to fix the Vlookup errors:

  1. Vlookup #N/A Error can be bypassed by iferror function and provide the lookup value which available exactly in the lookup value column.
  2. Vlookup #VALUE error would be fixed by checking the syntax of the vlooup and correct that, then we can see the exact result if there is the correct lookup value
  3. Vlookup #NAME error can be rectified by checking the spelling the vlookup function and correct parenthesis in the formula.

 

 

 

VLOOKUP function – Excel Formula

642
Vlookup Tutorial
Vlookup Tutorial

Vlookup function

This is the best formula among all excel formulas. Hence you are going to learn this formula in detailed in this article. Also you are going to know all the vlookup advantages and disadvantages ( Limitations ), definition,Syntax, Approximate Match, Exactmatch, which areas can be used the vlookup, Vlookup in same worksheet, Vlookup with duplicates, Vlookup different sheet, Named range in Vlookup, How to create Named range in excel and Vlookup Meaning.Vlookup full form is vertical lookup, Unless we follow the syntax, we get the result as vlookup Errors 

Vlookup definition:


Using this function we can get the right side values based on left values. In the below example 1 you can find the same.

Syntax :

Note: Always it is better to sort the values of the data in ascending or descending depending on your requirement to get the results else there might be chance to get unexpected results by this formula.

Example 1:

Vlookup in same worksheet:

Vlookup in Same worksheet Example
Vlookup in Same worksheet

Vlookup Syntax Explanation:

Lookup Value:  G4 cell is the Lookup value in the below example

Table Array:  C column and D columns are Table array

Column index Number: Since we select only two columns C and D. And we require the 2nd column values as results, we are going to give the column index number as 2

Exact match: 99.99% of time we regularly use the exact-match that is denoted by zero ‘0’. Here in the below example you can find the exact-match only.

Example 2:

Vlookup from another sheet:

It is also same as example 1, only one difference is that you need to add the sheet name to the table array in the below syntax. Here in the below example you can find the data is in the vlookup data sheet. And result in the Vlookup result sheet. Hence the syntax would be as shown in the below picture.

Table Array:

It has the change that is ‘Vlookup Data’!

vlookup from another sheet Data
vlookup from another sheet-Data

Vlookup from different Sheet as shown in the below

vlookup from another sheet-formula
vlookup from another sheet-formula

Approximate match: Very rarely we use this approximate-match that is denoted by ‘1’

Example 3:

Vlookup Example To Use Approximate Match
Vlookup Example To Use Approximate Match

The below areas can be used the vlookup function :

Userforms, subroutines, modules, textbox, listbox, combobox, worksheets (with in the same worksheets,), different excel worksheets (means one sheet to another worksheet with in the same workbook), different excel workbooks  and User defined functions

Vlookup advantages:

  1. Mainly this function can be used for the purpose of searching values for the respective lookup values.
  2. VLOOKUP is simple formula and very powerful in excel spreadsheet
  3. Many Excel formula can be used within the and along with VLOOKUP
  4. Assigning the right values for the given lookup values as I have explained you in the approximate match example in the above.
  5. We can customize the function by taking the support of the other excel functions like countif, sumif, count, countifs, sumifs, and sum function. This function can be used with the combination of the sumproduct, if function, nested if function, Indirect, index and match functions.
  6. VLOOKUP can be customized in the user defined functions as and when we require. This is one of the major advantages.
  7. This function can be used for Pivot tables and tables also to get the exact values
  8. We can use the named ranges for range criteria in the vlookup syntax as shown in the below

Named Range in the Vlookup:

Named range is nothing but a collections of cells within the range where you can provide the name for that specific range

Example:

Vlookup Example to use Named Range
Vlookup Example to use Named Range

Named Range In Excel:

Creating Named Range in Excel Spreadsheet
Creating Named Range in Excel Spreadsheet

Vlookup disadvantages ( Limitations ):

  1. Vlookup can not get the right value when there are duplicates in the lookup value related column. We must need to sort the data when there are duplicate values in the lookup value related column. Then this function can return the first value related value This is the major drawback of this function. Of course we can overcome this problem by using the different functions in the excel workbooks

Vlookup with duplicate values:

Example 4:

Vlookup without sorting for duplicate lookupvalue
Vlookup without sorting for duplicate lookupvalue

 

2. Using this function we can only pull the right side values as we discussed in the above. If we need to pull the left side values based on the right side value. We can use the reverse vlookup function. This is the combination of the index and match functions.

3.It only works on columns if we need to use for the rows then we have to use the Hlookup function which is very similar to Vlookup function. Hence reverse vlookup which is the combination of the index and match functions.

4. Vlookup major disadvantage is when you have more vlookup formulas within Spreadsheet or within one Excel workbook, it hangs a lot since it is an array formula. When user getting hanged in the workbook that time user need to setup formulas calculation option as manual, you can find the below screen shot to setup manual option in the excel.

Example 5:

Formula Calculation option setting as Manual
Formula Calculation option setting as Manual

Once we finish working in the excel, we need to set back as Automatic in the above option as usual

Vlookup Meaning:

Vlookup significance is nothing but a Vertical lookup where you can search the data vertically based on the lookup values availability. As I have explained you in the above examples. Vlookup is one of the key functions within the excel Spreadsheet. Almost many MIS tasks get complete with only with this function. Still this has some limitations as we have already discussed in the above. But we can overcome this lose by taking the support of the other excel functions. Here in the excel Vlookup point of view vertical in the sense column by column

Example 6:

Column A, Column B wise from left to right we can search the values using this VLOOKUP formula in Excel spreadsheets. Please observe the below Screenshot for better understanding

Vlookup searches from Left to right vertically column wise
Vlookup searches from Left to right vertically column wise

There are many wonderful examples can be derived by Vlookup in the live projects, those also we are trying to include in this article at the earliest.

This entire article can be considered as Vlookup tutorial

See Also :

Index & Match Combination ( Reverse Vlookup)
VLOOKUP Error types 
How To Use If And VLOOKUP Together

Loop

3

Loop

Here we are going to know about the loops in VBA. loops are 4 types in VBA. Those are while loop, loop, for loop, do while loop.

Loop is concept in programming language to run continuously until it gets particular given target. For example if you need to run the 100 numbers in vba program. You must use the loop concept to do this task. Then you need not write 100 lines of the code to print 100 numbers. This is a main advantage of the loop.

 

 

List Of All Excel Formulas or Functions

117

List Of All Excel Formulas or Functions

 

Download vlookup formula here

Excel Function name

Description of Excel Function

CALL functionCalls a procedure in a dynamic link library or code resource
EUROCONVERT functionConverts a number to euros, converts a number from euros to a euro member currency, or converts a number from one euro member currency to another by using the euro as an intermediary (triangulation).
REGISTER.ID functionReturns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered
SQL.REQUEST functionConnects with an external data source and runs a query from a worksheet, then returns the result as an array without the need for macro programming
BETADIST functionIt eturns the beta cumulative distribution function In Excel 2007. This is a Statistical function used for statistical operations
BETAINV functionReturns the inverse of the cumulative distribution function for a specified beta distribution
CHIDIST functionReturns the one-tailed probability of the chi-squared distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
CHIINV functionReturns the inverse of the one-tailed probability of the chi-squared distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
CHITEST functionReturns the test for independence. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
CONFIDENCE functionReturns the confidence interval for a population mean. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
COVAR functionReturns covariance, the average of the products of paired deviations. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
CRITBINOM functionReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
EXPONDIST functionReturns the exponential distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
FDIST functionReturns the F probability distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
FLOOR functionRounds a number down, toward zero. This is the newly added function from 2007 Excel version and this is very useful Excel formula for  Mathametical and trigonometrical operations
FTEST functionReturns the result of an F-test. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
GAMMADIST functionReturns the gamma distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
GAMMAINV functionReturns the inverse of the gamma cumulative distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
HYPGEOMDIST functionReturns the hypergeometric distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
LOGINV functionReturns the inverse of the lognormal cumulative distribution
LOGNORMDIST functionReturns the cumulative lognormal distribution
MODE functionReturns the most common value in a data set. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
NEGBINOMDIST functionReturns the negative binomial distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
NORMDIST functionReturns the normal cumulative distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
NORM.INV functionReturns the inverse of the normal cumulative distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
NORMSDIST functionReturns the standard normal cumulative distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
NORMSINV functionReturns the inverse of the standard normal cumulative distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
PERCENTILE functionReturns the k-th percentile of values in a range2. This  is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
PERCENTRANK functionReturns the percentage rank of a value in a data set. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
POISSON functionReturns the Poisson distribution. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
QUARTILE functionReturns the quartile of a data set. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
RANK functionReturns the rank of a number in a list of numbers. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
STDEV functionEstimates standard deviation based on a sample
STDEVP functionCalculates standard deviation based on the entire population. is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
TDIST functionReturns the Student’s t-distribution
TINV functionThis function quite opposite to TDIST function. It returns the inverse of TDIST Student’s Tdistribution
TTEST functionReturns the probability associated with a Student’s t-test. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
VAR functionEstimates variance based on a sample. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
VARP functionCalculates variance based on the entire population. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
WEIBULL functionThis function calculates variance based on the all population, even includes numbers, text, and logical values as well. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
ZTEST functionReturns the one-tailed probability-value of a z-test.This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
CUBEKPIMEMBER functionReturns a key performance indicator (KPI) name, property, and measure, and displays the name and property in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, used to monitor an organization’s performance.
CUBEMEMBER functionReturns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube.
CUBEMEMBERPROPERTY functionReturns the value of a member property in the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.
CUBERANKEDMEMBER functionReturns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or top 10 students.
CUBESET functionDefines a calculated set of members or tuples through sending a fixed expression to the cube at the server, which creates the set, after which returns that set to Microsoft Office Excel
CUBESETCOUNT functionNumber of items in the given set can be returned
CUBEVALUE functionAggregated vlaue of the cube can be returned by this function
DAVERAGE functionReturns the average of selected database entries
DCOUNT functionCounts the cells that contain numbers in a database
DCOUNTA functionCounts nonblank cells in a database
DGET functionExtracts from a database a single record that matches the specified criteria
DMAX functionReturns the maximum value from selected database entries
DMIN functionReturns the minimum value from selected database entries
DPRODUCT functionMultiplies the values in a particular field of records that match the criteria in a database
DSTDEV functionEstimates the standard deviation based on a sample of selected database entries
DSTDEVP functionCalculates the standard deviation based on the entire population of selected database entries
DSUM functionAdds the numbers in the field column of records in the database that match the criteria
DVAR functionEstimates variance based on a sample from selected database entries
DVARP functionCalculates variance based on the entire population of selected database entries
DATE functionReturns the serial number of a particular date
DATEDIF functionCalculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.
DATEVALUE functionConverts a date in the form of text to a serial number
DAY functionConverts a serial number to a day of the month
DAYS functionReturns the number of days between two dates
DAYS360 functionCalculates the number of days between two dates based on a 360-day year
EDATE functionReturns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH functionReturns the serial number of the last day of the month before or after a specified number of months
HOUR functionConverts a serial number to an hour
ISOWEEKNUM functionReturns the number of the ISO week number of the year for a given date
MINUTE functionConverts a serial number to a minute
MONTH functionConverts a serial number to a month
NETWORKDAYS functionReturns the number of whole workdays between two dates
NETWORKDAYS.INTL functionReturns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days
NOW functionReturns the serial number of the current date and time
SECOND functionConverts a serial number to a second
TIME functionReturns the serial number of a particular time
TIMEVALUE functionConverts a time in the form of text to a serial number
TODAY functionReturns the serial number of today’s date
WEEKDAY functionConverts a serial number to a day of the week
WEEKNUM functionConverts a serial number to a number representing where the week falls numerically with a year
WORKDAY functionReturns the serial number of the date before or after a specified number of workdays
WORKDAY.INTL functionReturns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days
YEAR functionConverts a serial number to a year
YEARFRAC functionReturns the year fraction representing the number of whole days between start_date and end_date
BESSELI functionReturns the modified Bessel function In(x)
BESSELJ functionReturns the Bessel function Jn(x)
BESSELK functionReturns the modified Bessel function Kn(x)
BESSELY functionReturns the Bessel function Yn(x)
BIN2DEC functionConverts a binary number to decimal
BIN2HEX functionConverts a binary number to hexadecimal
BIN2OCT functionConverts a binary number to octal
BITAND functionReturns a ‘Bitwise And’ of two numbers
BITLSHIFT functionReturns a value number shifted left by shift_amount bits
BITOR functionReturns a bitwise OR of 2 numbers
BITRSHIFT functionReturns a value number shifted right by shift_amount bits
BITXOR functionReturns a bitwise ‘Exclusive Or’ of two numbers
COMPLEX functionConverts real and imaginary coefficients into a complex number
CONVERT functionConverts a number from one measurement system to another
DEC2BIN functionConverts a decimal number to binary
DEC2HEX functionConverts a decimal number to hexadecimal
DEC2OCT functionConverts a decimal number to octal
DELTA functionTests whether two values are equal
ERF functionReturns the error function
ERF.PRECISE functionReturns the error function
ERFC functionReturns the complementary error function
ERFC.PRECISE functionReturns the complementary ERF function integrated between x and infinity
GESTEP functionTests whether a number is greater than a threshold value
HEX2BIN functionConverts a hexadecimal number to binary
HEX2DEC functionConverts a hexadecimal number to decimal
HEX2OCT functionConverts a hexadecimal number to octal
IMABS functionReturns the absolute value (modulus) of a complex number
IMAGINARY functionReturns the imaginary coefficient of a complex number
IMARGUMENT functionReturns the argument theta, an angle expressed in radians
IMCONJUGATE functionReturns the complex conjugate of a complex number
IMCOS functionReturns the cosine of a complex number
IMCOSH functionReturns the hyperbolic cosine of a complex number
IMCOT functionReturns the cotangent of a complex number
IMCSC functionReturns the cosecant of a complex number
IMCSCH functionReturns the hyperbolic cosecant of a complex number
IMDIV functionReturns the quotient of two complex numbers
IMEXP functionReturns the exponential of a complex number
IMLN functionReturns the natural logarithm of a complex number
IMLOG10 functionReturns the base-10 logarithm of a complex number
IMLOG2 functionReturns the base-2 logarithm of a complex number
IMPOWER functionReturns a complex number raised to an integer power
IMPRODUCT functionReturns the product of complex numbers
IMREAL functionReturns the real coefficient of a complex number
IMSEC functionReturns the secant of a complex number
IMSECH functionReturns the hyperbolic secant of a complex number
IMSIN functionReturns the sine of a complex number
IMSINH functionReturns the hyperbolic sine of a complex number
IMSQRT functionReturns the square root of a complex number
IMSUB functionReturns the difference between two complex numbers
IMSUM functionReturns the sum of complex numbers
IMTAN functionReturns the tangent of a complex number
OCT2BIN functionConverts an octal number to binary
OCT2DEC functionConverts an octal number to decimal
OCT2HEX functionConverts an octal number to hexadecimal
ACCRINT functionReturns the accrued interest for a security that pays periodic interest
ACCRINTM functionReturns the accrued interest for a security that pays interest at maturity
AMORDEGRC functionReturns the depreciation for each accounting period by using a depreciation coefficient
AMORLINC functionReturns the depreciation for each accounting period
COUPDAYBS functionReturns the number of days from the beginning of the coupon period to the settlement date
COUPDAYS functionReturns the number of days in the coupon period that contains the settlement date
COUPDAYSNC functionReturns the number of days from the settlement date to the next coupon date
COUPNCD functionReturns the next coupon date after the settlement date
COUPNUM functionReturns the number of coupons payable between the settlement date and maturity date
COUPPCD functionReturns the previous coupon date before the settlement date
CUMIPMT functionReturns the cumulative interest paid between two periods
CUMPRINC functionReturns the cumulative principal paid on a loan between two periods
DB functionReturns the depreciation of an asset for a specified period by using the fixed-declining balance method
DDB functionReturns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
DISC functionReturns the discount rate for a security
DOLLARDE functionConverts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
DOLLARFR functionConverts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
DURATION functionReturns the annual duration of a security with periodic interest payments
EFFECT functionReturns the effective annual interest rate
FV functionReturns the future value of an investment
FVSCHEDULE functionReturns the future value of an initial principal after applying a series of compound interest rates
INTRATE functionReturns the interest rate for a fully invested security
IPMT functionReturns the interest payment for an investment for a given period
IRR functionReturns the internal rate of return for a series of cash flows
ISPMT functionCalculates the interest paid during a specific period of an investment
MDURATION functionReturns the Macauley modified duration for a security with an assumed par value of $100
MIRR functionReturns the internal rate of return where positive and negative cash flows are financed at different rates
NOMINAL functionReturns the annual nominal interest rate
NPER functionReturns the number of periods for an investment
NPV functionReturns the net present value of an investment based on a series of periodic cash flows and a discount rate
ODDFPRICE functionReturns the price per $100 face value of a security with an odd first period
ODDFYIELD functionReturns the yield of a security with an odd first period
ODDLPRICE functionReturns the price per $100 face value of a security with an odd last period
ODDLYIELD functionReturns the yield of a security with an odd last period
PDURATION functionReturns the number of periods required by an investment to reach a specified value
PMT functionReturns the periodic payment for an annuity
PPMT functionReturns the payment on the principal for an investment for a given period
PRICE functionReturns the price per $100 face value of a security that pays periodic interest
PRICEDISC functionReturns the price per $100 face value of a discounted security
PRICEMAT functionReturns the price per $100 face value of a security that pays interest at maturity
PV functionReturns the present value of an investment
RATE functionReturns the interest rate per period of an annuity
RECEIVED functionReturns the amount received at maturity for a fully invested security
RRI functionReturns an equivalent interest rate for the growth of an investment
SLN functionReturns the straight-line depreciation of an asset for one period
SYD functionReturns the sum-of-years’ digits depreciation of an asset for a specified period
TBILLEQ functionReturns the bond-equivalent yield for a Treasury bill
TBILLPRICE functionReturns the price per $100 face value for a Treasury bill
TBILLYIELD functionReturns the yield for a Treasury bill
VDB functionReturns the depreciation of an asset for a specified or partial period by using a declining balance method
XIRR functionReturns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPV functionReturns the net present value for a schedule of cash flows that is not necessarily periodic
YIELD functionReturns the yield on a security that pays periodic interest
YIELDDISC functionReturns the annual yield for a discounted security; for example, a Treasury bill
YIELDMAT functionReturns the annual yield of a security that pays interest at maturity
CELL functionReturns information about the formatting, location, or contents of a cell. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
ERROR.TYPE functionReturns a number corresponding to an error type
INFO functionReturns information about the current operating environment. This function returns the current opration environment
ISBLANK functionReturns TRUE if the value is blank
ISERR functionReturns TRUE if the value is any error value except #N/A
ISERROR functionReturns TRUE if the value is any error value
ISEVEN functionReturns TRUE if the number is even
ISFORMULA functionReturns TRUE if there is a reference to a cell that contains a formula
ISLOGICAL functionReturns TRUE if the value is a logical value
ISNA functionReturns TRUE if the value is the #N/A error value
ISNONTEXT functionReturns TRUE if the value is not text
ISNUMBER functionReturns TRUE if the value is a number
ISODD functionReturns TRUE if the number is odd
ISREF functionReturns TRUE if the value is a reference
ISTEXT functionReturns TRUE if the value is text
N functionReturns a value converted to a number
NA functionReturns the error value #N/A
SHEET functionReturns the sheet number of the referenced sheet
SHEETS functionReturns the number of sheets in a reference
TYPE functionReturns a number indicating the data type of a value
AND functionReturns TRUE if all of its arguments are TRUE
FALSE functionReturns the logical value FALSE
IF functionSpecifies a logical test to perform
IFERROR functionReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
IFNA functionReturns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression
IFS functionChecks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.This  function is not implimented in the Mac version
NOT functionReverses the logic of its argument
OR functionReturns TRUE if any argument is TRUE
SWITCH functionEvaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. This function isn’t available in Excel 2016 for Mac.
TRUE functionReturns the logical value TRUE
XOR functionReturns a logical exclusive OR of all arguments
ADDRESS functionReturns a reference as text to a single cell in a worksheet
CHOOSE functionChooses a value from a list of values
COLUMN functionReturns the column number of a reference
COLUMNS functionReturns the number of columns in a reference
FORMULATEXT functionReturns the formula at the given reference as text
GETPIVOTDATA functionReturns data stored in a PivotTable report
HLOOKUP functionLooks in the top row of an array and returns the value of the indicated cell
HYPERLINK functionCreates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDEX functionUses an index to choose a value from a reference or array
INDIRECT functionReturns a reference indicated by a text value
LOOKUP functionLooks up values in a vector or array
MATCH functionLooks up values in a reference or array
OFFSET functionReturns a reference offset from a given reference
ROW functionReturns the row number of a reference
ROWS functionReturns the number of rows in a reference
RTD functionRetrieves real-time data from a program that supports COM automation
TRANSPOSE functionReturns the transpose of an array
VLOOKUP functionLooks in the first column of an array and moves across the row to return the value of a cell
ACOS functionThis function returns the arccosine number of the given number
ACOSH functionReturns the inverse hyperbolic cosine of a number. Since this is a trigonometrical function. We can use this in such operations
ACOT functionReturns the arccotangent of a number
ACOTH functionReturns the hyperbolic arccotangent of a number
AGGREGATE functionReturns an aggregate in a list or database
ARABIC functionConverts a Roman number to Arabic, as a number
BASE functionConverts a number into a text representation with the given radix (base)
CEILING functionRounds a number to the nearest integer or to the nearest multiple of significance
CEILING.MATH functionRounds a number up, to the nearest integer or to the nearest multiple of significance
CEILING.PRECISE functionRounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.
COMBIN functionReturns the number of combinations for a given number of objects. Returns the number of combinations with repetitions for a given number of items
COS functionReturns the cosine of a number
COSH functionReturns the hyperbolic cosine of a number
COT functionReturns the hyperbolic cosine of a number
COTH functionReturns the cotangent of an angle
CSC functionReturns the cosecant of an angle
CSCH functionReturns the hyperbolic cosecant of an angle
DECIMAL functionConverts a text representation of a number in a given base into a decimal number
DEGREES functionConverts radians to degrees
EVEN functionRounds a number up to the nearest even integer
EXP functionReturns e raised to the power of a given number
FACT functionReturns the factorial of a number
FACTDOUBLE functionReturns the double factorial of a number
FLOOR.MATH functionRounds a number down, to the nearest integer or to the nearest multiple of significance
FLOOR.PRECISE functionRounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.
GCD functionReturns the greatest common divisor
INT functionRounds a number down to the nearest integer
ISO.CEILING functionReturns a number that is rounded up to the nearest integer or to the nearest multiple of significance
LCM functionReturns the least common multiple
LN functionReturns the natural logarithm of a number
LOG functionReturns the logarithm of a number to a specified base
LOG10 functionReturns the base-10 logarithm of a number
MDETERM functionReturns the matrix determinant of an array
MINVERSE functionReturns the matrix inverse of an array
MMULT functionReturns the matrix product of two arrays
MOD functionReturns the remainder from division
MROUND functionReturns a number rounded to the desired multiple
MULTINOMIAL functionReturns the multinomial of a set of numbers
MUNIT functionReturns the unit matrix or the specified dimension
ODD functionRounds a number up to the nearest odd integer
PI functionReturns the value of pi
POWER functionReturns the result of a number raised to a power
PRODUCT functionMultiplies its arguments
QUOTIENT functionReturns the integer portion of a division
RADIANS functionConverts degrees to radians
RAND functionReturns a random number between 0 and 1
RANDBETWEEN functionReturns a random number between the numbers you specify
ROMAN functionConverts an arabic numeral to roman, as text
ROUND functionRounds a number to a specified number of digits
ROUNDDOWN functionRounds a number down, toward zero
ROUNDUP functionRounds a number up, away from zero
SEC functionReturns the secant of an angle
SECH functionReturns the hyperbolic secant of an angle
SERIESSUM functionReturns the sum of a power series based on the formula
SIGN functionReturns the sign of a number
SIN functionReturns the sine of the given angle
SINH functionReturns the hyperbolic sine of a number
SQRT functionReturns a positive square root
SQRTPI functionReturns the square root of (number * pi)
SUBTOTAL functionReturns a subtotal in a list or database
SUM functionAdds its arguments
SUMIF functionAdds the cells specified by a given criteria
SUMIFS functionAdds the cells in a range that meet multiple criteria
SUMPRODUCT functionReturns the sum of the products of corresponding array components
SUMSQ functionReturns the sum of the squares of the arguments
SUMX2MY2 functionReturns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2 functionReturns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2 functionReturns the sum of squares of differences of corresponding values in two arrays
TAN functionReturns the tangent of a number
TANH functionReturns the hyperbolic tangent of a number
TRUNC functionTruncates a number to an integer
ABS functionABS Funtion returns only the positive value
AVERAGE functionReturns the average of its arguments
AVERAGEA functionReturns the average of its arguments, including numbers, text, and logical values
AVERAGEIF functionReturns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFS functionReturns the average (arithmetic mean) of all cells that meet multiple criteria.
BETA.DIST functionReturns the beta cumulative distribution function
BETA.INV functionThis is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
BINOM.DIST functionReturns the individual term binomial distribution probability
BINOM.DIST.RANGE functionReturns the probability of a trial result using a binomial distribution
BINOM.INV functionReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
CHISQ.DIST functionReturns the cumulative beta probability density function
CHISQ.DIST.RT functionReturns the one-tailed probability of the chi-squared distribution
CHISQ.INV functionReturns the cumulative beta probability density function
CHISQ.INV.RT functionReturns the inverse of the one-tailed probability of the chi-squared distribution
CHISQ.TEST functionReturns the test for independence
CONFIDENCE.NORM functionReturns the confidence interval for a population mean
CONFIDENCE.T functionReturns the confidence interval for a population mean, using a Student’s t distribution
CORREL functionReturns the correlation coefficient between two data sets
COUNT functionCounts how many numbers are in the list of arguments
COUNTA functionCounts how many values are in the list of arguments
COUNTBLANK functionCounts the number of blank cells within a range
COUNTIF functionCounts the number of cells within a range that meet the given criteria
COUNTIFS functionCounts the number of cells within a range that meet multiple criteria
COVARIANCE.P functionReturns covariance, the average of the products of paired deviations
COVARIANCE.S functionReturns the sample covariance, the average of the products deviations for each data point pair in two data sets
DEVSQ functionReturns the sum of squares of deviations
EXPON.DIST functionReturns the exponential distribution
F.DIST functionReturns the F probability distribution
F.DIST.RT functionReturns the F probability distribution
F.INV functionReturns the inverse of the F probability distribution
F.INV.RT functionReturns the inverse of the F probability distribution
FINV functionReturns the inverse of the F probability distribution
FISHER functionReturns the Fisher transformation
FISHERINV functionReturns the inverse of the Fisher transformation
FORECAST functionReturns a value along a linear trend. This formula was updated in the 2016 Excel version , this function was changed as FORECAST.LINEAR Still this function is available in the previous versions
FORECAST.ETS functionReturns a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm
FORECAST.ETS.CONFINT functionReturns a confidence interval for the forecast value at the specified target date
FORECAST.ETS.SEASONALITY functionReturns the length of the repetitive pattern Excel detects for the specified time series
FORECAST.ETS.STAT functionReturns a statistical value as a result of time series forecasting
FORECAST.LINEAR functionReturns a future value based on existing values
FREQUENCY functionReturns a frequency distribution as a vertical array
F.TEST functionReturns the result of an F-test
GAMMA functionReturns the Gamma function value
GAMMA.DIST functionReturns the gamma distribution
GAMMA.INV functionReturns the inverse of the gamma cumulative distribution
GAMMALN functionReturns the natural logarithm of the gamma function, Γ(x)
GAMMALN.PRECISE functionReturns the natural logarithm of the gamma function, Γ(x)
GAUSS functionReturns 0.5 less than the standard normal cumulative distribution
GEOMEAN functionReturns the geometric mean
GROWTH functionReturns values along an exponential trend
HARMEAN functionReturns the harmonic mean
HYPGEOM.DIST functionReturns the hypergeometric distribution
INTERCEPT functionReturns the intercept of the linear regression line
KURT functionReturns the kurtosis of a data set
LARGE functionReturns the k-th largest value in a data set
LINEST functionReturns the parameters of a linear trend
LOGEST functionReturns the parameters of an exponential trend
LOGNORM.DIST functionReturns the cumulative lognormal distribution
LOGNORM.INV functionReturns the inverse of the lognormal cumulative distribution
MAX functionReturns the maximum value in a list of arguments
MAXA functionReturns the maximum value in a list of arguments, including numbers, text, and logical values
MAXIFS functionReturns the maximum value among cells specified by a given set of conditions or criteria
MEDIAN functionReturns the median of the given numbers
MIN functionReturns the minimum value in a list of arguments
MINIFS functionReturns the minimum value among cells specified by a given set of conditions or criteria.
MINA functionReturns the smallest value in a list of arguments, including numbers, text, and logical values
MODE.MULT functionReturns a vertical array of the most frequently occurring, or repetitive values in an array or range of data
MODE.SNGL functionReturns the most common value in a data set
NEGBINOM.DIST functionReturns the negative binomial distribution
NORM.DIST functionReturns the normal cumulative distribution
NORMINV functionReturns the inverse of the normal cumulative distribution
NORM.S.DIST functionReturns the standard normal cumulative distribution
NORM.S.INV functionReturns the inverse of the standard normal cumulative distribution
PEARSON functionReturns the Pearson product moment correlation coefficient
PERCENTILE.EXC functionReturns the k-th percentile of values in a range, where k is in the range 0..1, exclusive
PERCENTILE.INC functionReturns the k-th percentile of values in a range
PERCENTRANK.EXC functionReturns the rank of a value in a data set as a percentage (0..1, exclusive) of the data set
PERCENTRANK.INC functionReturns the percentage rank of a value in a data set
PERMUT functionReturns the number of permutations for a given number of objects
PERMUTATIONA functionReturns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
PHI functionReturns the value of the density function for a standard normal distribution
POISSON.DIST functionReturns the Poisson distribution
PROB functionReturns the probability that values in a range are between two limits
QUARTILE.EXC functionReturns the quartile of the data set, based on percentile values from 0..1, exclusive
QUARTILE.INC functionReturns the quartile of a data set
RANK.AVG functionReturns the rank of a number in a list of numbers
RANK.EQ functionReturns the rank of a number in a list of numbers
RSQ functionReturns the square of the Pearson product moment correlation coefficient
SKEW functionReturns the skewness of a distribution
SKEW.P functionReturns the skewness of a distribution based on a population: a characterization of the degree of asymmetry of a distribution around its mean
SLOPE functionReturns the slope of the linear regression line
SMALL functionReturns the k-th smallest value in a data set
STANDARDIZE functionReturns a normalized value
STDEV.P functionCalculates standard deviation based on the entire population
STDEV.S functionEstimates standard deviation based on a sample
STDEVA functionEstimates standard deviation based on a sample, including numbers, text, and logical values
STDEVPA functionCalculates standard deviation based on the entire population, including numbers, text, and logical values
STEYX functionReturns the standard error of the predicted y-value for each x in the regression
T.DIST functionReturns the Percentage Points (probability) for the Student t-distribution
T.DIST.2T functionReturns the Percentage Points (probability) for the Student t-distribution
T.DIST.RT functionReturns the Student’s t-distribution
T.INV functionReturns the t-value of the Student’s t-distribution as a function of the probability and the degrees of freedom
T.INV.2T functionReturns the inverse of the Student’s t-distribution
TREND functionReturns values along a linear trend
TRIMMEAN functionReturns the mean of the interior of a data set
T.TEST functionReturns the probability associated with a Student’s t-test
VAR.P functionCalculates variance based on the entire population
VAR.S functionEstimates variance based on a sample
VARA functionEstimates variance based on a sample, including numbers, text, and logical values
VARPA functionCalculates variance based on the entire population, including numbers, text, and logical values
WEIBULL.DIST functionReturns the Weibull distribution
Z.TEST functionReturns the one-tailed probability-value of a z-test
BAHTTEXT functionConverts a number to text, using the ß (baht) currency format
CHAR functionReturns the character specified by the code number
CLEAN functionRemoves all nonprintable characters from text
CODE functionReturns a numeric code for the first character in a text string
CONCAT functionCombines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or IgnoreEmpty arguments.
CONCATENATE functionJoins several text items into one text item
DBCS functionChanges half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
DOLLAR functionConverts a number to text, using the $ (dollar) currency format
EXACT functionChecks to see if two text values are identical
FIND, FINDB functionsFinds one text value within another (case-sensitive)
FIXED functionFormats a number as text with a fixed number of decimals
JIS functionhanges half-width (single-byte) characters within a string to full-width (double-byte) characters
LEFT, LEFTB functionsReturns the leftmost characters from a text value
LEN, LENB functionsReturns the number of characters in a text string
LOWER functionConverts text to lowercase
MID, MIDB functionsReturns a specific number of characters from a text string starting at the position you specify
NUMBERVALUE functionConverts text to number in a locale-independent manner
PHONETIC functionExtracts the phonetic (furigana) characters from a text string
PROPER functionCapitalizes the first letter in each word of a text value
REPLACE, REPLACEB functionsReplaces characters within text
REPT functionRepeats text a given number of times
RIGHT, RIGHTB functionsReturns the rightmost characters from a text value
SEARCH, SEARCHB functionsFinds one text value within another (not case-sensitive)
SUBSTITUTE functionSubstitutes new text for old text in a text string
T functionConverts its arguments to text
TEXT functionFormats a number and converts it to text
TEXTJOIN functionCombines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
TRIM functionRemoves spaces from text
UNICHAR functionReturns the Unicode character that is references by the given numeric value
UNICODE functionReturns the number (code point) that corresponds to the first character of the text
UPPER functionConverts text to uppercase
VALUE functionConverts a text argument to a number
ENCODEURL functionReturns a URL-encoded string. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
FILTERXML functionReturns specific data from the XML content by using the specified Xpath. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations
WEBSERVICE functionReturns data from a web service. This is the newly added function in the 2007 Excel version and This formula is very useful Statistical operations