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

                                      Using Excel's wildcard 
At times, you could want to apply positive string matching or seek features — like search — without understanding exactly what you're looking for. As an example, you could need to look for a kingdom that starts offevolved with the phrase "New" — but suit against all viable outcomes, along with "New Hampshire", "big apple", and "New Jersey". To do this, you can use something called a "wildcard" person to tell Excel that you're looking for some thing, but you are no longer precisely certain what but.

Think of Excel's wildcard characters like the jokers in a deck of playing cards. They can tackle any fee they need to.

Right here's some extra statistics on how wildcards work:

The fundamental wildcard shape
There are two key styles of wildcards in Excel. Here's what they appear to be and how you use them.

Wildcard name rationalization
? Question mark Takes the region of a unmarried person. As an instance, "Tr?Y" suits with "Tray", "Troy", and "Trey", however no longer "Trolley"
* Asterisk Can take the place of any wide variety of characters. For instance, "Tr*y" matches with "Tray", "Troy", and "Trolley".
~ Tilda Tells Excel that the following character need to be treated as a regular character and no longer a wildcard. As an example, "Tr~?Y" suits most effective with "Tr?Y", not "Tray" or "Troy".
In line with the chart above, use the query mark (?) while you need to accept only a single man or woman, and the asterisk (*) when you need to just accept multiple characters.

Read More

Create Worksheet in Excel 2013

                         
                        SID's EXCEL WORKBOOK

Lesson 10 -How can Insert new blank Worksheet

              Create Worksheet in Excel 2013


Inserting New Worksheet
three new clean sheets constantly open when you start Microsoft Excel. Beneath steps explain you how to create a new worksheet if you need to begin every other new worksheet while you're running on a worksheet, otherwise you closed an already opened worksheet and need to begin a brand new worksheet.

Step 1 − proper click on the Sheet name and select Insert option.

New Sheet
Step 2 − Now you will see the Insert conversation with pick Worksheet option as decided on from the general tab. Click on the ok button.

Insert conversation
Now you should have your clean sheet as proven beneath equipped to start typing your textual content.

Blank Sheet
you could use a brief reduce to create a clean sheet every time. Attempt the usage of the Shift+F11 keys and you may see a new clean sheet just like the above sheet is opened.

                                              for more Tutorial to related Topics

EXCEL VIDEO TUTORIALS /advance Excel Course

Read More

How to input records in Excel

                            SID's EXCEL WORKBOOK

Lesson 9 -How can type text_how to Enter data

                                            Save your workbook


                       A way to input records in Excel

in this lesson, we'll study the maximum fundamental way to go into information in an Excel worksheet - by way of typing. In destiny training, we're going to observe a number of shortcuts for coming into statistics faster.

To enter records in Excel, simply pick out a cellular and begin typing. You'll see the text seem each within the mobile and in the system bar above.

To tell Excel to accept the statistics you've typed, press input. The facts can be entered at once, and the cursor will flow down one mobile.

You may also press the tab key in place of the input key. If you press tab, the cursor will circulate one mobile to the proper as soon as the statistics has been entered.

When Excel sees which you are typing right into a listing, pressing enter on the stop of the row will move the cursor down one row and back to the first column.

At any time at the same time as you're typing you could press the get away key to cancel. This brings Excel returned to the state it changed into in before you commenced typing.

While you want to delete data that has already been entered, simply pick the cells, and press the delete key.
                                  you can also see this video to increase your input performance

                                                     for more Tutorial to related Topics

EXCEL VIDEO TUTORIALS /advance Excel Course

Read More

Save your workbook and save editing changes

                         SID's EXCEL WORKBOOK

Lesson 8 -How can save Excel Workbook_how to Save editing changes

                                            Save your workbook

Applies To: Excel 2013
wherever you need to keep your workbook (for your pc or the internet, as an example), you do all of your saving on the file tab.

Whilst you’ll use keep or press Ctrl+S to keep an present workbook in its cutting-edge area, you want to apply keep As to save your workbook for the primary time, in a specific area, or to create a duplicate of your workbook in the equal or some other region. Here’s how:

click on report > store As.

Save As alternative at the report tab

under keep As, pick out the area wherein you need to shop your workbook. For instance, to shop in your desktop or in a folder on your laptop, click on computer.

Pick a location choice

TIP:  To store on your One Drive vicinity, click on One Drive, after which sign up (or sign in). To add your own locations in the cloud, like an Office 365 SharePoint or a One Drive location, click upload an area.

Click on Browse to discover the vicinity you want for your documents folder.

To pick some other vicinity to your pc, click on computer, after which choose the exact area in which you need to save your workbook.

Within the report name field, enter a call for a brand new workbook. Input a unique call if you’re growing a replica of an existing workbook.

To keep your workbook in a exclusive record layout (like .Xls or .Txt), inside the save as kind listing (under the file name container), select the format you need.

Click on keep.

Pin your favorite shop location

when you’re performed saving your workbook, you may “pin” the location you stored to. This maintains the area to be had so you can use it once more to shop some other workbook. If you tend to save matters to the identical folder or region loads, this could be a terrific time saver! You can pin as many places as you want.

Click on report > shop As.

Under store As, select the location where you last saved your workbook. For instance, if you last stored your workbook to the files folder on your computer, and you need to pin that location, click on laptop.

Underneath current folders at the right, point to the location you need to pin. A push pin picture  Push pin button appears to the proper.

 Use the rush pin icon to pin your favored shop region

click the photo to pin that folder. The photograph now shows as pinned  Pinned push pin icon . On every occasion you keep a workbook, this vicinity will seem on the top of the listing below current folders.

TIP:  To unpin a vicinity, simply click the pinned push pin photograph  Pinned push pin icon again.

Activate Auto Recovery

Excel automatically saves your workbook whilst you’re working on it, in case some thing happens, like the electricity going out. This is referred to as AutoRecovery. This isn’t similar to you saving your workbook, so don’t be tempted to depend upon AutoRecovery. Shop your workbook, regularly. However Autorecovery is a good manner to have a backup, simply in case some thing happens.

Ensure AutoRecovery is turned on:

click on document > options.

In the Excel alternatives conversation field, click store.

Keep choice in Excel alternatives

under save workbooks, make certain shop AutoRecover statistics every n mins is checked.

Set the minutes for a way regularly you want Excel to again up your paintings, and then click on ok.

                                                      if you want live guide line then see this

                                             

you can also learn From SID's Excel Expert Channel

EXCEL VIDEO TUTORIALS /advance Excel Course

Read More