# Sumif Formula

0

Sumif formula has significant role in MS Excel. It is very useful to create the dashboard. Also it can be used with combination of formulas based on the requirement. When you need to sum the values based on the one particular criteria (value), you can use this Sumif formula. This can be used as an array formula, if necessary.

Syntax: =SUMIF(Range,Criteria,Sumrange)

3

# Management Information System (MIS)

Here we are going to provide you detailed information about what is MIS, where MIS activities can be used, what exactly to be done in the MIS, How to manage MIS Reports and MIS Excecutives / Data Analysts, how to get MIS Executive Job for freshers, experienced people, Qualities of the MIS Executive / Data Analyst and MIS job advantages and disadvantages.

We also discuss some important MIS Daily Reports like Head count report, SLA Report, Procure to pay report ( P2P Report ), AP Dashboard, AR Dashboard, GL Dashboard Invoice Summary Report, Payroll team related.

## What is MIS?

MIS is nothing but managing the huge information in a sequential order in order to retrieve the required information. Whenever we require the particular information from the huge database, you should have the proper plan to maintain the data.

## Where MIS activities can be used?

MIS activities can be used in the corporate companies, government sectors and many other places where the huge data involves.

## What exactly to be done in the MIS?

As discussed in the above, we have to maintain the huge data to retrieve again on need basis. Hence usually if the data is limited we can go head with the MS Excel. And MS Excel is compulsory to use in the MIS. Because calculations purpose MS excel is the excellent application. Usually in the MIS, Information would be stored as reports, dashboards and in the other data formats. In order to store the data in the sequential order, we will maintain in the reports wise. If MS excel is not capable to maintain the data, then we can go ahead with the MS Access to store the data. IF data is more 1 GB in one single file then we have to choose the SQL or other database model.

Excel single file capacity 30 MB. if exceeds more than 30 MB, then we have to choose the MS Access

MS Access single file capacity 1 GB. If exceeds more than that, then we can choose SQL database or any other database oracle based availability.

## How to manage MIS reports, Dashboards ?

### Managing the MIS Reports:

Usually we prepare the reports for the daily, weekly, monthly, quarterly, half-early or annually in a single file for the respective day/ week/ month etc.. we will keep the important cleaned data in a particular order column header wise to pick the important numbers ( Business drivers) to show the activity related status in a report. These reports to be cascaded to top level management and they take the decisions based on these numbers also they come to know the present situation of the activity.

### Managing the MIS Dashboards:

When you create the dashboards, you can keep the data sheets in the hidden mode and show only the display sheet which have important numbers and graphs related sheets. based on the frequency data can be empty and refill the data into the dashboard template.

Here one important thing is we need to have the formulas if we are going to prepare in the MS Excel. Excel formulas would play the key role in the MIS Reports/ Dashboards. This is one excellent advantage with the MS Excel. Any difficult thing can be achieved with formulas. we have the good formatting look in the MS Excel. That’s why management prefers to show the data in the Excel than any other applications.

## MIS Executives / Data Analyst :

MIS People are decision makers who can advise the awesome ideas to the top level management. Operation manager must have the MIS skills. MIS executives are also called as Data Analyst.

## Qualities of MIS Executives / Data Analyst :

This job role is very crucial for the company. All the confidential information will be there with the MIS Executive. Hence it is very important to hide the information from the non related people. Data Analyst must hide the lot of information from non related people.

### Example:

Assume that John is the MIS Exective and he is supporting to 4 departments in the same team. Hence he has all the confidential information of 4 departments. In such case one of the departments manager Rock came and asked about the total income for the month for the other department. Then John will be in trouble, he can not reject his request and at the same time he should not provide that information to Rock. Then John should say, “i am yet prepare the report, once it is done, I will let you know”. That’s how john should escape from Rock’s request. Because it is not related to Rock’s department. Due diligence is very important quality for the Data Analyst.

## Payroll Team Related MIS Activities:

Pay role team also has the MIS activities. Since this team maintains the salaries of the all employees, it is highly confidential task. Also it is very challenging job to process the salaries on monthly basis. I will provide another example which was happened to my colleague. He has process one guy salary to another guy because of the naming confusion. Two employees have to same name. That’s why he got confused and interchanged their salaries. It has become very big mistake in the Payroll MIS team.

