MS Excel: Underused formulas you should know

With the growth of technology and the relative ease of use of programs such as Microsoft Excel, users are finding it more efficient to document and perform calculations.  If you were to open an Excel file, the odds are that you will see a variety of formulas ranging from the basic addition (+) and subtraction (-)  mathematical calculations to the more complex vlookup and hlookup logical formulas. With its growing popularity, Excel is constantly raising the bar and setting the standards for included functions and formulas with each new release.

Despite the popularity of Excel and the Formula Bar which guides you to creating those formulas, there are a few uncommon formulas that could ease your spreadsheet and calculation tasks.  Feel free to grab the attached workbook and follow along!

DATEDIF
The DATEDIF formula is used to calculate the difference between two dates, using a set interval.

Use:
=DATEDIF(date1,date2,interval)

date1 is the starting date
date2 is the ending date

Intervals can be any value from the following:
d – Days
m – Months
y – Years
yd – Days, excluding Years
ym – Months, excluding Years
md – Days, excluding Months and Years

Examples:
Start Date (cell E7) is 1/15/2011
End Date (cell E7) is 6/26/2013

=DATEDIF(E7, E8,”d”) would return a value of 893, representing 893 Days
=DATEDIF(E7, E8,”m”) would return a value of 29, representing 29 Months
=DATEDIF(E7, E8,”y”) would return a value of 2, representing 2 Years
=DATEDIF(E7, E8,”yd”) would return a value of 162, representing 162 Days, excluding Years (1/15 to 6/26)
=DATEDIF(E7, E8,”ym”) would return a value of 5, representing 5 Months, excluding Days and Years (Jan to Jun)
=DATEDIF(E7, E8,”md”) would return a value of 11, representing 11 Days, excluding Months and Years (15th to the 26th)


CONVERT
The CONVERT formula is used to translate a measurement system into another measurement.

Use:
=CONVERT(number,from_unit,to_unit)

Number is any numerical value.

The from_unit and to_units can be any form of measurement from within a category (Liquid Measurement, Force, Weight, etc.) to another unit in the same category.

For a complete listing of units, please visit the Microsoft Office Support pages.

Examples:
In cell E23 is the value 30.

To convert Inches to Feet, we would use =CONVERT(A1,”in”,”ft”) which returns a value of 2.5, representing 2.5 Feet in 30 inches.
To convert Hours to Seconds, we would use =CONVERT(A1,"hr","sec") which returns a value of 108000, representing 108,000 seconds in 30 hours.
To convert Inches to Meters, we would use =CONVERT(A1,"in","m") which returns a value of 0.762, representing 0.762 meters in 30 inches.

If you are following along in the provided workbook, you should note that the formula used in E24 contains cell references in place of the measurement unit values as described herein.


REPT
The REPT formula is used to repeat a value a given number of times.

Use:
=REPT(value,number_of_times)

Value is any text or numerical string value or cell reference to a any text or numerical string.

Number_of_times is any numerical value.

Examples:
In cell E36 is the value abc
In cell E37 is the value 4

=REPT(E36,E37) would return the string:  abcabcabcabc


SIGN
The SIGN formula is used to determine the (sign) of a number, either positive (1), negative (-1) or zero (0).

Use:
=SIGN(number)

Number is any numerical value.

Examples:
In cell E45 is the value: -351

=SIGN(E45) would return the value: -1 (meaning the number is negative)

If in cell E45, we use the formula: =(-4*19)/(2*-6)*4

=SIGN(E45) would return the value: 1 (meaning the resulting value is positive)


DOLLAR & USDOLLAR
The DOLLAR and USDOLLAR formulas are used to convert a number to currency format, with decimal rounding.

While the formulas work the same in functionality, they DOLLAR formula will use the local PC currency setting while the USDOLLAR formula will always be formatted to US $.

Use:
=DOLLAR(number,number_of_decimals)
=USDOLLAR(number,number_of_decimals)

Number is any numerical value.

Number_of_Decimals is any numerical value.

Examples:
In cell E53 is the value: 8596.1652
In cell E54 is the value: 2

=USDOLLAR(E53,E54) would return the value: $8,596.17

In cell E63 is the value: 456.256548
In cell E64 is the value: 4

=USDOLLAR(E63,E64) would return the value: $456.2565


While sticking to your normal formulas is always a safe fallback, don’t be afraid to get out there and experiment!  Your Excel Spreadsheet is only as advanced as the formulas and functions used to make it; so feel free to dive deeper into those Excel formulas and if needed, post a Question here on Experts-Exchange for any clarification or help that may be needed.