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.

7 comments
ppg
ppg

Excel provides the ROUNDDOWN(cell,0) function which rounds towards zero, i.e. 10.2 becomes 10 and -10.2 becomes -10. The TRUNC function works the same way. To get the fractional part you can use MOD(ABS(cell),1) which will work for zero.

agency
agency

If I wanted -10.8 to round to -11 and -10.2 to round to 10 I'd use the following IF function: =IF(cell

moganti
moganti

In case of negative integral values, still the formula =Int(cell) + (cell

Tony Hopkinson
Tony Hopkinson

rounding was required, he shrugs. Don't do Excel myself int knocked me completely off my stride, it's is a four byte whole number where I come from, could only do what amounts to cast if it was +- 2^31 or so... I'd have looked for floor and ceiling myself to cope with this sort of thing. Didn't know trunc existed in excel either, it's common function in many languages or libraries though.

Tony Hopkinson
Tony Hopkinson

It's Trunc(Round()).... I must confess to finding that moderately confusing...

david
david

Assuming we are looking for (-10) + (-0.2) as the split, using the example given, an alternative method is to use the absolute value of the number to get the integer part: and multiply by the sign to get the positive or negative. Thus, if the number is in cell A1 we can put the integer part in B1: B1 = INT(ABS(A1))*SIGN(A1) and the decimal part in C1 C1 = A1 - B1

david
david

Demonstrating my lack of familiarity with Excel functions. Int(Abs())*Sign() gives the same result as Trunc() I guess; but I didn't know of the existence of Trunc. Why do you need Round?