Similar way finance team also has MIS activities. In this team all the reports will deal with the money related. Hence it is very challenging job to do. Every time MIS guy would deal with lot of money related numbers. If any report goes with wrong number, then company would be in trouble. Hence in this department, management would do double check all the MIS activities. Still sometimes errors occurs. whenever error occurs in the finance team, management gets many calls from their higher level like from clients. Hence It is very tough thing to manage, sometimes need to pay the penalty to client by company.

## How To Get MIS Executive Job as a fresher or experienced?

1. Educational qualification of the MIS Executive is a graduate from any bachelors
2. Hands on experience should be there in the MS Excel mainly and better to have power point presentation skills.
3. Should be capable to speak English fluently without any MTI ( Mother tongue influence). Means when people speak their mother tongue should not be appeared in the spoken English. Because sometime we may need to handle client calls at that point of time. Client may face problem to understand because most of the clients are from the USA, UK or Australia. If the client is from India that is fine. If we keep practice on daily basis, it is easier to remove the MTI from our spoken English
4. It is better to have 30 wpm minimum typing speed. Because sometimes there are requirements to type some information on need basis. It is very rare but good to have the typing skills
5. Better to have the Analytical knowledge, since always MIS executive would work the huge data. Freshers can ignore this point as they will not have any idea at the beginning stage
6. MIS Executive always plays with the huge data. Hence should be well versed with data analysis knowledge. Means when executive receives the data, need to check the what exactly the information and what exactly to be helped to management and what are the business drivers in the given data as such.
7. Good to have 7 QC tools (1 Pareto Analysis, 2 Histogram, 3 Fishbone analysis (cause and effect diagram), 4 check sheet, 5 Control chart 6 Scatter diagram, 7 Stratification) knowledge but not compulsory.
8. Pivot tables preparation is must for MIS Job position. Because most of the time need to handle with the huge data. When we have huge data, it is very difficult to understand the whole data at once. Hence if MIS job holder has capability of the creating pivot tables, It helps him a lot in understanding the whole data in a easy manner. Also helps him in creating the reports and dashboards
9. Should be capable with Excel dashboard creation, Excel reports, Excel template. When you say dashboards or reports. These are involved the excel formulas and other excel techniques like hide in the excel sheets and all. In order to learn this either you can see the older dashboards or google it with the excel dashboards and get the sample dashboards to understand. Freshers can ignore this point as they just need only excel formulas skills.
10. Excel Charts or Graphs would play key role in the MIS job. As management only looks these charts when they have no time to see whole information. Based the given data, MIS Executive would select the graphs like bar graph, pie chart or any other which suits to existing data points

The above points are very important for experienced guys and freshers who wants to join as MIS Executive. Few points are not compulsory to fresher as i have already mentioned in those respective points.

## MIS Reports / Dashboards:

Here we can learn about some important MIS reports and dashboards with examples.

This report conveys the number of people working in the respective team or in the organisation. HR will have this head count report for entire company as he/ she is responsible to maintain this report. If the team size is bigger more than 50 people approximately. Usually MIS Executive or HR prepares this report on monthly basis as they have to know the number people in the respective team or in the company. Now we can learn how to prepare this report. At first you need to have the all the employee details in the one worksheet. Like First Name, Last Name, Email address, Reporting manager, Joining date etc. As shown in the below

In the above list, whenever employee leaving the organization or team change, we need to capture. Then we can find the opening balance for the next month, closing balance for the current month and department wise number of employees.

Every month end you have to prepare this report, then you will come to know the exact head count on monthly. When you do this, you also find the attrition percentage. This is confidential report.

### Procure to pay report ( P2P Report ):

This report is mainly for vendor payments and invoice ageing status. When we are producing one product, we require some raw materiel to produce. or if we have to provide some service also we require some things in order to provide the service. To provide the service or raw material, we have to pay amount to vendors. At that point of time, this procure to pay concept comes into the place. In order to pay the amount, we will have some time and dead line. we prepare this report to know about the vendor payments mainly. We will have some service level agreement to pay the amount.

Example:

