# Trending December 2023 # Linest In Excel (Formula, Examples) # Suggested January 2024 # Top 14 Popular

You are reading the article Linest In Excel (Formula, Examples) updated in December 2023 on the website Daihoichemgio.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 Linest In Excel (Formula, Examples)

LINEST in Excel

Linest function in excel is a statistical function used to calculate straight-line statistics and return an array from the available selected data, which also describes that line. In other words, the Linest function calculates the statistics of a simple line equation (Y = mx + C) which also explains the relationship between the dependent and independent variables using the least square procedure to find the best solution for the data used.

Excel functions, formula, charts, formatting creating excel dashboard & others

LINEST Formula in Excel

Below is the LINEST Formula in Excel :

LINEST Function in Excel includes the following parameters:

known_y’s: The known y’s is n range or array of y values from the line equation.

known_x’s: The known x’s is a range or array of x values from the line equation. If this x value is null, excel will assume those x_values as 1,2,3..with the same number of values as y values.

const: The const is a logical value that specifies either “TRUE” or “ FALSE”.

stats: The stat is a logical value that specifies either to return additional regression statistics, i.e. “TRUE” or “FALSE”, which function needs to return the statistics on the line of best fit.

Steps to Use the LINEST Function in Excel

How to Use the LINEST Function in Excel?

LINEST Function in Excel is very simple and easy to use. Let us understand the working of the LINEST Function in Excel by some LINEST Formula example.

You can download this LINEST?Function Excel Template here – LINEST?Function Excel Template

Example #1

To use the LINEST as an array formula then, you need to do the following steps :

Select the cell where the function is and press f2.

Press CTRL +SHIFT +ENTER.

In this LINEST Function in Excel example, we will see how the LINEST function works with the data. Enter the data in Excel with two data captions named X and Y.

In order to use the LINEST function to find the exact result, Go to Formulas and choose the More function. Choose the LINEST Function under the statistical category, as shown below.

Choose the LINEST function, and you will get the below dialog box as shown below:

You will get the same value which is the coefficient m in the equation y=mx+b

So the result will be :

As mentioned above, we must press CTRL+SHIFT+ENTER to get the exact data. Now we can see that formula is enclosed with two parentheses, i.e. { } where the LINEST function is evaluated.

We can mention a straight line with slope and y-intercept. In order to get the intercept and slope regression, we can use the LINEST function lets see an example with step by step procedure.

Example #2

In this example, we will see how to use the LINEST function in Excel. This function is used to calculate the line of Coefficient.

Line Equation: Y=mx+c

Using LINEST Function in Excel, we are going to calculate:

A line of Best Fit gradient

A line of best-fit intercept

The standard error of the gradient

The standard error of the intercept

R2

Regression Sum of squares

Residual sum of squares.

Consider the below data, which has X1 and Y1 values shown below:

To calculate the above equation, select the cell and insert the LINEST function shown below.

Use CTRL+SHIFT+ENTER to get all values where we can see the formula contains open and closing parenthesis.

Let’s see the same data how we can derive the same equation in a chart :

So that scatter chart graph will be displayed with the selected x and y data.

Now we will add a trend line to show exactly by selecting the scattered graph below.

Once you select the option “Add Trendline,” a new trend line will be added in the chart, as shown below.

It shows various statistical parameters like exponential, Liner, Logarithmic, and polynomial.

Here choose the polynomial option with an order 2, as shown below screenshot.

Scroll down and check to mark the display equation on the chart and display an R-Square value in the chart.

So the equation has been displayed in the chart as shown below with the same line equation.

Example #3 – LINEST Functioning for Multiple Range of X Values:

Consider the below example, which has the same X1 and Y data and X2 values.

The following chart has been evaluated by using the scattered graph by adding a trend line function.

Assume the equation for Y=b+m1*X1+m2*X2

Lines Function : LINEST (Known_y’s,[Known_X’s],[const],[stats])

Consider the below array of a table which denotes as follows:

Where

m1 – denotes X

m2- denotes X2

Const- denotes b

