## Excel Function of the Week - ROUND

The round function enables you to truncate values to the number of decimal places you desire. This could be in fractions of a whole value e.g. hundredths and thousandths of whole number, or as large numbers, e.g. rounding to the closest million or billion.

There are some very similar functions, ROUNDUP, ROUNDDOWN and MROUND. In addition there are some complimentary functions such as FLOOR and CEILING.

These functions all perform the same way with specific variations. Round follows the 4/5 round principle. That is if the number ends in 4 or lower it rounds down, and if it ends in 5 or higher it rounds up.

## Excel Function of the Week - IF

I have decided to write a short post once a week looking at a single Excel function. This week we are going to look at the IF function. Definition from Excel Help

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.

Syntax

IF(logical_test, value_if_true, [value_if_false])

My explanation

The IF function is best thought of as a solution to “either - or” scenarios. Here are some good examples with the syntax to use for each one.

## Developing in Excel 2007

I much prefer working in Excel 2007 to Excel 2003. Despite the issues with backward compatibility, there are a lot of advantages and benefits to using the new version.

Some little things that have been changed are

The previous limit on nested brackets in formulas from 7 has been increased to 64. I used this today

The number of columns and rows has increased. I used this today.

I had to develop a file for a client that involved a complex work roster arrangement to calculate days off in repeating 2, 3 or 4 week cycles for the next 20 years.

Here is the nested formula that got me the logic for a roster.

I then used one formula to generate over 600,000 cells and create a map that looks like this.