how to use correl Formula in Microsoft Excel

This article describes the formula syntax and usage of the CORREL function in Microsoft Excel.

Description

Returns the correlation coefficient of the Array1 and Array2 cell ranges. Use the correlation coefficient to determine the relationship between two properties. For example, you can examine the relationship between a location's average temperature and the use of air conditioners.

Syntax

CORREL(array1, array2)

The CORREL function syntax has the following arguments:

Array1    Required. A cell range of values.

Array2    Required. A second cell range of values.

Remarks

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

If Array1 and Array2 have a different number of data points, CORREL returns the #N/A error value.

If either Array1 or Array2 is empty, or if s (the standard deviation) of their values equals zero, CORREL returns the #DIV/0! error value.

The equation for the correlation coefficient is:

Equation

where

x and y

are the sample means AVERAGE(array1) and AVERAGE(array2).

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data1

Data2

3

9

2

7

4

12

5

15

6

17

Formula

Description

Result

=CORREL(A2:A6,B2:B6)

Correlation coefficient of the two data sets in columns A and B.

0.997054486

                                                   if you want the live session then see

if you want more videos of Advance Excel

Read More

how to use Date value function in EXcel Age calculation part 3

Using a combination of Excel functions and the date of birth, you can easily calculate age in Excel. You can either calculate the age till the current date or between the specified period of time.

The technique shown here can also be used in other situations such as calculating the duration of a project or the tenure of the service.

How to Calculate Age in Excel

In this tutorial, you’ll learn how to calculate age in Excel in:

The number of years elapsed till the specified date.
The number of Years, Months, and Days elapsed till the specified date.
You can also download the Excel Age Calculator Template.

Calculate Age in Excel – Years Only

Suppose you have the date of birth in cell B1, and you want to calculate how many years have elapsed since that date, here is the formula that’ll give you the result:



=DATEDIF(B1,TODAY(),"Y")
How to Calculate Age in Excel - Datedif years

If you have the current date (or the end date) in a cell, you can use the reference instead of the TODAY function. For example, if you have the current date in cell B2, you can use the formula:

=DATEDIF(B1,B2,"Y")
Now, let’s see how the DATEDIF function works.

DATEDIF function is provided for the compatibility with Lotus 1-2-3. One of the things that you’ll notice when you use this function is that there is no IntelliSense available for this function. No tool tip appears when you use this function.

How to Calculate Age in Excel - Datedif

This means that while you can use this function is Excel, you need to know the syntax and how many arguments this function takes.

Overview of the Excel DATEDIF function

Syntax: =DATEDIF(start_date, end_date, unit)

Excel DATEDIF function takes 3 arguments:

start_date: It’s a date that represents the starting date value of the period. It can be entered as text strings in double quotes, as serial number, or as a result of some other function, such as DATE().
end_date: It’s a date that represents the end date value of the period. It can be entered as text strings in double quotes, as serial number, or as a result of some other function, such as DATE().
unit: This would determine what type of result you get from this function. There are six different output that you can get from the DATEDIF function, based on what unit you use. Here are the units that you can use:
“Y” – returns the number of completed years in the specified time period.
“M” – returns the number of completed months in the specified time period.
“D” – returns the number of completed days in the specified period.
“MD” – returns the number of days in the period, but doesn’t count the ones in the Years and Months that have been completed.
“YM” – returns the number of months in the period, but doesn’t count the ones in the years that have been completed.
“YD” – returns the number of days in the period, but doesn’t count the ones in the years that have been completed.
You can also use the YEARFRAC function to calculate the age in Excel (in years) in the specified date range.

Here is the formula:

=INT(YEARFRAC(B1,TODAY()))
How to Calculate Age in Excel - YEARFRAC years

The YEARFRAC function returns the number of years between the two specified dates and then the INT function returns only the integer part of the value.

NOTE: It’s a good practice to use the DATE function to get the date value. It avoids any erroneous results that may occur when entering the date as text or any other format (which is not an acceptable date format).