In the earlier version, the LINEST function is used as a formula that is not correct to find the total sum of squares if the third argument to the LINEST function is set to false, and this causes an invalid value for the regression sum of squares. Also, values are incorrect for the other output sum of squares. The collinearity value caused a round of error, standard errors of regression coefficient that are not given exact results, and degrees of freedom that are not appropriate.

In Excel 2003 LINEST function has been improved and given good results by adding the TREND function to make it appropriate.

Things to Remember

The LINEST function in Excel should be used with appropriate values; if not, we will not get the exact result.

The LINEST function in Excel will not work If the array of Known_x’s is not as same as the array of Known_y’s.

Recommended Articles

This has been a guide to the LINEST in Excel. Here we discuss the LINEST Formula in Excel and How to use the LINEST Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

You're reading Linest In Excel (Formula, Examples)

## Dcount In Excel (Formula, Examples)

Excel functions, formula, charts, formatting creating excel dashboard & others

DCOUNT in Excel

The DCount function in Excel is a Database type function which is used to count the cell number, which contains only numbers or numerical values. The selected range for Database in the syntax can be a table or column. Fields part is optional, but here we can select the column whose value we need to count and then for Criteria part range those cell which has criteria. We need to mention the criteria in separate cells by which want cell count.

DCOUNT Formula in Excel

Below is the DCOUNT Formula in Excel :

Below is the component of the DCOUNT function in Excel:

Database: The Range of cells that the user selected for applying criteria against. The field name must be there in the database. It is a mandatory field.

Field: The column name or the column number which tell excel which column in the database to count. It will be from the database only. It can be a field number / Index number. It can be omitted the result will be calculated on satisfying criteria.

Criteria: The range of cells which contain the conditions. The user specifies it. The criteria must contain at least one field name. Criteria can include multiple ranges as the condition specified by the user.

Note: The DCOUNT function is not case-sensitive. So, if the user provides data opposite case to the database, it will not affect the result of the function.

How to Use the DCOUNT Function in Excel?

You can download this DCOUNT Function Excel Template here – DCOUNT Function Excel Template

Example #1

A user wants to calculate the total order made after Order id 11450 for Coke.

Pre-requisite: There is a list of order Id’s, Beverage Name and Expense on a beverage made by some customer. Criteria are written for the same on the top row for which the user wants to count total order.

Step 1: Select the G2 cell and write the DCOUNT formula =DCOUNT

Step 2: Select the database, which is first asked in function, so select B4 to D10.

Step 3: Now enter the field value or column name for which a user wants to count all cell.

Step 4: Enter the criteria for which the user wants to apply to the database.

Summary of Example #1:

A user can modify the criteria and can fetch data from the database according to his criteria given to the DCOUNT function in a fraction of seconds.

Example #2

A user wants to calculate the total Product ID, which is Green in color and Price greater than 5 lakhs.

Pre-requisite: There is a list of Product Id, Color description, per item price, quantity and price tag on the product purchased by a customer. Criteria are written for the same on the top row for which the user wants to count total items.

Step 1: Select the I2 cell and write the DCOUNT formula =DCOUNT

Step 3: Now enter the field value or column name for which a user wants to count all cell.

Step 4: Enter the criteria for which the user wants to apply to the database.

Summary of Example #2:

A user can modify the criteria and can fetch data from the database according to his criteria given to the DCOUNT function in a fraction of seconds.

Example #3

A user wants to calculate the total employee with a salary greater than 6 Lakhs and older than 30 years or whose name starts with the alphabet M and younger than 21 Year.

Pre-requisite: A user has data for some XYZ Company employee data, which have field name like First Name, age, salary and joining date.

Criteria are written for the same on the top row for which the user wants to count total items.

Step 2: Select the entire database, which is first asked in the DCOUNT function, so select B5 to E17.

Step 3: Now DCOUNT function will ask for a field name which we can omit, so a user can skip the field name value and just put a sing comma (,).

Step 4: Enter the criteria for which user wants to apply on the database, which written on top of the database.

Summary of Example #3:

