Splitting A Number into Integer and Decimal Portions - MS Excel Tips

Here is a quick formula tip to start another awesome week.

Often while working with data, I need to split a number in to integer and decimal portions. Now, there are probably a ton of ways you can do this. But here are two formulas I use quite often and they work well.

Assuming the number is in cell A1,

• Integer part =INT(A1)
• Decimal part =MOD(A1,1)

These formulas work whenever my data has only positive numbers (which is the case 90% 0f time). But if I am dealing with a mix of positive and negative numbers, I use,

• Integer part =INT(A1) +(A1<0)
• Decimal part =MOD(A1,SIGN(A1))

What formulas do you use to process numbers?

I use a lot of formulas while working with numbers. But my favorite ones are MOD, INT, SIGN, ABS, RAND and RANDBETWEEN (new in Excel 2007, requires Analysis toolpak in earlier versions).