If you thought dividing one number by another was simple, think again. Excel has five different ways to divide numbers and we’ll explain them all.
Obviously, there is the basic concept of division in Excel using / in a formula.
Like in Excel we can use the formula =[@[Total Sales]/@[Number of Tickets Sold]]. By dividing the total sales by the number of tickets sold, we can find out the price per ticket.
Pretty simple and straight forward.
Dividing with a Percentage
But what about dividing with a percentage? Since Excel saves percentages as a fraction, 25% is really 0.25. So you multiply to get the percentage of a value.
In our example below, we can use the formula =0.25*15,000 to get the exact number (25%) of 15,000 tickets sold.
The Quotient function returns the integer portion of the result, ignoring any fractions. The formula is:
The numerator is the dividend whereas the denominator is the divider. Both are required for the function to work.
The formula for the price per ticket is
=QUOTIENT([@[Total Sales]],[@[Number of Tickets Sold]])
But if you were to use the basic division of =$15,555,250.00/500,000 you would get a result of $31.11 per ticket, quotient however works it magic, so really it’s only useful if you weren’t looking at exact results down to the fraction.
Quotient() only rounds down, if you want to round up or down use Round(), see below.
The Mod function returns the remainder of a division, ignoring the whole number or only the result to the right of the decimal point. In other words, the opposite of Quotient(). The formula is:
The number is the number for which you want to find the remainder. The divisor is the number of which you want to divide. Both are required for the function to work.
The formula for the tickets leftover is
=MOD([@[number of tickets]],[@[Group Size])
For example, you have 1000 tickets sold in groups of 33 tickets, you will be able to divide 33 into 1000, a total of 30 times, and there is 10 tickets leftover.
The Round function takes the result of a simple division and then rounds it to the nearest integer (up or down). Eg. Round(1000/33,0) will round to the nearest whole number. The formula is:
The number is the number you want to round. The num_digits is the number of digits to which you want to round the number argument.