The user wants to calculate the total employee with a salary greater than 6 Lakhs and older than 30 years or whose name starts with the alphabet M and younger than 21 Year. The same result is displaying in the resultant cell.

A user can modify the criteria and can fetch data from the database according to his criteria given to the DCOUNT function in a fraction of seconds.

A different way to use the DCOUNT function in Excel:

Field by Name: As we have used in the above example, we have given the field a name.

Field by Index: A user can give an index name or just a column number.

Field by Omitted: A user can omit the field value.

Things to Remember about DCOUNT Function in Excel

DCOUNT function in excel will count only if the user giving non-blank or numeric value. If the field value is blank or text value, then it will throw an Invalid Name Error.

Criteria can have multiple rows in the DCOUNT function in excel.

The database and criteria must have the same matching header; otherwise, it will not execute the function.

The field value can be Name in double quotes (” Name”) or field value index number.

If criteria in function left blank, then it will throw a #Value error.

The criteria can be put anywhere in the active sheet. Best to put on top of the database.

Always make sure the database and criteria or not overlap; there should be at least one-row gap in between.

Recommended Articles

This has been a guide to DCOUNT in Excel. Here we discuss the DCOUNT Formula in Excel and How to use the DCOUNT Function in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

## Frequency In Excel (Formula, Examples)

FREQUENCY in Excel

The frequency function in Excel is used to calculate how many times a number is getting repeated, in other words, we can say it calculates the occurrence of any number from the selected range of the bin table. If we see the syntax of the Frequency function, we will have a data array and bin array, whereas the data array is the complete range of data available, and the bin array is the number whose frequency we need to calculate.

Excel functions, formula, charts, formatting creating excel dashboard & others

What is the FREQUENCY function in Excel?

The FREQUENCY function returns how often values occur within a set of data. It returns a vertical array of numbers.

If you have age data for a group of children, you want to count how many children fall under different age ranges. Or you have the data of a school student and want to see how many students have achieved a grade of ‘A’, ‘B’, ‘C’ etc. For the above problems, we could use the FREQUENCY function in Excel.

FREQUENCY Formula in Excel

The FREQUENCY Function has two arguments as below:

Data_array – An array or set of values for which you want to count frequencies.

Bins_array – An array of intervals (“bins”) for grouping values.

FREQUENCY counts how many times values occur in a dataset. This function has a special characteristic, and its usage differs from other formulas. While applying the FREQUENCY Function within a cell, type the formula and press CTRL+ SHIFT+ ENTER. This creates {} brackets around the formula, which Excel interprets as an array formula. If you simply press ENTER key, then this may return an incorrect result.

As FREQUENCY Function comes under the Statistical functions category, it is found under the FORMULAS tab. Please follow the below steps:

Select the More Functions option.

Please select the FREQUENCY Function from the drop-down listed item per the screenshot below.

Enter the Data_array, for which you want to count the frequencies.

Enter the Bin_array, for which you want to group the values in data_array.

How to Use FREQUENCY Function in Excel?

The FREQUENCY Function in Excel is very simple and easy to use. Let us understand the working of the FREQUENCY Function in Excel by some FREQUENCY Formula examples.

You can download this FREQUENCY Function Excel Template here – FREQUENCY Function Excel Template

Example #1

We have below some test scores:

According to the below intervals, we will apply the FREQUENCY formula.

The interval field is the second argument of the FREQUENCY Function Bins_array, which specifies the maximum values for the score ranges. This means the score is to be split into 0—60, 61-70, 71-80, 81-90 and 90+.

First, select the cells where we want to apply the FREQUENCY formula.

The Excel formula {=FREQUENCY(B4:B12, D4:D8)} into cells E4:E8 using CTRL+SHIFT+ENTER to complete the formula. This would give a vertical array with 5 values, as below screenshot:

The final result is shown below:

Explanation:

The first value will display in cell E4. The result would be 1 because only 1 test score <=60.

The second value is 3 because there are 3 test scores between 61 and 70.

The third value is 1 because there is only 1 test score between 71 and 80.

The Fourth value is 4 because there is 4 test score between 81 and 90.

