## Morefunc additional block functions in Excel

**MOREFUNC Add-In for Excel**(

**free add application**- author Laurent Longre) contains a set of user functions which enable certain calculations and additional formula that standard Excel versions and built-in features are not supported

**Morefunc**Add-In implemented 65/66/67 useful functions in Excel.

**Morefunc Add-Ins**works in Excel 97, 2000, 2002, 2003, 2007. It also works in Excel 2010 and Excel 2013 but only 32-bit versions, this Add-Ins does not work on 64-bit versions. I wrote a functions 65/66/67. The application that I have contains 65 functions from the help files, and one that is not mentioned. If you want more information on the syntax then see "Help on this function" by clicking on help-link on dialog box function, or when you activate on Formulas tab in the group Morefunc.

## How to install MOREFUNC, on computer

- If you want
**MOREFUNC**, you can download on your computer: Download Morefunc - Unzip the zip file to a folder
- Run the file setup.exe (Run As Administrator for Win7 and Win8), follow the installation steps
- Now you have the application installed Morefunc (C:/Program Files/Morefunc)

## List of all Excel functions that contains MOREFUNC application

The table below refer to all additional features that are found within the application or package MOREFUNC. Some of the most famous are INDIRECT.EXT, LASTROW, DATEDIFF, FORMULATEXT. List all function inside application MOREFUNC with them syntax, you can find in ZIP file

Particularly interesting is the functions

Particularly interesting is the functions

**INDIRECT.EXT**(see end of this tutorial) working with closed workbooks unlike Excel INDIRECT functions. In the Help window, you can see examples of each**MOREFUNC functions**.DATE AND TIME | INFO |

DATE.DIFF | Returns the elapsed time between two dates in years, months and days |

EASTERDATE | Returns the Easter Sunday of a given year (serial number) |

ISO.WEEKNUM | Returns the ISO-compliant week number of a date |

INFORMATION | INFO |

EVAL | Evaluates a formula or expression that is in the form of text and returns the result |

FILENAME | Returns the name of the current workbook (file) |

FORMULATEXT | Returns the formula of a cell |

INDIRECT.EXT | Returns value or the contents of a cell or a range contained in a closed workbook |

MRFVERSION | Returns MOREFUNC version number |

PAGENUM | Returns the page number of a cell |

SHEETNAME | Returns the name of a sheet in the current workbook |

XLM.GET.CELL | Returns information about the formatting, location, or contents of a cell or a range |

XLM.GET.DOCUMENT | Returns information about a sheet or the current workbook |

XLM.GET.WORKBOOK | Returns information about the current workbook |

XLM.GET.WORKSPACE | Returns information about the workspace |

MATH_FUNCTION | INFO |

CHBASE | Converts a value from one base into another base |

GEOMEAN.EXT | Equivalent of the built-in function GEOMEAN (geometric mean) supporting calculations of higher capacity |

HEX.AND | HEX.AND performs a bitwise conjunction on hexadecimal numbers (AND) |

HEX.FORMAT | Formats an hexadecimal number |

HEX.NEG | Inverts the sign of an hexadecimal number |

HEX.NOT | Performs bitwise negation (NOT) on an hexadecimal number |

HEX.OR | HEX.OR performs a bitwise disjunction on hexadecimal numbers (OR) |

HEX.SUM | Returns the sum of hexadecimal numbers |

MATRIXROUND | Returns an array of adjusted rounded numbers, eliminating "visual" sum errors |

MDTERM.EXT | Returns the matrix determinant of an array (supports larger matrices than MDETERM) |

MINVERSE.EXT | Inverts a square matrix - supports larger arrays than MINVERSE. |

MMULT.EXT | Multiplies two matrixes. This function supports larger arrays than MMULT |

MRAND | Returns an array of random integers without repetition |

PN.ISPRIME | Returns TRUE if a number is a prime number (probabilistic method) |

