Error in deserializing body of reply message for operation 'Translate'. The maximum string content length quota (8192) has been exceeded while reading XML data. This quota may be increased by changing the MaxStringContentLength property on the XmlDictionaryReaderQuotas object used when creating the XML reader. Line 1, position 13902.

Posted on May 16th, 2011 in Excel Howtos , Huis , Posts by Hui - 26 comments

Following on from Chandoo’s MLookup function published on 1st April 2011, I thought it might be worth documenting a few undocumented, no-longer documented and rarely used Excel functions.

Although some of the functions below aren’t documented they still work as of Excel 2010.

Users should be cautious with their use going forward as Microsoft may withdraw them from future versions of Excel.

But if you see them appear in older Excel models at least you’ll be the full bottle.

This post will look at the following functions:

DatedifRoundupRoundDownEvaluateConvertRomanFactDoubleBahttextWorked examples of all these functions are presented in the Example File which is compatible with all versions of Excel.

The **DATEDIF** function computes the difference between two dates in a variety of different intervals, such number of years, months, or days.

This function is available in all versions of Excel since at least version 5/95, but is documented in the help file only for Excel 2000.

By the way, do not confuse the **DATEDIF** worksheet function with the VBA **DateDiff** function.

**=DATEDIF(Start Date, End Date, Interval)**

Where:**Start** **Date** must be less than the **End** **Date. **

**Interval** is the interval type to return.

**Interval** value must be one of the following:

If you are including the **Interval** string directly within the formula, you must enclose it in double quotes:

**=DATEDIF(Date1,Date2,”m”)**

Datedif has been used a number of times at Chandoo.org

http://chandoo.org/forums/topic/how-to-calculate-age-from-their-dob

http://chandoo.org/wp/2009/09/22/elapsed-time-excel/

http://chandoo.org/wp/2008/08/26/date-time-tips-ms-excel/

**Disclaimer:**

Although the Datedif function above isn’t documented it still works as of Excel 2010. Users should be cautious with their use going forward as Microsoft may withdraw support for them in future Excel versions.

The **Roundup** and Rounddown functions rounds a number up or down, away from zero and have pretty much been replaced by the Round function.

The **Roundup** function rounds a number up, away from zero.

=ROUNDUP(number, num_digits)

The **Rounddown** function rounds a number down, towards zero.

=ROUNDDOWN(number, num_digits)

**Roundup**() behaves similarly to the **Round**() function, except that it always rounds a number up based on the following rules:

ROUNDUP(4.1,0) equals 5

ROUNDUP(106.9,0) equals 107

ROUNDUP(3.14159, 3) equals 3.142

ROUNDUP(-3.14159, 1) equals -3.2

ROUNDUP(31415.926, -2) equals 31500**Rounddown**() behaves similarly to the **Round**() function, except that it always rounds a number down based on the following rules:

ROUNDDOWN(4.1, 0) equals 4

ROUNDDOWN(106.9,0) equals 106

ROUNDDOWN(3.14159, 3) equals 3.141

ROUNDDOWN(-3.14159, 1) equals -3.1

ROUNDDOWN(31415.92654, -2) equals 31400

The Roundup and Rounddown functions have been used several times at Chandoo.org

**Roundup**

http://chandoo.org/wp/2010/04/29/quarterly-totals-from-monthly-data/

http://chandoo.org/wp/2010/04/30/quarterly-totals-multi-year-data/

**Rounddown**

http://chandoo.org/wp/2010/04/30/quarterly-totals-multi-year-data/

http://chandoo.org/wp/2009/07/06/excel-formulas-round-sort/

Evaluate is an Excel ver 4.0 macro function which is still supported and functional in Excel 2010.

The Evaluate function allows for the evaluation of a text equation as an algebraic equation.

The evaluate function cannot be used as a spreadsheet function but can be used in Named Ranges.

It is probably best described by example; **Evaluate 1**, from the Example File.

Say you have a polynomial equation in a cell as Text **A1: ‘=X2 + 5*Y – Z**

Setup 3 named ranges, X, Y , Z with values X=10, Y=5 and Z=3

You can use Evaluate in a a Named Range eg: **Result =Evaluate(SheetName!$A$1)**

And then on a worksheet **=Result**, which will return the answer **122** = **102 + 5*5 – 3**

Evaluate can be used to allow graphing of equations without use of worksheet functions or even worksheet ranges, an example of each is shown in the examples file as **Evaluate 2 **and **Evaluate 3**** **.

Evaluate 2: Uses a Range as the X Values and a Named Range using the Evaluate function as the calculated Y Values

Evaluate 3: Uses Named Ranges as the X Values and as the calculated Y Values based on an Evaluate function

**Use of the Evaluate function on Chandoo.org:**

Not Used

Converts a number from one measurement system to another.

For example, CONVERT can translate a table of distances in Kilometres to a table of distances in Miles.

Convert includes 49 units spread amongst the following 10 categories

**=Convert**(**number**, From Unit, To Unit)

A list of all the Conversion Units and Conversion Prefixes is included on the Conversion Factors tab of the Examples File.

Converts a 5 pound mass to kilograms (2.267)Converts 80 degrees Fahrenheit to Celsius (26.6)Data types are not the same so an error is returned (#N/A)‘=CONVERT(CONVERT(100,”ft”,”m”),”ft”,”m”)Converts 100 square feet into square meters (9.290304).A list of all the Conversion Units and Conversion Prefixes is included on the Conversion Factors tab of the Examples File.

http://chandoo.org/forums/topic/convert-function

The Roman function converts a number to Roman format.

**=ROMAN**(**number**, form)

**=ROMAN**(45 ) = XLV

**Form** is a number specifying the type of roman numeral you want. The roman numeral style ranges from Classic to Simplified, becoming more concise as the value of form increases.

Nil

Factdouble returns the double factorial of a number and is expressed in mathematics as n!!

Double factorials are used in probability theory and other higher levels of mathematics and is really just a way to simplify an otherwise complex expression

If the number is Even **Factdouble** = n(n-2)(n-4)…(4)(2)

If the number is Odd **Factdouble** = n(n-2)(n-4)…(3)(1)

So it is simpler to write 10!! than 10x8x6x4x2

**=Factdouble**( **number **)

Not used

Converts a number to Thai Text represention of the number

**Use:**

**=Battext( Number)**

=**Bahttext(250)** , Returns

Not used

An example file with worked examples from all the above functions is available from the following link; Example File

The file is compatible with all Excel versions.

What Functions Have You Stumbled Onto?

Let us know in the comments below:

**Spread some love,It makes you awesome!**

Posts & Navigation

Tags: Convert, Datedif, Evaluate, FactDouble, Learn Excel, Microsoft Excel Formulas, Roman, rounddown(), roundup, spreadsheets

RSS feed for comments on this post. TrackBack URI

## No comments:

Post a Comment