fraction difficulty

[Phil]

I have office 2000:

I want cells to show up as fractions, but I want them all in 1/8th
increments.

I don't however want 2/8, 4/8 or 6/8, but 1/4. 1/2 and 3/4.

I can format each cell individually, but this creates problems if the
data changes.

TIA

Phil

 

[Dennis Tucker]

Phil,

I think you mean that you want the VALUE inside of some cells to show as
fractions?

This can be done by typing them in(but start with a Apostrophe or Single
Quote).

Note: You will not be able to do any worksheet math functions using these
cells.


Dennis

 

[macropod]

Hi Phil,

Use a custom cell format:
?/8
or:
# ?/8

 

[macropod]

Alternatively, you could use the 'fraction - up to one digit' format.

 

[Bernd P]

Hello Phil,

=TRIM(CHOOSE(2+SIGN(A1),"-","0","") &
IF(INT(ABS(A1))<>0,INT(ABS(A1))&" ","") & CHOOSE(1+ROUND((ABS(A1)-
INT(ABS(A1)))*8,0),"", "1/8","1/4","3/8","1/2","5/8","3/4","7/8"))

Regards,
Bernd

 

[Ron Rosenfeld]

I have office 2000:

I want cells to show up as fractions, but I want them all in 1/8th
increments.

I don't however want 2/8, 4/8 or 6/8, but 1/4. 1/2 and 3/4.

I can format each cell individually, but this creates problems if the
data changes.

TIA

Phil

Can you round the result?

If so, format the cell as a fraction with up to one digit, or Custom Format:

# ?/?

Then round the value to the nearest 1/8

e.g. =ROUND(A1*8,0)/8

or =MROUND(A1,1/8)

or =ROUND(Your_Original_Formula*8,0)/8

etc.

Depending on how you are entering your data.

If you cannot round the result, then you'll need to use a macro, probably
event-triggered.
--ron

 

[Ron Rosenfeld]

or =MROUND(A1,1/8)

One forgotten caveat: If you use this function, A1 and the "1/8" must have the
same sign. So you might prefer to use the ROUND function I previously posted.
--ron

 

[Bernd P]

Hello Ron,

You still trust the number format fraction?

See #4 of my Excel Dont's:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd

 

[Ron Rosenfeld]

Hello Ron,

You still trust the number format fraction?

See #4 of my Excel Dont's:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd

Trust is not a word I use in this circumstance.

But your discussion as to how Excel produces an error is flawed.

First of all, you write:

" Example: Enter the floating point constant 1.1 into a cell. Set the number
format of this cell to Fraction (Up to 1 digit). You will get 11/9."

Excel doesn't do that. Setting the format to Fraction (up to 1 digit) results
in an integer plus a fraction. To obtain a fractional value greater than one,
you need to custom format, something like #/?

And on *my* Excel, if I enter 1.1 into A1, and "Set the number format of this
cell to Fraction (Up to 1 digit)" I see a value of 1 1/9.

If I custom format it #/?, I see the equivalent value of 10/9.

Both ARE CORRECT, and you even write that 10/9 is correct.

Perhaps if you gave a valid example of where my suggestion would result in an
incorrect value, there might be some validity to your question; but your #4
does not provide that.
--ron

 

[Ron Rosenfeld]

Hello Ron,

You still trust the number format fraction?

See #4 of my Excel Dont's:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd

In followup, I think the most likely explanation of both of your errors in
discussing the fractional format -- and by both I mean your incorrect
description of how Excel displays a number formatted to show a fraction with
one digit; and your apparent belief that Excel displays 1.1 as 11/9, is that
when you were looking at your worksheet, you did not note that there is a space
between the first two 1's. In other words, what is in your cell is "1 1/9" but
you overlooked that first space, and so thought it was 11/9.
--ron

 

[Bernd P]

In followup, I think the most likely explanation of both of your errors in
discussing the fractional format -- and by both I mean your incorrect
description of how Excel displays a number formatted to show a fraction with
one digit; and your apparent belief that Excel displays 1.1 as 11/9, is that
when you were looking at your worksheet, you did not note that there is aspace
between the first two 1's.  In other words, what is in your cell is "1 1/9" but
you overlooked that first space, and so thought it was 11/9.
--ron

Hello Ron,

Thanks. You are right. I did not see the blank. Excel is obviously
correct here.

Regards,
Bernd