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:
Complete calendar months between the dates.Number of days between the dates.Complete calendar years between the dates.Complete calendar months between the dates as if they were of the same year.Complete calendar days between the dates as if they were of the same year.Days Excluding Years And MonthsComplete calendar days between the dates as if they were of the same month and same year.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:
If num_digits is greater than 0, then number is rounded up to the specified number of decimal places.If num_digits is 0 or omitted, then number is rounded up to the nearest integer.If num_digits is less than 0, then number is rounded up to the left of the decimal point.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.
More concise. See example below.More concise. See example below.More concise. See example below.Converts 499 to Roman (CDXCIX)Converts 499 to Roman (CDXCIX)Converts 499 to Roman (LDVLIV)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 )
Factdouble of 8 = 8x6x4x2 = 384Factdouble of 9 = 9x7x5x3x1 = 945Not 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