Score 90+; there is no value.

Example #2

We can apply the FREQUENCY Function to decimal values also. Let’s take an example of the heights of 11 children (in centimeters).

0.00-1.25 meter

1.26-2.75 meter

2.76-4.25 meter

4.26-5.75 meter

Over 5.76 meter

As shown in the above screenshot, the Formula used for the FREQUENCY distribution is:

{=FREQUENCY(A18:A28,B18:B21)}

The final result is :

Things to Remember

First, you need to highlight or select the range of cells for the result of applying an array formula.

Type the FREQUENCY Function into the first cell of the highlight range and press CTRL+SHIFT+ENTER.

FREQUENCY Function in Excel returns an array of values; it must be entered as an array formula.

Entering an array formula into too many cells can cause errors like #N/A. Only use the formula in the appropriate cells to avoid delays.

Recommended Articles

This has been a guide to FREQUENCY Function in Excel. Here we discuss the FREQUENCY Formula in Excel and how to use the FREQUENCY Function in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

## Meaning, Types, Curve, Formula, Examples

What is Marginal Utility?

The marginal utility determines the shift (rise/fall) in customer satisfaction as they consume additional units of a product/service.

For example, a consumer eats a donut for the first time. It tastes fantastic, and they try another. However, eating another isn’t as satisfying as the first or second. Therefore, in terms of marginal utility, we can say that with every extra piece of donut, the consumer has, the utility (craving) for the food item decreases.

It is an economic concept that measures the utility of the last unit consumed. The idea says that the more of a product one has, the less valuable it becomes. As we use more of a good/service, we get less utility from every additional unit.

Start Your Free Investment Banking Course

Key Takeaways

It is the benefit a consumer derives from consuming one more unit of a good or service.

This theory is also known as the law of diminishing marginal utility. It states that as we consume goods, we derive less and less satisfaction from each additional unit consumed.

To calculate, divide the total utility change by the total units consumed.

One can classify it into three types: positive, negative, and neutral. Positive utility means increasing utility with each added unit, while negative means vice versa. Neutral is when the extra quantity does not affect its utility.

History

The foundation of this concept arose when economists in the 19th century were trying to figure out how to measure a product’s value. The originating theory was that one could determine a product’s value only by its utility and customer satisfaction.

When economists further studied this notion, they arrived at the paradox of diamond and water. Which states that even though water is the basis of life and of high importance to human life, diamonds are more valuable.

The economists then introduced the marginal utility per the scarcity principle; the more scarce the product, the more valuable each additional unit is. In this case, a diamond’s value increases with increasing units, whereas water does not. The same goes for any other commodity.

Finally, in the 20th century, the concept became well-known to world economists.

How Does Marginal Utility Work?

It refers to the subjective value a consumer assigns to a good/product consumed. Consumers place different values on each unit, which means their preferences change depending on what they have already consumed.

As we start with fewer units of something, the utility may be higher than when we begin with more pieces. For example, when one is thirsty and has no water, they place a high value on one gallon of water. On the other hand, if someone is slightly dehydrated and has plenty of water, they might not care about getting another gallon of water.

It is usually the difference between total and average utility. Moreover, it can be applied to any product or service and helps us understand how people’s preferences change as they consume more of something.

Law of Diminishing Marginal Utility

When we spend money to buy additional units of a good, there is a DMU.

According to it, each additional unit provides less utility than the previous one, regardless of the amount per unit increases. Simply put, as the quantity of a good/service consumed increases, its utility decreases.

For example, buying a can of milk during necessity assigns it higher utility. However, purchasing another can of milk when it is already available decreases its value.

Essentially, this law dictates that we need less of a good over time because we eventually become accustomed to it.

One can illustrate this law using graphs where we plot the total satisfaction on the y-axis against the quantity consumed on the x-axis. The slope is negative until it eventually becomes vertical as consumption increases without bounds.

Law of Equi Marginal Utility Explanation

The fundamental idea about the law of equi-marginal utility is to derive maximum satisfaction through marginal utility.

