Ms excel: if function (WS)

MS Excel: LINEST Function (WS)

This Excel tutorial explains how to use the Excel LINEST function with syntax and examples.

Description

The Microsoft Excel LINEST function uses the least squares method to calculate the statistics for a straight line and returns an array describing that line. The LINEST function uses the following line equation:

y = mx + b (for a single range of x values)

y = m1x1 + m2x2 + ... + b (for multiple ranges of x values)

Syntax

The syntax for the LINEST function in Microsoft Excel is:

LINEST( y_values, [x_values], [constant], [additional_statistics] )

Parameters or Arguments

y_values

The known set of "y values" from the line equation.

x_values

Optional. It is the known set of "x values" from the line equation. If this parameter is omitted, x_values is assumed to be {1,2,3,...} with the same number of values as y_values.

constant

Optional. It is either TRUE or FALSE. If this parameter is omitted, the function will assume TRUE. If constant is TRUE, b in the line equation is calculated normally. If constant is FALSE, b becomes 0 so that the line equation is evalulated as y = mx.

additional_statistics

Optional. It is either TRUE or FALSE. If this parameter is omitted, the function will assume FALSE. If additional_statistics is TRUE, the function will return additional regression statistics. If additional_statistics is FALSE, the function will returns only m coefficients and b constant.

Applies To

The LINEST function can be used in the following versions of Microsoft Excel:

• 
  Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
  

Type of Excel Function

The LINEST function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  

Example (as Worksheet Function)

Let's look at some Excel LINEST function examples and explore how to use the LINEST function as a worksheet function in Microsoft Excel:

This first example we have entered the y values in column A (cells A2 through A6) and the x values in column B (cells B2 through B6). Then we have entered the following LINEST function in both cells D2 and E2 as follows:

=LINEST(A2:A6,B2:B6,TRUE,FALSE)

Result: 0.5

The LINEST function can be written as an array formula. When creating your array formula, you need to do the following:

1. 
   Enter the formulas in both cells D2 and E2
   
2. 
   Highlight cells D2 and E2
   
3. 
   Press F2 (in Windows) or CONTROL+U (on Mac)
   
4. 
   Press Ctrl+Shift+Enter
   

This creates {} brackets around your formulas as follows:

{=LINEST(A2:A6,B2:B6,TRUE,FALSE)}

Result: 0.5

You can see how the formula look in the images below.

The value in cell D2 returns the slope of 0.5 and the value in cell E2 returns the y-intercept of 0.

This formula could also be written as follows (entering values instead of cell ranges):

=LINEST({2,3,5,7,10},{4,6,10,14,20},TRUE,FALSE)

Result: 0.5

The LINEST function can be written as an array formula. When creating your array formula, you need to do the following:

1. 
   Enter the formulas in both cells D2 and E2
   
2. 
   Highlight cells D2 and E2
   
3. 
   Press F2 (in Windows) or CONTROL+U (on Mac)
   
4. 
   Press Ctrl+Shift+Enter
   

This creates {} brackets around your formulas as follows:

{=LINEST({2,3,5,7,10},{4,6,10,14,20},TRUE,FALSE)}

Result: 0.5

MS Excel: PMT Function (WS, VBA)

This Excel tutorial explains how to use the Excel PMT function with syntax and examples.

Description

The Microsoft Excel PMT function returns the payment amount for a loan based on an interest rate and a constant payment schedule.

Syntax

The syntax for the PMT function in Microsoft Excel is:

PMT( interest_rate, number_payments, PV, [FV], [Type] )

Parameters or Arguments

interest_rate

The interest rate for the loan.

number_payments

The number of payments for the loan.

PV

The present value or principal of the loan.

FV

Optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

Optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

Value	Explanation
0	Payments are due at the end of the period. (default)
1	Payments are due at the beginning of the period.

Applies To

The PMT function can be used in the following versions of Microsoft Excel:

• 
  Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
  

Type of Excel Function

The PMT function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  
• 
  VBA function (VBA)
  

Example (as Worksheet Function)

Let's look at some PMT examples and explore how to use the PMT function as a worksheet function in Microsoft Excel:

This first example returns the monthly payment on a $5,000 loan at an annual rate of 7.5%. The loan is paid off in 2 years (ie: 2 x 12). All payments are made at the beginning of the period.

=PMT(7.5%/12, 2*12, 5000, 0, 1)

Result: -$223.60

This next example returns the weekly payment on a $8,000 loan at an annual rate of 6%. The loan is paid off in 4 years (ie: 4 x 52). All payments are made at the end of the period.

=PMT(6%/52, 4*52, 8000, 0, 0)

Result: -$43.28

This next example returns the annual payment on a $6,500 loan at an annual rate of 5.25%. The loan is paid off in 10 years (ie: 10 x 1). All payments are made at the end of the period.

=PMT(5.25%/1, 10*1, 6500, 0, 0)

Result: -$852.03

This final example returns the monthly payment on a $5,000 loan at an annual rate of 8%. The loan is paid on for 3 years (ie: 3 x 12) with a remaining balance on the loan of $1,000 after the 3 years. All payments are made at the end of the period.

=PMT(8%/12, 3*12, 5000, -1000, 0)

Result: -$132.01

MS Excel: SLN Function (WS, VBA)

This Excel tutorial explains how to use the Excel SLN function with syntax and examples.

Description

The Microsoft Excel SLN function returns the depreciation of an asset for a period based on the straight-line depreciation method.

