Calculate division results without decimals
—by T. L. Aardsma
Microsoft Excel 97/98/2000/2002
Microsoft Windows, Macintosh
Simple division in Excel requires no special functions. Just use the forward slash (/) in a formula to indicate that you want to divide, and your work is done—unless you want to your division results to return only whole numbers. For instance, imagine you have 300 product samples, and you want to distribute them evenly among 24 store locations. You can use the formula =300/24, which produces the result 12.5. But if you can only send whole samples, the prospect of sending 12 and a half to each store is out of the question. How can you make Excel return just the number of whole samples you can send to each store, disregarding decimals if the numbers in your formula don’t divide evenly? Plus, how can you make Excel tell you the number of samples you’ll have left if you send an equal number to each store? These types of integer division tasks do require special functions: QUOTIENT and MOD. In this article, we’ll show you how to use them.
A worksheet divided
To help you understand how the QUOTIENT and MOD functions work, we’ll put them to use in a simple ex-ample. We’ll begin by showing you how you can use the QUOTIENT function to return only the integer por-tion of a division result—i.e., the numbers to the left of the decimal point—discarding any decimal digits that might otherwise appear to the right of the de-cimal point. Then we’ll show you how to use the MOD function to do the opposite, returning the just the remainder of a division result. After you’ve had a chance to give the QUOTIENT and MOD functions a try, we’ll explain some technical aspects of their behavior that you should keep in mind as you begin using them in situations that are more complex.
Integer division components
Integer division equations consist of four components: the divi-dend, the divisor, the quotient, and the remainder. The dividend is the number you’re dividing, and the divisor is the number you’re dividing by. The quotient is the integer portion of the result, and the remainder is the portion of the dividend that isn’t evenly divisible by the divisor. For example, consider the equation 11 ÷ 4 = 2, remainder 3. The dividend is 11, and the divisor is 4. The quotient is 2, and the remainder is 3.
Use QUOTIENT to return an integer without a remainder
Normally, when you use Excel to divide one value by another, the result is a decimal value when the numbers don’t divide evenly. For example, if you en-ter the formula =5/2 in a cell and press [Enter], the result is the decimal value 2.5. In many situations, you want your formula to return a decimal value. But sometimes, you’re only concerned with the integer portion of division results. The remainder can simply be thrown away. For instance, imagine you’re an interior decorator, and you have an opportunity to purchase 110 yards of expensive designer fabric at an unbeatable bargain. To find out which item will bring you the biggest return on your investment, you want to find out how many curtains, duvet covers, slipcovers, or throws you can make from all the fabric. And since you can only make whole items, you’ll want to return only whole numbers. The QUOTIENT function enables you to return the integer portion of a division result without returning the remainder. The QUOTIENT function uses the fol-lowing syntax:
where numerator is the value you want to divide