Generally, it states that consumers must allocate their income among various goods to derive equal utility.

Suppose a student with \$100 has to buy school supplies. They can gain maximum satisfaction if they distribute the amount in books, stationery, backpack, etc.

Assumptions

The price of the product/service and the consumer’s income are established and will not change.

The consumer requires multiple goods/services, understands their utility, and wants to maximize satisfaction.

Each product’s usefulness is quantifiable using numbers without fractions or decimals (1, 2, 3, etc.).

Limitations

For most commodities, quantifying utility in cardinal numbers can be difficult.

If the prices are subject to change regularly or if the consumer’s income is meager, it can pose an objection to the law.

It is not a suitable measure for knowledge gain.

Marginal Utility Formula

Marginal Utility = Change in Total Utility (TU) / Change in Total consumed units(Q)

(OR)

Marginal Utility = (TUf – TUi) / (Qf – Qi)

Where,

TUi and TUf are the total usefulness of Qi and Qf Units, respectively.

Qi and Qf are the total consumption initially and finally, respectively.

How to Calculate Marginal Utility – Examples

You can download this Marginal Utility Excel Template here – Marginal Utility Excel Template

Example 1

A customer purchases five chips packets and records the total utility after consuming each pack as follows,

We calculate the marginal utility for each chip packet as follows,

This graph represents the comparison between the total and marginal utility. It shows that with each additional unit, total utility increases while marginal decreases.

Example 2

Ms. Z buys three units of a good and reports the total utility as 40. However, sometime later, she purchased additional four units and said the final utility was 65. Calculate the marginal utility of the product.

Given,

Let us calculate the change in total utility and consumed units,

Implementing the formula,

The marginal utility for goods consumed is 6.

Types 1. Positive or Growing

It is when consuming an additional unit raises its marginal value. The more a consumer acquires the product, the more interested and satisfied they are with it.

For instance, the more educational books one has, the more utility it produces.

2. Lowering or Negative

Decreasing utility occurs when the utility of each unit decreases with additional consumption. It is also known as diminishing marginal utility.

For example, if you’re thirsty on a hot day, the first glass of water will provide much greater satisfaction than your second. The last sip will be incredibly unsatisfying if you continue drinking until you’re full.

3. Neutral

It is when the consumption of additional units does not affect the product’s value; it remains constant.

For example, a person can buy any number of clothes but never feel less or more satisfied with them. As a necessity, they are always on the neutral side.

Marginal Utility and Total Utility

Marginal Utility

The product’s value concerning total units determines whether the use of each unit from first to last rises or falls.

It is called marginal because the utility provided by each unit decreases as the number of units increases.

The graph for this begins at the same level as a total utility but then has a downward slope.

Total Utility

It is the contentment a person experiences after consuming a certain quantity of one or more products.

In this case, the utility also rises as a product’s unit count rises.

The graph representation for total utility starts lower and then has an upward curve.

Marginal Utility Curve

The marginal utility curve represents the optimal quantity of a product an individual wants to consume. It derives from the law of diminishing returns, which states that with more units consumed, the satisfaction gained with each additional unit decreases.

The curve demonstrates this by showing how total satisfaction increases at an increasing rate up until a certain point when it levels off or even dips. The shape of the curve can be convex, concave, and linear.

A convex curve is similar to a U. The satisfaction increases with consumption upto a point and eventually decreases. For example, we may enjoy eating one cookie but not four.

A concave curve looks like an upside-down U. The satisfaction starts high while consumption is at zero but then declines as consumption increases. Moreover, it reaches a point where satisfaction is lowest and then begins to increase again.

The linear curve has no curvature, and its slope is constant over the entire range. It plots the curve for everyday goods with an income elasticity greater than 1, i.e., their demand increases as people’s incomes increase. For example, food and clothing are both common goods.

It is a central concept in microeconomics that determines the total goods an individual can/will consume

Companies use this measure to gauge the product satisfaction of second-time consumers.

This concept allows scientific evaluation of consumer perception shifts regarding satisfaction levels.

The curve only applies to those who have already reached a certain level of satisfaction.

