Excel Rounding Errors?

Question

I want to have a function that divides one number by another and I inputed =QUOTIENT(numerator, denominator), but it says that 46/23 is 2.000 AND if I change 46 to 60, the answer is still 2.000.

How do I fix this rounding error?

(its excel 2007 if that matters)

Answer 1

This is not a rounding error, it is how the quotient function works.

Quotient Function: Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.

Do simple division instead. =A1/B1

then you can choose how many decimal places you want to display using the toolbar buttons.

If you decrease the decimal places you will get rounding of the displayed number by Excel also. So don't.

Answer 2

If you look up the QUOTIENT function in Excel help, you'll find that it returns the integer portion of the quotient. Excel is not giving you an error. It is doing exactly what it is supposed to do.

46 / 23 = 2
60/23 = 2.609 (when rounded to 3 decimal places)

When a function returns the integer portion it returns the whole number portion of the number and does away with the fraction. So for 60/23 it should return the answer 2.000 if you have the cell formatted in the Number format to show 3 decimal places. If you want to show the exact figure. It is much easier to just type

=60/23

Then you'd get:

2.609

as your answer.

To change the number of decimal places, go to:

Format > Cells...

Then click the Number tab, then select Number. Set the decimal places to however many decimal places you'd like it to show for the cell or group of cells that you selected.

If you already have the cell formatted as a Number format, you shouldn't even have to type the equals sign as long as you are not trying to reference two different cells such as dividing A1 by B1 and putting the answer in C1. So if the cell is already formatted as a number and you are entering the actual numbers all you should have to type is:

60/23

And it will give you the number in decimal format to however many decimal places you specified.

Answer 3

if you want to reduce the decimal places, on the home tab of excel 2007 click decrease decimal click until the zeros disappear and you've got only the number 2 shown on the cell