when we need to pay some amount to the vendors, we will have some duration to pay that. Hence we must maintain one report for calculating the time on day to day basis. In order to do this process, we will have the invoice copies. These are classified as purchase order, No purchase order, Travel & Expenses as such. In this case No purchase order to process, value of the purchase order payments to be captured. This report will have the below tabs ( Sheets) approximately. Similar way various business models will have their respective tabs.

Summary Dashboard sheet can be shown as in the below manner. This is just an example to understand by yourself. You can prepare the same in your own manner based on your client requirement.

SLA Report sheet has the ageing like  0-3 days, 4-7 days, 8-14 days, >14 days. Using the excel formulas, we can pull the number of days into the above ageing columns as shown in the below

Similar way we can show the Purchase order, No Purchase order, Freight, Travel and Expenses summary in different sheets. Once we prepare these sheets, we can show one consolidated worksheet to for all the overall information. Then we have to prepare the payments sheet, to show the payment information for the vendors as on that particular day.

Hope you understand the Procure to pay report in detailed.

1. You will not get continues flow of work. Actually speaking bit lesser work most of the times. Because 100% Accuracy required in this job. 4 hours of the work would be assigned in the 8 hours of job time.
2. Typing work would be very lesser comparing to other non voice jobs.
3. It would be very interesting and enjoyable, when you are dealing the challenges with Excel formulas or helping others to solve their excel template issues
4. Most of the other team members would respect the MIS Executive, because this job role would support to the team as I explained in the above point.
5. Once this job gets habituated with all the assigned tasks, it would be cake walk for working in daily.
6. Managers would rely on the MIS Executive’s report numbers to take their crucial decisions in the business
7. Many team members would try to catch you all the time to learn excel tricks from MIS Executive
8. This job role would circulate reports to top level management hence MIS Executive will have the good connectivity with top level management like BU Heads.
9. This job role will even send the reports to clients. Hence even in client location people also gives the respect to this job role. Since they will also send requests to prepare the Ad hoc reports
10. By doing all these activities, usually MIS Executive would become an subject matter expert in their respective team activities.

1. MIS guy would rely on production team member’s inputs to prepare the report, dashboards. This would little bit hectic process due to some reason, when they are unable to send the inputs, Report circulation would be delayed. Managers would ask the MIS guy for the report as they would like to know the process current situation.
2. Whenever clients come with ad hoc reports requirement, it would be an urgent requirement. MIS guy should prepare the daily reports as well as this ad hoc report for the client. That particular time, this MIS Executive would face a problem to work in fast pace mode. In the Workforce management team, MIS Executive has the always tasks in the queue to do.
3. Sometimes production would update the numbers incorrectly and the same would be taken by MIS guy and getting errors in the report after next day. That time it would be bit difficult to prove that he has done correctly.
4. Payroll team MIS job also bit difficult to do because it is almost similar to bank job. Every paisa should be accountable while preparing the payroll salary report or any other report in the payroll team.

Based on the above points, you can understand MIS Job would be enjoyable also hectic task sometimes. Hence MIS executive should learn how to play the safe game in the job role while dealing with the production team or any other top level management.

# Index Formula

0

Index formula is equal to Vlookup and HLookup. Both the formulas capability was included in the index formula. Vlookup has a drawback like you can not pull left side value. But using this Index formula you can overcome that problem.

Index formula syntax:

=Index(Array,rownum,columnnum)

within the index if you include the match function. Then this formula is stronger and best formula in the excel. Match formula can be used within the rownum or columnnum place in the above syntax. When you need to find the particular column within the data data set, then we have to use index match combination.

Index formula in reports

This can be very useful to prepare the SLA report for the project.

you can keep the data sheet hidden and pick the hidden values to display sheet in the SLA dashboard. Then keep the conditional formatting to show the red, amber and green for KPIs (Key performance indicator) and CPIs( Critical performance indicator)

Index formula is known as advanced excel formula. Since it

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.

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.

## 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.

78

# 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

## 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.

## 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.

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.

666

# 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.

## 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.

## 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 different Sheet as shown in the below

## 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

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

## 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:

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:

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

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

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.

126

# List Of All Excel Formulas or Functions

### 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
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
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
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