PN.NEXT | Returns the smallest prime number greater or equal to the given number |

WEIGHTED.AVERAGE | Returns a weighted average |

STATISTICS | INFO |

COUNTDIFF | Counts the number of unique values in a range or an array |

COUNTIF.3D | Same function as COUNTIF supporting 3D ranges |

MMAX | Returns the N largest values in a table |

MMIN | Returns the N smallest values in a table |

STDEV.GROUPED | Estimates standard deviation based on a sample (grouped data) |

STDEVP.GROUPED | Calculates standard deviation based on the entire population (grouped data) |

VAR.GROUPED | Estimates population variance group classes |

VARP.GROUPED | Calculates population variance group classes |

TEXT | INFO |

ANSI128 | Removes accents from a string |

MCONCAT | Concatenates all values/data in a range or table |

NBTEXT | Converts a positive number into spelled-out text, in several languages |

REGEX.COMP | Returns TRUE if a text contains a regular expression |

REGEX.COUNT | Counts the matches of a regular expression in a string |

REGEX.FIND | Returns the position (first character) of a substring matching a regular expression |

REGEX.LEN | Returns the length of a substring matching a regular expression |

REGEX.MID | Returns a substring matching the given regular expression |

REGEX.SUBSTITUTE | Substitutes a new text for the substring matching a regular expression |

TEXTREVERSE | Reverses the characters of a string |

WMID | Extracts a word or a group of words from a text |

WODRCOUNT | Returns the number of words in a text |

AUTOMATIC SORTING | INFO |

HSORT | "horizontal" sort (sorts the columns) |

HSORT.IDX | Returns a sort index based on one or several keys, "horizontal" sort (sorts columns) |

UNIQUEVALUES | Returns the unique items of a range or an array (stored in a one-column array) |

VSORT | "vertical" sort (sorts the rows) |

VSORT.IDX | Returns a sort index based on one or several keys, "vertical" sort (sorts the rows) |

OTHER FUNCTIONS | INFO |

ARRAY.FILTER | Stores only the visible cells of a range (for instance a filtered range) in an array and returns this array |

ARRAY.JOIN | Joins various items (ranges, numbers, strings...) in a vertical array and returns this array. ARRAY.JOIN allows to pass union of ranges to several functions which don't support them |

GETV | Allow to use temporary variables in worksheet formula, returns the value of a temporary variable |

INTVECTOR | Returns a series (vector) of consecutive integers in the ascending order |

LASTROW | Returns the last non-empty row (or cell) of a range consisting of one or more entire columns |

RECALL | Returns the former contents of the caller cell (its contents before the last calculation) |

SETV | Allow to use temporary variables in worksheet formula, assigns a value to a temporary variable |

SHEETOFFSET | Returns a range that is a specified number of sheets from a range |

THREED | Coerces a 3D range into a single array |

UNION.OFFSET | Returns a reference to a range of adjacent or non-adjacent cells that is a specified number of rows and columns from an union of cells |

## Morefunc in Excel 2007

If you use

**Excel 2007**, after you install on your computer needs to**add MOREFUNC Add-In in Excel, 2007**.- Start Excel 2007
- Click the Office button
- Click the Excel Options button
- Select the Add-Ins in the left menu
- Find
**Morefunc**in the right part of the window and select it - Click on GO button

You will open a new dialog box on which you need to include the

**Add-In Morefunc**. Click on OK button.Now you have an active Add-In

**application MOREFUNC on the Ribbon of Excel 2007**. Click the**Formulas tab**and you will see a**group of commands Morefunc**. Help for each function, syntax, you can see by clicking on the**Help**command in this group. (see figure below)## Morefunc in Excel 2013

**If you use Excel 2010 or Excel 2013**, you can also use this application for additional formulas - functions in Excel. This

**MOREFUNC Add-in application does not compatible with 64-bit versions of Excel**.

To use

**Morefunc in Excel 2013**, do the following. Close Excel 2013 and all of the workbook