It assumes people will always want more rather than be satisfied with what they have.

It is a qualitative and assumption-based metric. Hence, it can be challenging to measure. People have different preferences, so they may not share their preferences’ intensity.

Calculator

Total Units Consumed Initially (Qi) Total Units Consumed Finally (Qf) Total Utility for Initial Units (TUi) Total Utility for Final Units (TUf) Marginal Utility =   Marginal Utility = =(

0

0

) / (

0

0

) =

0

Answer: Marginal utility, in economics, says that the value of an additional unit of a product/service differs from the value of the previous unit. It typically measures how much satisfaction one can get from consuming another unit of a good/service.

Q2. Define the diminishing marginal utility law.

Answer: It generally has three types: positive, negative, and neutral. The negative one is also known as DMU. The law of DMU states that its usefulness will decline with an increase in total units consumed.

Q3. What is the marginal utility of money?

Answer: Also known as the marginal utility of income, it claims that the utility law applies not only to commodities/services but also to people’s income. As one’s income grows, their satisfaction level will improve, but it is not constant. At a certain point, any income growth would not bring any additional satisfaction to the person.

Q4. What is the importance of marginal utility?

Answer: It is a tool that can help consumers regulate spending to receive maximum utility from various products. Consumers can use this measure when deciding whether or not it is worth purchasing additional units.

Recommended Articles

## Ipmt In Excel How To Use Ipmt Function In Excel? (With Examples)

IPMT Function in Excel

IPMT Function calculates a specific portion of interest based on the loan amount and tenure. The syntax of IPMT is quite similar to the syntax of PV Function in Excel, which all have seen earlier. To understand better, IPMT helps used to distinguish between different portions or segments of any loan and to what time how small amount is to be paid based on the interest applicable can be calculated.

IPMT Formula in Excel:

Excel functions, formula, charts, formatting creating excel dashboard & others

Explanation of IPMT Function in Excel

There are six parameters used for the IPMT function. Four parameters are compulsory, and two are optional.

Parameter details are as follows:

Compulsory Parameters:

Rate: The interest rate per period.

Per: The period for which you want to find the interest and must be in the range 1 to n per.

Nper: The total number of payment periods in an annuity.

Pv: The present value, or the lump-sum amount that a series of future payments is worth right now.

Optional Parameters:

[FV]: It is an optional argument. The FV or a cash balance you want to attain after the last payment is made. If FV is omitted, excel assumes it to be 0 (the future value of a loan, for example, is 0).

[Type]: This is also an optional argument. The number 0 or 1 indicates when payments are due. If this argument is omitted, Excel assumes it to be 0.

The Type can be 0 or 1, where:

1 = The payment is made at the start of the period.

How to Use the IPMT Function in Excel?

IPMT function in Excel can be used as a worksheet function and a VBA Function. Here are some examples of the IPMT functions to understand the working of the IPMT function in Excel.

You can download this IPMT Function in Excel Template here – IPMT Function in Excel Template

Example #1

Interest payment made for months 1 and 2 of a loan of \$70,000, which is to be paid after 6 years. An interest rate of 6% per year, and the payment to the loan is to be made at the end of each month.

Result is :

To convert the annual interest rate of 6% into the monthly rate (=6%/12) and the number of periods from years to months (=6*12).

Example #2

Interest during quarters 1 and 2 of an investment is required to increase investment from \$0 to \$6,000 over 3 years. The interest rate of 4.5% per year, and the payment into the investment is to be made at the beginning of each quarter.

Result is :

The annual interest rate has been converted into a quarterly rate (4.5%/4)

The number of periods has been converted from years to quarters (=3*4).

The [type]argument has been set to 1 to indicate that the payment will be made at the start of each quarter.

The interest for the first quarter is zero, as the first payment is made at the start of the quarter.

Note:

* Use N%/12 for rate and N*12 for nper when there are monthly payments. N%/4 for rate and N*4 for nper when quarterly payments and N% for rate and N for nper when there are annual payments.

*Cash paid out is shown as negative numbers. Cash received is shown as positive numbers.

