Lost Excel Functions

Undocumented, Unloved and Unused Excel Functions

Following on from Chandoo’s MLookup function published on 1^st 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:

• Datedif
• Roundup
• RoundDown
• Evaluate
• Convert
• Roman
• FactDouble
• Bahttext

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

Datedif

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.

Use:

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

Interval	Meaning	Description
m	Months	Complete calendar months between the dates.
d	Days	Number of days between the dates.
y	Years	Complete calendar years between the dates.
ym	Months Excluding Years	Complete calendar months between the dates as if they were of the same year.
yd	Days Excluding Years	Complete calendar days between the dates as if they were of the same year.
md	Days Excluding Years And Months	Complete 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”)

Examples:

	Start Date	13/01/1963
	End Date	12/05/2011
Years	=DATEDIF($B$5,$C$5,”Y”)		48
Months	=DATEDIF($B$5,$C$5,”M”)		579
Days	=DATEDIF($B$5,$C$5,”D”)		17651
Months Exc. Years	=DATEDIF($B$5,$C$5,”ym”)		3
Days Exc. Years	=DATEDIF($B$5,$C$5,”yd”)		119
Days Exc. Years & Months	=DATEDIF($B$5,$C$5,”mD”)		29
Start Date > End Date	=DATEDIF($D$5,$D$4,”Y”)		#NUM!

Use of the Datedif function on Chandoo.org:

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.

ROUNDUP() and ROUNDDOWN()

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

Use:

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.

Examples:

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.

Examples:

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

Use on the Roundup and Rounddown functions on Chandoo.org:

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

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.

Example:

Say you have a polynomial equation in a cell as Text A1: ‘=X² + 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 = 10² + 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

Convert

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

Category	No Units
Weights & Mass,	5
Time	5
Force	3
Power	2
Temperature	3
Distance	8
Pressure	3
Energy	9
Magnetism	2
Liquid Measures	9

Use:

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

Examples:

Example	Result	Description (Result)
=CONVERT(5, “lbm”, “kg”)	2.27	Converts a 5 pound mass to kilograms (2.267)
=CONVERT(80, “F”, “C”)	6.67	Converts 80 degrees Fahrenheit to Celsius (26.6)
=CONVERT(1, “ft”, “kg”)	#N/A	Data types are not the same so an error is returned (#N/A)
‘=CONVERT(CONVERT(100,”ft”,”m”),”ft”,”m”)	9.29	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.

Use of the Convert function on Chandoo.org

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

 

Roman

The Roman function converts a number to Roman format.

Use:

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

Form	Type
0 or omitted	Classic.
1	More concise. See example below.
2	More concise. See example below.
3	More concise. See example below.
4	Simplified.
TRUE	Classic.
FALSE	Simplified.

Example:

Example		Formula	Description (Result)
=ROMAN(2011)		MMXI	Converts 2011 to Roman (MMXI)
=ROMAN(499,0)	Classic or Omited	CDXCIX	Converts 499 to Roman (CDXCIX)
=ROMAN(499, True)	Classic	CDXCIX	Converts 499 to Roman (CDXCIX)
=ROMAN(499,1)	More Concise	LDVLIV	Converts 499 to Roman (LDVLIV)
=ROMAN(499,2)	More Concise	XDIX	Converts 499 to Roman (XDIX)
=ROMAN(499,3)	More Concise	VDIV	Converts 499 to Roman (VDIV)
=ROMAN(499,4)	Simplified	ID	Converts 499 to Roman (ID)
=ROMAN(499, False)	Simplified	ID	Converts 499 to Roman (ID)

Use of the Roman function on Chandoo.org:

Nil

Factdouble

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

Use:

=Factdouble( number )

Example:

Example	Result	Description (Result)
=Factdouble(8)	384	Factdouble of 8 = 8x6x4x2 = 384
=Factdouble(9)	945	Factdouble of 9 = 9x7x5x3x1 = 945

 

Use of the Factdouble function on Chandoo.org

Not used

 

Bahttext

Converts a number to Thai Text represention of the number

Use:

=Battext( Number)

Example:

=Bahttext(250) , Returns

Use of the Bahttext function on Chandoo.org:

Not used

 

Examples

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 Discovered?

What Functions Have You Stumbled Onto?

Let us know in the comments below: