Edate() Function

The Excel EDATE function returns a date on the same day of the month, x months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the future, and a negative value for dates in the past. 

Syntax

The syntax for the EDATE function in Microsoft Excel is:

=EDATE( start_date, months )

Parameters or Arguments

start_date

The starting date to use in the calculation.

months

The number of months to add to the start_date. It can be a positive or negative value.

Date Arguments

Note that Microsoft advises that you do not type dates directly into functions, as Excel interprets text representations of dates differently, depending on the date interpretation settings on your computer.

Therefore the start_date argument for the Edate function should be input as either:

·         A reference to a cell containing a date

Or

·         A date returned from another function or formula.

Edate Function Examples

Column B of the following spreadsheet shows six examples of the Excel Edate function.

Formulas:

	A	B
1	31-Dec-2015	=EDATE( A1, 1 )
2	31-Dec-2015	=EDATE( A2, -1 )
3	31-Dec-2015	=EDATE( A3, 2 )
4	28-Feb-2016	=EDATE( A4, 12 )
5	29-Feb-2016	=EDATE( A5, 12 )
6	29-Feb-2016	=EDATE( A5, -12 )

Results:

A B 1 31-Dec-2015 31-Jan-2016 2 31-Dec-2015 30-Nov-2015 3 31-Dec-2015 29-Feb-2016 4 28-Feb-2016 28-Feb-2017 5 29-Feb-2016 28-Feb-2017 6 29-Feb-2016 28-Feb-2015

The above examples illustrate that:

►   You can use negative values for the months argument, to get a date that is before the supplied start_date.

►   The function is able to cope with months that do not contain the same day number as the start_date. For example:

Ø  There is no 31st day of November, so when calculating 1 month before 31st December, the function returns the 30th November.

Ø  Similarly, the function can handle the leap year day - returning 28-Feb-2017 as the date 12 months after 29-Feb-2016.

Note also that, as recommended by Microsoft, in all of the above calls to the Edate function, the start_date has been supplied as a cell reference.

Reference:

https://www.techonthenet.com/excel/formulas/edate.php

https://exceljet.net/excel-functions/excel-edate-function

http://www.excelfunctions.net/Excel-Edate.html

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/

PermutationA() Function

Permut and Permutationa Functions

The Excel Permut and Permutationa functions both calculate the number of permutations of a selection of objects from a set.

However, the two functions differ in that the Permut function does not count repetitions whereas the Permutationa function does count repetitions.

For example, in a set of 3 objects, a, b, c, how many permutations of 2 objects are there?

-          The Permut Function returns the result 6 (permutations: ab, ac, ba, bc, ca, cb);

-          The Permutationa function returns the result 9 (permutations: aa, ab, ac, ba, bb, bc, ca, cb, cc).

PermutationA Function

The Excel Permutationa function calculates the number of permutations, with repetitions, of a specified number of objects from a set.

Syntax

The syntax for the PERMUT function in Microsoft Excel is:

=PERMUT( number, chosen )

Parameters or Arguments

number

The number of items.

num_chosen

The number of items in each permutation.

Permutationa Function Examples

In the spreadsheet below, the Excel Permutationa function is used to calculate the number of permutations (with repetitions) of six objects, selected from different sized sets.

 Formulas:

	A
1	=PERMUTATIONA( 6, 6 )
2	=PERMUTATIONA( 7, 6 )
3	=PERMUTATIONA( 10, 6 )
4	=PERMUTATIONA( 49, 6 )

Results:

	A
1	46,656
2	117,649
3	1,000,000
4	13,841,287,201

Notes

►   A permutation is a group of items in which order/sequence matters.

►   If order is not significant, use the COMBIN function.

►   Arguments that contain decimal values are truncated to integers.

►   PERMUT returns a #VALUE! error value if

Ø  The supplied number argument is < 0;

Ø  The supplied number_chosen argument is < 0;

Ø  The supplied number argument is < the number_chosen argument.

►   PERMUT returns #NUM! if the supplied arguments are non-numeric.

References

http://www.excelfunctions.net/excel-permutationa-function.html

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501

Hyperlink() Function

The Excel HYPERLINK function returns a hyperlink from a given destination and "friendly name". You can use HYPERLINK to construct a clickable hyperlink with a formula. The HYPERLINK function can build links to workbook locations, pages on the internet, or to files on network servers.

Syntax

The syntax for the HYPERLINK function in Microsoft Excel is:

=HYPERLINK ( link, [display_name] )

Parameters or Arguments

Link_location

A path to the file or the URL to the Internet address.

display_name

Optional. It is the value that appears in the cell. If this parameter is omitted, then the link will appear in the cell.

Link_Location

The address supplied for the link_location argument can be an absolute reference or a relative reference. The difference between these two reference types is shown below.

Absolute Reference

Shows the entire path to the referenced file.

E.g.

C:\Documents and Settings\User1\ExcelFile1.xlsx

Relative Reference

Refers to the referenced file's location relative to the current directory.

E.g. if the current Excel file is located in C:\Documents and Settings, then the relative reference:

User1\ExcelFile1.xlsx

is equivalent to the absolute reference:

C:\Documents and Settings\User1\ExcelFile1.xlsx

Also, within a relative reference, ..\ is used to specify the directory above the current one.

E.g. if the current Excel file is located in C:\Documents and Settings, then the relative reference:

..\ExcelFile1.xlsx

means "move up one directory and from there, access the file ExcelFile1.xlsx"

This is equivalent to the absolute reference:

C:\ExcelFile1.xlsx

Hyperlink Function Examples

Excel Hyperlinks are an excellent way of organising your data. This is shown in the spreadsheet below, which collates details of sales invoices, and uses the Excel Hyperlink function to create links to individual invoices:

Formulas:

	A	B
1	Date	Invoice Link
2	05-Jan-17	=HYPERLINK( "C:\Invoices\Invoice_B001.pdf", "B001" )
3	06-Jan-17	=HYPERLINK( "Invoice_B002.pdf", "B002" )
4

Results:

	A	B
1	Date	Invoice Link
2	05-Jan-17	B001
3	06-Jan-17	B002
4

Note that, in the above example, the hyperlink in cell B2 uses an absolute reference, while the hyperlink in cell B3 uses a relative reference.

As the current spreadsheet is located in the directory C:\Invoices, the hyperlink functions in cells B2 and B3, create links to the files Invoice_B001.pdf and Invoice_B002.pdf, both of which are located in the directory C:\Invoices.

Notes

Use the HYPERLINK function to create links to workbook locations, pages on the internet, or to files on network servers.

When a user clicks a cell that contains the HYPERLINK function, Excel will open the file or page specified by link_location. Link_location can be a cell reference or named range, a path to a file stored on a local drive, a path a file on a server using Universal Naming Convention (UNC) or a path to a location on the internet or an intranet in Uniform Resource Locator (URL) format.

►   Link_location should be supplied as a text string in quotation marks or a cell reference that contains the link path as text.

►   If display_name is not supplied, the HYPERLINK will display link_location as the display_name.

►   To select a cell that contains HYPERLINK without jumping to the destination, use arrow keys to select the cell. Alternatively, click on the cell and hold the mouse button down until the cursor changes.

Reference:

http://www.excelfunctions.net/Excel-Hyperlink-Function.html

https://www.techonthenet.com/excel/formulas/hyperlink.php

https://exceljet.net/excel-functions/excel-hyperlink-function

follow us on http://arivilm.blogspot.in

like us on https://www.facebook.com/Arivilm2501/