Things to Remember

Below are a few error details that can come in the IPMT function as the wrong argument will be passed in the functions.

2. Error handling #VALUE!: IPMT function through a #VALUE! Error when any non-numeric.

VBA Function Example:

The IPMT function can also be used in VBA code.

For example:

Recommended Articles

This has been a guide to IPMT in Excel. Here we discuss the IPMT Formula in Excel and how to use the IPMT function in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

## Formula, Examples, How To Use Text Function

TEXT Function

Written by

CFI Team

Published June 18, 2023

Updated July 7, 2023

What is the Excel TEXT Function? Formula

=Text(Value, format_text)

Where:

Value is the numerical value that we need to convert to text

Format_text is the format we want to apply

When is the Excel TEXT Function required?

We use the TEXT function in the following circumstances:

When we want to display dates in a specified format

When we wish to display numbers in a specified format or in a more legible way

When we wish to combine numbers with text or characters

Examples 1. Basic example – Excel Text Function

With the following data, I need to convert the data to “d mmmm, yyyy” format. When we insert the text function, the result would look as follows:

2. Using Excel TEXT with other functions

We use the old price and the discount given in cells A5 and B5. The quantity is given in C5. We wish to show some text along with the calculations. We wish to display the information as follows:

The final price is \$xxx

Where xxx would be the price in \$ terms.

For this, we can use the formula:

=”The final price is “&TEXT(A5*B5*C5, “\$###,###.00”)

The other way to do it by using the CONCATENATE function as shown below:

3. Combining the text given with data using TEXT function

When I use the date formula, I would get the result below:

Now, if we try to combine today’s date using CONCATENATE, Excel would give a weird result as shown below:

What happened here was that dates that are stored as numbers by Excel were returned as numbers when the CONCATENATE function is used.

How to fix it?

To fix it, we need to use the Excel TEXT function. The formula to be used would be:

4. Adding zeros before numbers with variable lengths

We all know any zero’s added before numbers are automatically removed by Excel. However, if we need to keep those zeros then the TEXT function comes handy.  Let’s see an example to understand how to use this function.

We are given a 9-digit product code, but Excel removed the zeros before it. We can use TEXT as shown below and convert the product code into a 9-digit number:

In the above formula, we are given the format code containing 9-digit zeros, where the number of zeros is equal to the number of digits we wish to display.

5. Converting telephone numbers to a specific format

If we wish to do the same for telephone numbers, it would involve the use of dashes and parentheses in format codes.

Here, I want to ensure the country code comes in brackets (). Hence the formula used is (##) ### ### ###. The # is the number of digits we wish to use.

Format Code

It is quite easy to use TEXT Function in Excel but it works only when the correct format code is provided. Some frequently used format codes include:

CodeDescriptionExample

# (hash)It does not display extra zeros#.# displays a single decimal point. If we input 5.618, it will display 5.6.

0 (zero)It displays insignificant zeros#.000 would always display 3 decimals after the number. So if we input 5.68, it will display 5.680.

, (comma)It is a thousand separator###,### would put a thousands separator. So if we input 259890, it will display 259,890.

If the Excel TEXT function isn’t working

Sometimes, the TEXT function will give an error “#NAME?”. This happens when we skip the quotation marks around the format code.

Let’s take an example to understand this.

If we input the formula =TEXT(A2, mm-dd-yy). It would give an error because the formula is incorrect and should be written this way: =TEXT(A2,”mm-dd-yy”).

Tips

The data converted into text cannot be used for calculations. If needed, we should keep the original data in a hidden format and use it for other formulas.

The characters that can be included in the format code are:

+ Plus sign

-Minus sign

()Parenthesis

:Colon

{}Curly brackets

=Equal sign

~Tilde

/Forward slash

!Exclamation mark

Less than and greater than

TEXT function is language-specific. It requires the use of region-specific date and time format codes.

Free Excel Course

Check out our Free Excel Crash Course and work your way toward becoming an expert financial analyst. Learn how to use Excel functions and create sophisticated financial analysis and financial modeling.