Calculate Age in Excel – Years, Months, & Days

Suppose you have the date of birth in cell A1, here are the formulas:

To get the year value:

=DATEDIF(B1,TODAY(),"Y")
How to Calculate Age in Excel - Datedif Year Value

To get the month value:

=DATEDIF(B1,TODAY(),"YM")
How to Calculate Age in Excel - Datedif Month Value

To get the day value:

=DATEDIF(B1,TODAY(),"MD")
How to Calculate Age in Excel - Datedif Day Value

Now that you know how to calculate the years, months and days, you can combine these three to get a text that says 26 Years, 2 Months, and 13 Days. Here is the formula that will get this done:

=DATEDIF(B1,TODAY(),"Y")&" Years "&DATEDIF(B1,TODAY(),"YM")&" Months "&DATEDIF(B1,TODAY(),"MD")&" Days"
How to Calculate Age in Excel - combined

Note that the TODAY function is volatile and its value would change every day whenever you open the workbook or there is a change in it. If you want to keep the result as is, convert the formula result to a static value.

Download the Excel Age Calculator Template
Download File

Excel Functions Used:

Here is a list of functions used in this tutorial:

DATEDIF() – This function calculates the number of days, months, and years between two specified dates.
TODAY() – It gives the current date value.
YEARFRAC() – It takes the start date and the end date and gives you the number of years that have passed between the two dates. For example, if someone’s date of birth is 01-01-1990, and the current date is 15-06-2016, the formula would return 26.455. Here the integer part represents the number of years completed, and the decimal part represents additional days that have passed after 26 years.
DATE() – It returns the date value when you specify the Year, Month, and Day value arguments.
INT() – This returns the integer part of a value.

Read More

History Of Microsoft Excel

              

 SID's EXCEL WORKBOOK

                                                           with SID Excel Expert

Lesson 1 - How to Start Excel, Creating and Saving Workbooks

                 History Of Microsoft Excel

In This post SID Excel Expert did explain the actual and true history of Excel Which are more easy to understand
Early History. Microsoft originally marketed a spreadsheet program called Multi plan in 1982. ... Lotus was slow to bring 1-2-3 to Windows and by 1988 Excel had started to outsell 1-2- and helped Microsoft achieve the position of leading PC software developer.
Who created Microsoft  Excel?

                   Basic Information

The creator of Microsoft excel is pioneerDan Bricklin  and The electronic spreadsheet was essentially made in 1979 by software pioneerDan Bricklin, who started up Software Arts with Bob Franks ton and created VisiCalc

Microsoft originally popular as spreadsheet program called Multi plan in 1982, which was very popular on CP/M systems, but on MS-DOS systems it lost popularity to Lotus 1-2-3.
This promoted development of a new spreadsheet called Excel which started with the intention to 'do everything 1-2-3 does and do it Good'. 

The first version of Excel was Discovered for the Mac in 1985 and the first Windows version was Published in November 1987. 

Lotus was slow to bring 1-2-3 to Windows and by 1988 Excel had began to outsell 1-2-3 and helped Microsoft achieve the position of leading PC software developer.

Microsoft pushed its benefits with regular new discoveries, every two years or so on. The current version for the Windows platform is Excel 11, also called Microsoft Office Excel 2003. The current version for the Mac OS X platform is Microsoft Excel 2016
               Version OF Microsoft excel

Publish date	version	Materials
November 19, 1990	Office 1.0	Word 1.1, Excel 2.0, PowerPoint 2.0
March 4, 1991	Office 1.5	Word 1.1, Excel 3.0, PowerPoint 2.0
July 8, 1991	Office 1.6	Word 1.1, Excel 3.0, PowerPoint 2.0, Mail 2.1
August 30, 1992	Office 3.0	CD-ROM version: Word 2.0c, Excel 4.0a, PowerPoint 3.0,

you can also get information about excel from the Help menu

                           you can also learn From SID's Excel Expert Channel

EXCEL VIDEO TUTORIALS /advance Excel Course

Read More