Excel Tips

Excel Tips You Can’t Live Without!

By Michael B. Jacobson, CPA

Excel comes with a lot of built in functions (almost 300 in Excel XP) You can even design your own. These built in functions fall into 9 basic categories – Database, Date and time, Financial, Information, Logical, Lookup & reference, Math and trigonometry, Statistical and Text. An earlier article (in the November, 2005 Asset) covered the following functions:

DDB - Returns the depreciation of an asset for a specified period

REPT - Repeat a character(s) in a cell

AND - Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

AVERAGE - Returns the average (arithmetic mean) of the arguments.

CEILING - Returns number rounded up, away from zero, to the nearest multiple of significance

CHAR - Returns the character specified by a number.

COMBIN - Returns the number of combinations for a given number of items. COUNT - Counts the number of cells that contain numbers and also numbers within the list of arguments.

CHOOSE - Takes a number from 1 to 29 and a list of items (up to 29) and returns the item that corresponds to the number.

MOD - MOD returns the remainder when one number is divided by another. DATEDIF - The DATEDIF function returns the time between two dates, SUBTOTAL - SUBTOTAL calculates a subtotal for a list.

NETWORKDAYS - Returns the number of whole working days between a start date and an end date

IF - Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

PI - Returns the mathematical constant pi, accurate to 15 digits.

Remember that you can find more information about all the Excel functions by going to Insert | Function or just click on the Insert Function icon on the formula bar. You can also enter the name of the function and click the icon and you will get a Function Arguments screen with a description of the function and boxes to enter the arguments. On the insert function dialog box you can enter a description of what you want to do and you will be presented with the proper function.

Here are some more functions you might find useful, interesting or even fun.

ABS

Returns the absolute value of a number. The absolute value of a number is the number without its sign.

Syntax

ABS(number)

Number is the real number of which you want the absolute value.

ACCRINT

Returns the accrued interest for a security that pays periodic interest.

Note - If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

Syntax

=ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)

Important - Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Issue is the security's issue date.

First_interest is the security's first interest date.

Settlement is the security's settlement date. The security settlement date is the

date after the issue date when the security is traded to the buyer.

Rate is the security's annual coupon rate.

Par is the security's par value. If you omit par, ACCRINT uses $1,000.

Frequency is the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4.

Basis is the type of day count basis to use.

Basis	Day count basis (# days per month/year)
0 or omitted	US (NASD) 30/360
1	Actual/actual
2	Actual/360
3	Actual/365
4	European 30/360

Remarks

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
Issue, first_interest, settlement, frequency, and basis are truncated to integers.
If you get an error message check Excel Help for this function.

Note that there is also the ACCRINTM function which returns the accrued interest for a security that pays interest at maturity.

AORAND

Returns a random number within the lower or upper bounds.

Syntax

=AORAND(Lower Bound, Upper Bound)

Remarks

Both bounds may be either numbers or cell references.
It appears that the function will only return a integer.

CLEAN

Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.

Syntax

CLEAN(text)

Text is any worksheet information from which you want to remove nonprintable characters.

CONCATENATE

Joins several text strings into one text string.

Syntax

CONCATENATE (text1,text2,...)

Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.

Remarks

The "&" operator can be used instead of CONCATENATE to join text items.

CONVERT

Converts a number from one measurement system to another. For example, CONVERT can translate a table of distances in miles to a table of distances in kilometers.

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

Syntax

CONVERT(number,from_unit,to_unit)

Number is the value in from_units to convert.

From_unit is the units for number.

To_unit is the units for the result.

CONVERT accepts the following text values (in quotation marks) for from_unit and to_unit.

Weight and mass	From_unit or to_unit
Gram	"g"
Slug	"sg"
Pound mass (avoirdupois)	"lbm"
U (atomic mass unit)	"u"
Ounce mass (avoirdupois)	"ozm"

Distance	From_unit or to_unit
Meter	"m"
Statute mile	"mi"
Nautical mile	"Nmi"
Inch	"in"
Foot	"ft"
Yard	"yd"
Angstrom	"ang"
Pica (1/72 in.)	"Pica"

Time	From_unit or to_unit
Year	"yr"
Day	"day"
Hour	"hr"
Minute	"mn"
Second	"sec"

Temperature	From_unit or to_unit
Degree Celsius	"C"
Degree Fahrenheit	"F"
Degree Kelvin	"K"

In addition there are conversion units for Pressure; Force, Energy, Power and Magnetism

The following abbreviated unit prefixes can be prepended to any metric from_unit or to_unit.

Prefix	Multiplier	Abbreviation
exa	1E+18	"E"
peta	1E+15	"P"
tera	1E+12	"T"
giga	1E+09	"G"
mega	1E+06	"M"
kilo	1E+03	"k"
hecto	1E+02	"h"
dekao	1E+01	"e"
deci	1E-01	"d"
centi	1E-02	"c"
milli	1E-03	"m"
micro	1E-06	"u"
nano	1E-09	"n"
pico	1E-12	"p"
femto	1E-15	"f"
atto	1E-18	"a"

Remarks

If the units are in different groups, CONVERT returns the #N/A error value.
Unit names and prefixes are case-sensitive.

COUNTIF

Counts the number of cells within a range that meet the given criteria.

Syntax

COUNTIF(range, criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples".

Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.

Of course, you should not forget ACOSH which returns the inverse hyperbolic cosine of a number or CHIINV which returns the inverse of the one-tailed probability of the chi-squared distribution. If you ever need them they are there along with a few hundred others. Click on the insert function icon and scroll through the list. You will find something useful that can make your computing life easier.

Get more Excel Tips

Mike Jacobson is a manager at Hochschild, Bloom and Company in St. Louis. He is the chair of the MSCPA Information Technology Committee. Mike can be reached at mjacobson@hbclp.com.