Microsoft

Return a value's integer and decimal components

Returning a value's integer and decimal components isn't difficult, unless you encounter a negative value. Then, the usual solutions won't work.

Dissecting a value sounds like a detailed operation, but in truth, you can determine a value's integer and decimal components with a few simple expressions —  they just might not be the expressions you first turn to.

To return a value's integer component, you'd probably use the Int() function. That function works fine as long as the values are positive. Throw in a negative value and it doesn't work as expected. I used the Int() function in the sheet shown below and shaded some unexpected results.

This isn't a mistake. It's Int() working as designed — to round an integer down to its nearest integer. When a number's negative, that behavior gets in the way: Rounding down -10.2 doesn't return -10, it returns -11. When negative values are a possibility, use the following expression:

=Int(cell) + (cell<0)

The second component in this expression returns 1 or 0 (true or false) to accommodate Int()'s rounding down behavior. For instance, the value -10.2 evaluates as follows:

=Int(-10.2) + (-10.2 < 0) =

=Int(-10.2) + 1 =

-11 + 1 =

-10

Who could blame you if you get a bit lost at the -11 + 1 line. Remember, Int() rounds a decimal value down, not up.

You'll run into the same problem when applying your first best guess to return a value's decimal component. Mostly likely, you'll try

=Mod(cell,1)

It works with positive values but doesn't handle negative values as you might expect. The Mod() function performs the following equation:

dividend – 1 * Int(dividend / divisor)

Now, let's use this formula to evaluate -10.2 using what we learned about Int() above:

-10.2 – 1 * Int(-10.2/1)

-10.2 – 1 * Int(-10.2)

-10.2 – 1 * -11

-10.2 – - 11

.8

Mod() is working correctly, even if it isn't returning the results you expected. If there's any possibility that you'll encounter a negative value, use the following expression to return a value's decimal component:

=Mod(cell,Sign(cell))

The Sign() function determines a number's sign. This function returns 1 if the number is positive, -1 if the number is negative, and 0 if the number is 0 (which, as you might suspect, can cause trouble). Again, let's evaluate -10.2 using this new expression:

=Mod(-10.2,Sign(-10.2)

=Mod(-10.2,-1)

-.2

When the evaluated value is a positive number, the Sign() function returns 1, so it can handle both negative and positive values. What it can't handle is 0. When the value you're evaluating is 0, Sign() returns 0. Mod() can't handle 0 as the divisor because you can't divide by 0. You'll want to build in an appropriate error handler for 0, such as

=IF(cell=0,0,MOD(cell,SIGN(cell)))

As with most error handling, your data and business rules will dictate your solution.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks