Cell Formatting

[creekboy]

Need to Format a cell to show a result in 1/3 - 1/4ths - or 1/2s

Example 1.222 would show - 1 1/4

1.300 would show - 1 1/3

1. 450 would show - 1 1/2 etc.

Can this be done easy enough?

Thanks for your time guys.

[etaf]

this will do the formatting
=INT(A1)&" "& SUBSTITUTE(TEXT(MOD(A1,1),"?/?"),"/","/")

this will round to the nearest quarter
=INT(A1)&" "& SUBSTITUTE(TEXT(MOD(ROUND(A1*4,0)/4,1),"?/?"),"/","/")

BUT - you want to round up or down to 1/4 , 1/3, 1/2, 2/3, 3/4
is that correct

Dont know how to add the 1/3rds yet - other than using a lookup table

[Richard Buttrey]

Hi,

It may not be what you want but don't forget there is a standard format for fractions which would for instance show 1.222 as 1 1/4.

If this isn't acceptable (1.3 would also show as 1 1/4) then you'll need to provide some rules for deciding why a .222 type fraction should be shown as quarters, and why 1.3 should be thirds, etc. For instance what should 1.29 show as since this is equidistance between 1.25 (quarters) and 1.33 (thirds).

When you get a moment would you also update your location. This is often useful when providing answers since it helps determine what your various locale settings may be.

[creekboy]

Sorry for not getting back to you guys sooner but my internet kept shutting down on me. Finally got it fixed to-day.

The person that asked me to try to make up the spreadsheet for this has changed his mind and no longer requires 1/3s just 1/4s. That makes it much easier for me (a newbie)

Again, many thanks for you help.

P.S. Location updated.

Creekboy.