fraction display error

[JoeC.nc]

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

 

[Matt Richardson]

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

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

 

[JoeC.nc]

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 Richardsonhttp://teachr.blogspot.com- Hide quoted text -

- 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

 

[Dave Peterson]

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.

 

[Bernie Deitrick]

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

 

[joeu2004]

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.

 

[Dave Peterson]

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.

 

[JoeC.nc]

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














- 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

 

[Bernd P]

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