fraction difficulty

  • Thread starter Thread starter Phil
  • Start date Start date
P

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
 
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
 
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
 
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
Click to expand...

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
 
or =MROUND(A1,1/8)
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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
Click to expand...

Hello Ron,

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

Regards,
Bernd
 
Back
Top