Using a program that works with files (Windows Explorer, Total Commander, etc.). Be sure to

**copy as Administrator**if you are using Windows 7 or later.

Find the

**folder Morefunc**on main partition of your operating system (C:\Program Files\Morefunc\).

**Copy the three files**in the folder where you installed Excel 2013 (Morefunc.xll, Morefunc11.xla, Morefunc12.xlam). Path to the Excel 2013 can be as C:\Program Files\Microsoft Office\Office15\Library\. (In Excel 2010, the folder is "Office14"). (see picture below).

In the next step

- Start Excel 2013
- Click your mouse on File => Options
- On the dialog box (see Figure below), select one of the following items related to Morefunc.
- Click on GO button

## How to enable Morefunc for Excel 2013

In the next step to enable the application

**Morefunc**, turn on all three items relating to**MOREFUNC Excel application**. Confirm the change, click on the OK button.## MOREFUNC applications on the ribbon Excel 2013

If necessary, close the Excel 2013, and re-run it. Click on

**Formulas tab**, and you see a group of commands for**MOREFUNC application**. From the drop-down menu you can select additional functions which are placed in thematic groups.Also note the picture above you on the ribbon appeared new tab ADD-INS.

If you are bothered by his appearance, you can disable it. (see below tutorials)

If you are bothered by his appearance, you can disable it. (see below tutorials)

## How to remove the Add-Ins tab on the ribbon of Excel 2013

**To turn OFF the add-ins tab of the ribbon**, do the following

- Right-click on the ribbon
- On the pop-up menu, select "Customize Ribbon ..."

You will open a new dialog window named "

**Customize the Ribbon**". In the right part of the window**uncheck the Add-Ins tab**. Changes confirm by click on the OK button (see Figure below).BTW: Application

**MOREFUNC**, on this described way may work simultaneously on two versions of Excel (Excel 2007 and Excel 2013) that are installed on a single computer or one operating system such as Windows 7.## INDIRECT.EXT function

Returns the value of a cell or range specified by its address (text string).

Unlike the

SYNTAX :

=INDIRECT.EXT(Reference,Volatile,A1-Style)

- Reference (string) : Range reference (address or name) (see below)

- Volatile (boolean, optional) : if TRUE or omitted, the function is volatile. If FALSE, it is not volatile (hit Ctrl-Alt-F9 to update the results).

- A1-style (boolean, optional) : If TRUE or omitted, the address is interpreted as an A1-style reference, if FALSE it is interpreted as an R1C1-style reference.

RETURNED VALUE :

Contents of the cell or range. If this value can't be retrieved (for instance because the workbook or the sheet doesn't exist), it returns #VALUE!

REMARKS :

The Reference argument can be one of the following :

- Another range in the same workbook :

If the range is in the same workbook, the Reference argument should contain its address in the usual form, like "A1", "Sheet1!A1", "'Sheet 1'!A1" and so on.

- A range in another workbook (absolute path) :

The pattern of the Reference argument is : "'Drive:\Folder\[WorkbookName.xls]SheetName'!A1". Don't forget the quotes!

- A range in another workbook (relative path) :

If the workbook is in the same directory: "'[WorkbookName.xls]SheetName'!A1".

- A workbook-level name :

If RangeName is a workbook-level name (not sheet-level) : "'Path\WorkbookName.xls'!RangeName (don't put the name of the workbook in []).

If the Volatile argument is TRUE (or omitted), the returned values are automatically updated when the workbook is opened. Notice that the function takes about 0.005 seconds to get the value of a cell in a closed workbook (which is relatively slow for a worksheet function), therefore it should not be used in too many cells.

If Volatile = FALSE, the returned values are not automatically updated. If you want to update them, press Ctrl+Alt+F9.

The calculations speed is significantly improved if you use INDIRECT.EXT in an array formula instead of separate non-array formulae.

EXAMPLES :

