<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

MS Excel: Underused formulas you should know

Published on
15,042 Points
4,442 Views
16 Endorsements
Last Modified:
Approved
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.
Underused-Formulas.xlsx
16
Comment
9 Comments
LVL 5

Expert Comment

by:DamjanDemsar
example for roman is wrong.... the value of A1 is 999 not 2056
0
LVL 18

Author Comment

by:Steven Harris
Good catch Damjan!

I am in the process of making an example spreadsheet when I noticed my error as well.  This will be updated when the spreadsheet is attached.
0
LVL 22

Expert Comment

by:rspahitz
Do you know which versions support these functions?
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

LVL 18

Author Comment

by:Steven Harris
DATEDIF and USDOLLAR are relatively undocumented even through Microsoft support, but have both been verified to work on Excel 2007 and later.

CONVERT, REPT, SIGN and DOLLAR are supported by Excel 2003 and later.

From what I can find, these are also compatibility with Excel 2011 for MAC.
0

Expert Comment

by:chrisyu D
Great information !! I am a beginner in programming , this article helps me better understand a project - create excel formulas in C# on MSDN..
0
LVL 1

Expert Comment

by:dabug80
Thanks Steve.

I never knew of the datedif function. I've added it to my cheat sheet
0

Expert Comment

by:Timmeh
I think you've got a typo:
Start Date (cell E7) is 1/15/2011
End Date (cell E7) is 6/26/2013

I'd say that you're End Date cell is cell E8
0

Expert Comment

by:Timmeh
Also, in my Dutch version of Excel (where DATEDIF is called DATUMVERSCHIL), the syntax has ; instead of ,
So in my case it's like this:  =DATUMVERSCHIL(e7;e8;"d")
0
LVL 6

Expert Comment

by:Let's Go
Datedif works on earlier versions of Excel back to the early 1990s, but was not documented.  It was introduced to mimic the functionality of the equivalent Lotus 1-2-3 @datedif to assist in users transferring to Excel.
0

Featured Post

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Join & Write a Comment

This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month