Lost Excel functions

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 2, position 9250.
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:

DatedifRoundupRoundDownEvaluateConvertRomanFactDoubleBahttext

Worked 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:

If num_digits is greater than 0, then number is rounded down to the specified number of decimal places.If num_digits is 0 or omitted, then number is rounded down to the nearest integer.If num_digits is less than 0, then number is rounded down to the left of the decimal point.

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 = 945

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

View the original article here

Microsoft Excel: more accurate results of the round function

', Head, and move them! When what you think in terms of roundto do you hear? If the Western movies and TV shows, I would of herbicides and grazing cattle seen live in the Western United States to spend time in your yard work might be thinking, and Excel functions.

Round-up & ROUNDDOWN

Is a special pattern ROUNDUP and the Sidekick ROUND ROUNDDOWN functions. These functions ensure that same value value that is used in the formulas on the worksheet appears in your readership and reviews are all important tools in the Toolbox of Excel. Printed and displayed in the worksheet, if formatted with two-digit decimal number is, e.g. to only than all of the formulas on the worksheet need to calculate two digits. If you have other than to view multiple worksheets have decimal digits or percentage, shares or parts that contain data, using the round function or lesser known brothers of one.

The structure of the ROUND function is easy.

= ROUND (number, num_digits)

Round and assumptions, and rounds to nothing more than 5 is the next number. If you specifically were rounded to 1... 316, two more digits following the decimal point is the result of a calculation that 1... Change to 32. Calculates the value of the round function is rounded to 0 decimal 1, 234.567, appear as 1,286's no slip.

ROUNDDOWN function rounds at all times, regardless of value under the name suggests the aggregate function is, and always rounded up.

If you are using the ROUNDUP function it is to calculate the value of your favorite option prices and other courted inconsistent key not is.

ROUNDUP function creates the structure and syntax.

= ROUNDUP (number, num_digits)

Imagine that the owner of a small business that uses Microsoft Excel based on the number of hours they have worked to use the ROUNDDOWN function to track employee vacation hours earned. Make sure you don't receive a vacation in this function, employees have earned them more than. Now, if the employee is to keep this idea. Create the ROUNDDOWN function.

= ROUNDDOWN (number, num_digits)

All these round features more clearly adds more accurate results in the Excel worksheet.

Dawn Bjork Busby Microsoft Office Specialist (MOS) master certified instructor and certified expert certified instructors, Microsoft application professionals (paid), Microsoft Office and as the software Pro ? and Microsoft Certified Trainer (MCT) is. Dawn software speakers share smart ways to use software effectively through her work as a trainer, consultant, and author of six books. Software for more tips, tricks, strategy and technology the found at http://www. SoftwarePro.com.

Microsoft Excel 5 or date functions.

Microsoft Excel features more than 200, how to use a lot? Especially if you have requires that you use to calculate the date or change date date functions in your life easier. I regularly use 7 date functions are as follows.

() This function uses quite a lot of people now inserts the current date and time a cell update. I.e., tomorrow now () function is in there if you open a workbook, tomorrow's date cell. Most use this cell, and then just leave it. Usually a few days has calculations another date cell calculations are based on the 2 days. If the where in order to take and the product shipping business [is to find the average number of days it takes orders to ship to a good start. NETWORKDAYS (start date, finish date, holidays) this function is the above example can actually work. Only in the subtraction now () number of days from the date from would be, but also includes the weekend. If your business does not work on weekends, this is in fact reflect business. Use NETWORKDAYS function start and (it's now () can enter the cells that contain functions or), finish date, you can enter any holiday between these dates. It is in from Monday to Friday will tell how many days you two days. Today () of the alternate now be used instead. Today () function returns only the current date, but the only difference between the two at present () returns the current function (the computer or network) system date and time. Comes out decimal is displayed if you are calculating the date difference. You can a bit neat to look at it spreadsheet. I 3 going to group them together are quite similar. Year (date): month (date), day (date). They are all just like behavior, date or within a cell and the year, month, day number representing returns to enter one. (Date), using the most. It is my year and four-digit number and to give. I is an easy way to just two days of General number, if you do this, your date is December 31, 2009 and January 1, 2010 if it's a one-year (2010 - 2009) grant. I tend to use them in some way IF statement. Using a conditional statement that is 1 month (C4) month > = month (today ()), I can be true otherwise false one calculation, in other cases. When I started using a lot of stuff the last feature is really liked, but now I use it. It's a date (year, month, day) is. Each extraction is the date of (,) (Sun) day (date) feature of the used, whole date cannot be used. Think quickly add to it or by using the month, date in the future I subtract from a date (, (now days), months (today ()) If you need to spend working on it to + 1 day (today ())), and then break it down in the future to save time, do your best complex formulas can seem confusing.

It is not only to find yourself again used date functions are once again, only these ones. , If you use them to try them out yourself, (in the function function using) to nest and build more powerful calculations. Increase the number of functions available to you to install the Analysis ToolPak add-in.

Katherine Davison

The sign up for her free monthly newsletter Http://www.katherinedavison.co.uk/about-me/newsletter in Outlook quick reference card.