Round Price to the Nearest Nickel

If you want to avoid pennies, you can round prices to the nearest nickel using the following formula:

=ROUND(value/0.05,0)*0.05

If you want to round UP to the nearest nickel, you can change the above formula to use ROUNDUP instead of ROUND, or you can use the CEILING formula:

=CEILING(value,0.05)
=ROUNDUP(value/0.05,0)*0.05

To round DOWN, you can use the FLOOR formula:

=FLOOR(value,0.05)
=ROUNDDOWN(value/0.05,0)*0.05

Calculating Number of Working Days

=NETWORKDAYS(start_date,end_date,holidays)

You can use the NETWORKDAYS() function in Excel to calculate the number of working days (excluding weekends) between and including two dates. In Excel 2000/2002/2003, this function requires the Analysis ToolPak to be installed. The start_date and end_date must be dates stored as serial numbers or defined using the DATE function. The holidays parameter is optional and is a reference to range of dates to be excluded besides weekends.

Example:

A1=1/1/2009    (Start Date)
A2=1/8/2009    (End Date)
A3=1/1/2009    (Holiday)
A4=1/2/2009    (Non-Working Day)
A5=NETWORKDAYS(A1,A2)        (= 6 Working Days)
A6=NETWORKDAYS(A1,A2,A3:A4)  (= 4 Working Days)

The WORKDAY() function is almost the reverse of the NETWORKDAYS function, except that the WORKDAY function finds the date that is the number of days before or after the start_date, but not including the start date.

=WORKDAY(start_date,days,holidays)

Example:

=WORKDAY(A1,6)   (= 1/9/2009)

Formulas for Dynamic Named Ranges

My latest article, "Formulas for Dynamic Named Ranges", explains how to use the OFFSET function to create a named range that can expand to the last Numeric value, Text value, or Non-Blank cell within a column or row. Although there are many possible applications of this technique, I recently used it to create a meal planner where the cells within the planner reference a dynamic named range to populate a drop-down list within the cells. The formulas used for the dynamic named range allow the list to contain blanks and the user doesn't need to do anything except insert, delete, or modify the items within the list.

Warning: Excel Performs Negation Before Exponentiation

What does –5² equal? –25 or 25? If you said –25, then you probably listened in school when you were told that negation should be performed on the same level as subtraction, which comes after exponentiation, so –5²=-(5²)=–25.

If you look up "operator precedence" in Excel's help system, you'll see that Excel performs Negation before Exponentiation, so it evaluates –5² as (–5)².

By the way, in case you were wondering, "negation" is when the minus sign "–" is used as a unary operator, as in -5, which doesn't have an operand on the left side of the expression. Subtraction is a binary operation, as in 6-5, which has an operand on both sides of the "–" operator.

What You Should Do: When using negation, ALWAYS use parentheses to enforce the proper order of operations. Even if YOU know what Excel is doing, don't assume that other people know this quirk about Excel.

Here are some examples of formulas in Excel where a mathematical purist might run into this problem if not using parenthesis around the negated expression.

=-A1^2
Excel evaluates as: =(-A1)^2

=5/(-B4^2)
Excel evaluates as: =5/((-B4)^2)

=5+(-A1^2+10)
Excel evaluates as: =5+((-A1)^2+10)

Don't confuse "negation" with "subtraction". The following formula is evaluated like you would expect based on standard order of operations:

=5+A1-A1^2
Excel evaluates as: =5+A1-(A1^2)

The BMI Formula

I recently created a BMI Chart with Excel and figured I'd list the formulas below, just in case you couldn't find them on the other 756000 BMI calculator sites.

• Metric Units: BMI = Weight[kg] / ( Height[m] x Height[m] )


• English Units: BMI = 703 x Weight[lb] / ( Height[in] x Height[in] )

If you were wondering, the "703" in the English version of the formula is just a conversion factor. The more exact conversion factor is 703.069579639159, which means that if you are using English units and the formula listed above, your BMI is actually about 0.01% higher than the calculator reports, which of course doesn't really matter.

I also created a Gadget for iGoogle, which I've embedded below just to see if I could.

Highlighting Every Other Row Using Conditional Formatting

Conditional Formatting (accessed via the Format menu) often makes spreadsheets seem to behave like 'magic' because when a user copies cells, the formatting can change automatically without the user knowing why. That is, unless the user knows about conditional formatting. Anyway, here is a common but useful tip for highlighting every other row within a table. Excel 2007 can autoformat tables quite nicely, but if you're like me and still need to use older versions, this tip can come in handy.

The following formula, when used as a conditional formatting condition, will result in the odd numbered rows having the format you define (such as a gray background, bold font, different color font, etc.)

=MOD(ROW(),2)=1

To highlight even numbered rows:

=MOD(ROW(),2)=0

Below is an example of how I modified my Checkbook Register template using this approach:



Hint: after you add the conditional format condition to one cell, you can use the format painter to copy the format to other cells. The formatting is copied and pasted just like any other formatting.

Line Break in a Cell Using a Formula

The basic way to add a Line Break within a cell is to press Alt+Enter (on a Mac press Ctrl+Option+Return). After pressing Enter, the Wrap Text property will automatically be set.

You can also add a line break in a cell with a formula using the CHAR(10) function and the concatenation function "&". The example below will add a line break between "abc" and "def".

="abc"&CHAR(10)&"def"

When using a formula, you will need to manually change the format of the cell to set the Wrap Text property. Otherwise, you'll end up seeing "abc□def" where the "□" indicates the presence of the line break when the Wrap Text property is unchecked.

Amortization Calculation

This article provides a basic explanation of how amortization works. The formula for calculating the periodic payment of a loan is given, along with a web-based calculator. The article also explains how to create an amortization table.

Analyze Survey Results with the FREQUENCY Function

Surveys that ask you to rate something on a scale of 1-5 or 1-7 or 1-10 are extremely common. If you need to summarize the results of such a survey quickly, it is very simple to use Excel's FREQUENCY(data_array,bins_array) function to do this. Below is an example where the ratings are integers between 1 and 5. To use the frequency function, follow these 3 steps:

1) Select cells A1:A5

2) Enter the following formula in cell A1
=FREQUENCY({1,1,1,1,2,2,2,2,2,3,3,4,4,5},{1,2,3,4,5})

3) Press Ctrl+Shift+Enter (because the frequency formula is an array formula)

Of course, you could also reference a range of cells for the data_array. Rather than using up another 5 cells for defining the bins_array, just include the values for the bins using curly braces {}. The data_array doesn't necessarily have to be integer values, but surveys usually involve multiple-choice. Personally, I would like to see more online surveys use a slider-bar for the ratings. That way, people like me who can't decide between a 4 or a 5 could choose 4.72 and still use a mouse. :)

One VERY cool way to use the frequency function is inside of the SparkBars() formula that is part of the Bissantz SparkMaker add-in. Here is a link to the article:
Using Sparklines for Survey Results

Financial Functions and Formulas

I created a condensed list of all the finance functions and formulas in Excel, including those that are in the Analysis ToolPak. This list is oganized into subcategories. Some of the function names are cryptic, so I underlined the letters in the description that make up the function name. A more detailed list is available in Excel's Help system.