=INDIRECT.EXT("'C:\[My workbook.xls]Table 5'!E34,FALSE)

returns the value of the cell "Table 5!E34" of the workbook "C:\My workbook.xls". This value is not automatically updated unless you change the argument or press Ctrl-Alt-F9.

=INDIRECT.EXT("'C:\My Documents\[Workbook5.xls]Sheet1'!A9")

Returns the value of the cell Sheet1!A9 of the workbook "My Documents\Workbook5.xls". This value is automatically updated (volatile function).

=INDIRECT.EXT("'..\Temp\Test\[MyWorkbook.xls]Sheet1'!A1") returns the value of cell Sheet1!A1 in the workbook "MyWorkbook.xls" located in the parent directory.

{=INDIRECT.EXT("'[Book2.xls]Sheet1'!A1:A10")} returns the values (array) of the range Sheet1!A1:A10 in the workbook "Book2.xls" located in the same directory.

Unlike the

**Excel INDIRECT**built-in function,**INDIRECT.EXT**can also return the value of a cell whose workbook is closed.SYNTAX :

=INDIRECT.EXT(Reference,Volatile,A1-Style)

- Reference (string) : Range reference (address or name) (see below)

- Volatile (boolean, optional) : if TRUE or omitted, the function is volatile. If FALSE, it is not volatile (hit Ctrl-Alt-F9 to update the results).

- A1-style (boolean, optional) : If TRUE or omitted, the address is interpreted as an A1-style reference, if FALSE it is interpreted as an R1C1-style reference.

RETURNED VALUE :

Contents of the cell or range. If this value can't be retrieved (for instance because the workbook or the sheet doesn't exist), it returns #VALUE!

REMARKS :

The Reference argument can be one of the following :

- Another range in the same workbook :

If the range is in the same workbook, the Reference argument should contain its address in the usual form, like "A1", "Sheet1!A1", "'Sheet 1'!A1" and so on.

- A range in another workbook (absolute path) :

The pattern of the Reference argument is : "'Drive:\Folder\[WorkbookName.xls]SheetName'!A1". Don't forget the quotes!

- A range in another workbook (relative path) :

If the workbook is in the same directory: "'[WorkbookName.xls]SheetName'!A1".

If it is in another directory, for instance, the parent directory: "'..\[WorkbookName.xls]SheetName'!A1".

- A workbook-level name :

If RangeName is a workbook-level name (not sheet-level) : "'Path\WorkbookName.xls'!RangeName (don't put the name of the workbook in []).

If the Volatile argument is TRUE (or omitted), the returned values are automatically updated when the workbook is opened. Notice that the function takes about 0.005 seconds to get the value of a cell in a closed workbook (which is relatively slow for a worksheet function), therefore it should not be used in too many cells.

If Volatile = FALSE, the returned values are not automatically updated. If you want to update them, press Ctrl+Alt+F9.

The calculations speed is significantly improved if you use INDIRECT.EXT in an array formula instead of separate non-array formulae.

EXAMPLES :

=INDIRECT.EXT("'C:\[My workbook.xls]Table 5'!E34,FALSE)

returns the value of the cell "Table 5!E34" of the workbook "C:\My workbook.xls". This value is not automatically updated unless you change the argument or press Ctrl-Alt-F9.

=INDIRECT.EXT("'C:\My Documents\[Workbook5.xls]Sheet1'!A9")

Returns the value of the cell Sheet1!A9 of the workbook "My Documents\Workbook5.xls". This value is automatically updated (volatile function).

=INDIRECT.EXT("'..\Temp\Test\[MyWorkbook.xls]Sheet1'!A1") returns the value of cell Sheet1!A1 in the workbook "MyWorkbook.xls" located in the parent directory.

{=INDIRECT.EXT("'[Book2.xls]Sheet1'!A1:A10")} returns the values (array) of the range Sheet1!A1:A10 in the workbook "Book2.xls" located in the same directory.