 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008fraction display error
Hi All, I came upon an excel issue that, performing a web search, I do not find documented anywhere. When decimal numbers are displayed as fraction using Format-->Fraction (up to one digit), Excel selects the closest available fraction (supposedly). However it doesn't do it correctly. eg 0.1 displays as 0 rather than 1/9 1.3 displays as 1 1/3 rather than 1 2/7 Values between 0.2308 and 0.2361 display as 1/4 rather than 2/9. I am aware that these display values are not used in calculations, but what the hell? Excel should be selecting the closest available fraction, no?? I believe that this issue is a bug, and not a Bin-->Dec artifact. BTW, Excel 2000 & 2002...don't know about other versions. Joe C On Jan 28, 10:44 am, JoeC...@gmail.com wrote: > Hi All, > [quoted text clipped - 21 lines] > > Joe C Hi Joe. When you are changing to the Fraction type, what are you choosing from the selection on the right of the dialog box. That is when you right click your cell (or cells) and choose Format Cell, and then pick 'Fractions' from the selections on the left, what are you choosing as the Fraction type in the list on the right. Different options provide different results. As for Excel chosing the closest available, I'm not sure. It certainly doesnt say anything about this in the online help, so I guess we can't make assumptions. Hope this helps, Matt Richardson http://teachr.blogspot.com > On Jan 28, 10:44 am, JoeC...@gmail.com wrote: > [quoted text clipped - 41 lines] > > - Show quoted text - Matt, I'm using "up to one digit" in the fraction type box. What bothers me is the inconsistency. 0.3 --> 2/7 (correct-closest fraction using 1 digit in numerator/ denominator) 1.3 --> 1 1/3 (incorrect, 1 2/7 is closer than 1 1/3) Anyway...wating to see what Microsoft has to say. Thanks, Joe If you're waiting for a response from MS to this message, it'll probably be a longgggg wait. The vast majority of posts are from users helping users. Every once in a while, an MS employee posts, but it's not very often. <snipped> > Matt, > [quoted text clipped - 11 lines] > > Joe  SignatureDave Peterson > Every once in a while, an MS employee posts, but it's not very often. And I would guess it is even rarer that an MS employee would identify him/herself as such. I used to work for a major computer system vendor. We had a policy against identifying yourself as an employee in public forums, for fear that readers would think that you are representing the company's position. Only authorized (PR) people were permitting to identify themselves as company employees. You can imagine how useless their statements are. I've seen a lot of MS employees use what looks like a valid @microsoft.com email address. Many times, they'll post that they're going to go back to the product group to find additional information. But the vast quantity of posts are from non-MS email addresses. > > Every once in a while, an MS employee posts, but it's not very often. > [quoted text clipped - 7 lines] > themselves as company employees. You can imagine how useless their > statements are. SignatureDave Peterson Joe, The algorithm that MS uses to format numbers as one digit fractions weights the whole number heaviest, then halves, thirds, quarters, eights, and then the remainder with diminishing frequency or weighting. 2/7 just isn't as important or intuitive as 1/2 or 1/3 - and remember, you can always use decimal formatting. HTH, Bernie MS Excel MVP > Hi All, > [quoted text clipped - 21 lines] > > Joe C On Jan 28, 9:24 am, "Bernie Deitrick" <deitbe @ consumer dot org> wrote: > Joe, > [quoted text clipped - 38 lines] > > - Show quoted text - Bernie, Thanks for the information. Strange that it's not documented on the web. The 0.3-->2/7, 1.3-->1 1/3 is still not consistent. Anyway, like many things, I guess this was a historical decision that was made and now "is what it is". Cheers Joe Hello, I think MS applied Aberth's algorithm: Oliver Aberth, A method for exact computation with rational numbers, JCAM, vol 4, no. 4, 1978 I get exactly the same results with my UDF NRN() which is based on his article: http://www.sulprobil.com/html/nearest_rational_number.html Thing is that the denominator is too small for Aberth's lemma, I presume. Regards, Bernd |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.