Syntax

The syntax for the SLN function in Microsoft Excel is:

SLN( cost, salvage, life )

Parameters or Arguments

cost

The original cost of the asset.

salvage

The salvage value after the asset has been fully depreciated.

life

The useful life of the asset or the number of periods that you will be depreciating the asset.

Applies To

The SLN function can be used in the following versions of Microsoft Excel:

• 
  Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
  

Type of Excel Function

The SLN function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  
• 
  VBA function (VBA)
  

Example (as Worksheet Function)

Let's look at some SLN examples and explore how to use the SLN function as a worksheet function in Microsoft Excel:

This first example returns the depreciation for an asset that costs $10,000, with a salvage value of $5,000. The useful life of the asset is 5 years.

=SLN(10000, 5000, 5)

Result: $1,000.00

This next example returns the depreciation for an asset that costs $8,000, with a salvage value of $0. The useful life of the asset is 7 years.

=SLN(8000, 0, 7)

Result: $1,142.86

Example (as VBA Function)

The SLN function can also be used in VBA code in Microsoft Excel.

Let's look at some Excel SLN function examples and explore how to use the SLN function in Excel VBA code:

Dim LValue As Double

LValue = SLN(8000, 0, 7)

In this example, the variable called LValue would now contain the value of 1142.857143.

MS Excel: DATEDIF Function (WS)

This Excel tutorial explains how to use the Excel DATEDIF function with syntax and examples.

Description

The Microsoft Excel DATEDIF function returns the difference between two date values, based on the interval specified.

Syntax

The syntax for the DATEDIF function in Microsoft Excel is:

DATEDIF( start_date, end_date, interval )

Parameters or Arguments

start_date and end_date

The two dates to calculate the difference between.

interval

The interval of time to use to calculate the difference between date1 and date2. Below is a list of valid interval values.

Interval	Explanation
Y	The number of complete years.
M	The number of complete months.
D	The number of days.
MD	The difference between the days (months and years are ignored).
YM	The difference between the months (days and years are ignored).
YD	The difference between the days (years and dates are ignored).

Applies To

The DATEDIF function can be used in the following versions of Microsoft Excel:

• 
  Excel 2003, Excel 2000
  

Type of Excel Function

The DATEDIF function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  

Example (as Worksheet Function)

Let's look at some Excel DATEDIF function examples and explore how to use the DATEDIF function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following DATEDIF examples would return:

=DATEDIF(A1, A2, "Y")

Result: 1

=DATEDIF(A1, A3, "D")

Result: 455

=DATEDIF(A1, A3, "YD")

Result: 90

Frequently Asked Questions

Question: In Microsoft Excel, I have a cell that gives the result of someone's age using this formula:

=DATEDIF(B5,TODAY(),"Y") & " "

The result works fine. I then want to take the answer and if the age is 11 or over, give a result of 250. If the age is 10 and under, give the result 200.

I have tried using the IF formula, but it won't work on the cell with the DATEDIF formula as a value. I can get the IF formula to work on blank cells if I put the age in manually as a number but not if I use the result from the DATEDIF cell.

Answer: The first thing that I notice when looking at your formula is that you have two components to your formula. The first part of your formula is the DATEDIF function:

=DATEDIF(B5,TODAY(),"Y")

And the second part of your formula, you are concatenating a space at the end of your result:

& " "

This space at the end is probably what is causing your problem, as your formula is not returning a pure number, but rather the age with a space concatenated at the end.

When you are testing your age with the IF function, make sure you don't include the concatenated space within the IF formula itself.

You can try the following formula:

=IF(DATEDIF(B5,TODAY(),"Y")>=11,250,200) & " "

This formula will return either 250 or 200 depending on the age and then concatenate a space at the end. Notice that the & " " is not included in the IF function itself.

Question: In Excel, I have an operating field which is cell C4. I need to know how to return a zero (0) if the operating field is "blank."

I have the following formula:

=DATEDIF(C4,TODAY(), "y")

I will have occasions when the C4 data field will be blank, and in these cases, I need the formula to return zero instead of 115. Please help!

Answer: You can use the ISBLANK function to test whether a cell value is blank as follows:

=IF(ISBLANK(C4),0,(DATEDIF(C4,TODAY(),"y")))

This formula will return 0 if C4 is blank. Otherwise, it will return the DATEDIF calculation.

MS Excel: COVAR Function (WS)

This Excel tutorial explains how to use the Excel COVAR function with syntax and examples.

Description

The Microsoft Excel COVAR function returns the covariance, the average of the products of deviations for two data sets.

Syntax

The syntax for the COVAR function in Microsoft Excel is:

COVAR( array1, array2 )

Parameters or Arguments

array1

A range or array of integer values.

array2

A range or array of integer values.

Applies To

The COVAR function can be used in the following versions of Microsoft Excel:

• 
  Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
  

Type of Excel Function

The COVAR function can be used in Microsoft Excel as the following type of function:

• 
  Worksheet function (WS)
  

Example (as Worksheet Function)

Let's look at some Excel COVAR function examples and explore how to use the COVAR function as a worksheet function in Microsoft Excel:

=COVAR({1,2;3,4}, {5,6;7,8})

Result: 1.25

The above example would return 1.25.

You could also reference ranges in Excel.

Based on the Excel spreadsheet above, you could enter the following formula:

=COVAR(A1:B2, D1:E2)

Result: 1.25

This would also return the value 1.25.