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

how to use datedif formula how to find over all years ,months and days of any personn in Excel

Advance Work Of Excel course and Advance Excel in now  presented Tittle
how to use datedif formula how to find over all years ,months and days of any person in Excel
Calculate the days, months or years between two dates. See how to use the undocumented Excel DATEDIF function. See how to:
1) Calculate the number of completed days between two dates d
2) Calculate the number of completed months between two dates m
3) Calculate the number of completed years between two dates y
4) Calculate the number of days after completed years yd
5) Calculate the number of months after completed years my
6) Calculate the number of months after completed years ym subscribe my channel
(You tube) https://www.youtube.com/channel/UCT19oGQ6i6kEp5sz9R4GWOA
(Google+)
https://plus.google.com/u/0/115433594671281035281
(Blogger)
http://advanceworkofexcel.blogspot.com/
(Twitter)
https://twitter.com/saddam_khan45
(Facebook Page)
https://www.facebook.com/Advance-Work-Of-Excel-453336005013724/

Read More

Beginners Introduction_definition_function_Training of Microsoft excel/Meaning Of Excel

                       

Starting EXCEL &SID's EXCEL WORKBOOK

                                                           with SID Excel Expert

Lesson 2 -what is the meaning_Definition_function and basic learning

                                  INTRODUCTION OF EXCEL 
Advance Work Of Excel
 Beginners Introduction_definition_function_Training of Microsoft excel/Meaning Of Excel
DEFINITION
Microsoft Excel is a spreadsheet program included in the Microsoft Office suite of applications. Spreadsheets present tables of values arranged in rows and columns that can be manipulated mathematically using both basic and complex arithmetic operations and functions
FUNTION
In Advance Excel, a function is a type of formula that allows the user to perform mathematical, statistical and logical operations more easily. While it is possible to create a unique formula, Excel offers users a variety of preset formulas that they term functions
TRAINING
Tutorials that range from basic to intermediate levels of learning, Con textures Advance Excel Tutorials definitely has you covered if you want to learn Excel. The channel has over 200 videos that you can truly utilize if you want to improve your knowledge about Advance Excel.
 if you want to master those Excel skills and take it to a higher level. Sid has Excel covered, and its instructional videos are brief yet easy to understand.
SID EXCEL EXPERT
Excels Fun truly lives up to its name by making its tutorial videos fun and easy to comprehend. With over 1000 Advance Excel videos, you’d be sure you’ll find one that will help you with your Excel needs.
LEARN EXCEL
If you want to learn Excels basic concepts as well as know practical information to help increase your productivity, visit Advance Work Of Excel channel on YouTube
Stuck on graphs, worksheet security, macros, formulas, and graphs on Excel? Don’t worry. Learn About Advance Excels step-by-step instructional videos will help you go through all your Excel issues with ease.
Advance Work of Excel,Excel,Microsoft Excel Software , Spreadsheet , Excel Charts, Excel Macros, Excel Formulas, Excel Function, VBA of Excel, Excellence, excel fun,basic formulas,excel 2013,Excel 2010, Excel 2016,2017

                                  you can also learn From SID's Excel Expert Channel

EXCEL VIDEO TUTORIALS /advance Excel Course

Read More