Solved Turn off rounding in excel

April 26, 2013 at 18:48:42
Specs: Windows XP

Resultant of division display zeroes after the decimal where there shoud be integers.

True value > $4.42
true value > $28,812.95
sum by formula > $28,817.37 numerator
true value > 14.1 divisor
result by formula> $2,043.00
Value 2 places > $2,043.79

What is means of properly returning the actual integer values after the decimal?


See More: Turn off rounding in excel

Report •


#1
April 27, 2013 at 05:48:39

There is no rounding in Excel that can be turned on or off. There are formatting options that can be used and there are rounding functions, but there is no "switch" to enable or disable rounding.

If I were you, the first thing I would check is the 14.1 value. If that value is actually 14.1054187251254, the result of your division formula will be $2,043.00. If the cell that displays 14.1 is formatted to display only 1 decimal place, it will show 14.1 but it might actually be 14.1054187251254.

Well, to be precise, if your 14.1 is 14.1054187251254 then the real answer is $2,042.99996771232 which will display as $2,043.00 if the cell is set to display 2 decimal places.

The point here is that no matter what the cell is set to display, Excel uses to actual value when it performs the operations.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 6, 2013 at 19:25:14
✔ Best Answer

As it turns out, the solution is to use just =(A1/B2) format and forget about =quotient function.

Report •

#3
May 6, 2013 at 20:42:20

No need for parenthesis. =A1/B2 is all you need.

Why would you have used the QUOTIENT function in the first place? The QUOTIENT function returns the integer portion of a division operation. Of course the answer was $2,043 because that's how the QUOTIENT function is supposed to work.

Perhaps when you said "result by formula" you should have mentioned that you were using the QUOTIENT function. I doubt that that is what anyone in this forum would have surmised.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 6, 2013 at 21:07:18

New to Excel. Figure a quotient function means division, you know, numerator over divisor yields a quotient. Excel has a different view apparently. Longtime user of QuatroPro, much friendlier, processes not dispersed and hard to locate at top of screen, and Excel Help takes one to a myriad display of cryptic advice. Not one explained the simple method, had to find by trial and error. Tough on 85 year old geezers. QP Help not a puzzle.

Report •

#5
May 9, 2013 at 10:38:29

re: "Excel Help takes one to a myriad display of cryptic advice"

I'll give you that one, particularly if you are using Excel 2007 or above.

The Help files in 2003 and earlier were much simpler and to the point. Now, they basically do a Google (Bing?) search and return anything that might be remotely related to your search term. On top of that, they do a lousy job of sorting the results by relevancy.

You can improve the results somewhat by limiting the "help" to that which is available within the application instead of using the on-line results, but even that has changed since the older versions, and not for the better.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 9, 2013 at 13:29:43

Thanks, I like simple.

Report •

#7
May 9, 2013 at 14:08:15

I have an Excel Workbook called XLFDIC04.xls
It has help for most functions, with examples.
A very handy file to keep around.

Get it here:

http://www.hoffits.com/

MIKE

http://www.skeptic.com/


Report •


Ask Question