Displaying fractions.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David Farber - 10 Jun 2006 19:27 GMT

Is there a way to format a cell (without a long convoluted formula) so it
rounds the fractional part of a number into either halves or thirds? For
example, 1.2 would be displayed as 1 1/3. Or 1.56 would be displayed as 1
1/2. The application would be for photography where it is common to compare
"stops" in increments of either halves or thirds.

Signature

Thanks for your reply.

David Farber
L.A., CA

Reply to this Message

Ron Coderre - 10 Jun 2006 19:38 GMT

Try this:

Select the range of cells to be impacted

From the Excel main menu:
<format><cells><number tab>
Category: Fraction
Type: Up to one digit
Click the [OK] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> Is there a way to format a cell (without a long convoluted formula) so it
> rounds the fractional part of a number into either halves or thirds? For
> example, 1.2 would be displayed as 1 1/3. Or 1.56 would be displayed as 1
> 1/2. The application would be for photography where it is common to compare
> "stops" in increments of either halves or thirds.

Reply to this Message

David Farber - 11 Jun 2006 18:06 GMT

That is close to what I wanted but it doesn't limit the denominator to
either a two or a three. For example, .8 formats to 4/5. I would like it to
display, the closest value to either 1/3, 1/2, or 2/3. Though 4/5 is the
exact number, I just want approximations with the highest precision to be
1/3. Most cameras only have adjustments in either 1/3 or 1/2 stop
increments.

Thanks for your reply.

David Farber

> Try this:
>
[quoted text clipped - 18 lines]
> > 1/2. The application would be for photography where it is common to compare
> > "stops" in increments of either halves or thirds.

Reply to this Message

Beege - 12 Jun 2006 13:23 GMT

David,

Can't get you an either/or in the same cell, maybe you can have two adjacent
cells that give you different answers.

Format/Cells/Custom

# ?/3
in one column of cells

# ?/2
in the other

Beege

> That is close to what I wanted but it doesn't limit the denominator to
> either a two or a three. For example, .8 formats to 4/5. I would like it
[quoted text clipped - 34 lines]
> compare
>> > "stops" in increments of either halves or thirds.

Reply to this Message

David Farber - 15 Jun 2006 19:25 GMT

I took some time and came up with the following formula. It is not very
elegant but it does the job. Note, cell C4 is the example cell and should be
formatted to # ?/?

=IF(C4-MID(C4,1,FIND(".",C4)-1)<0.166,MID(C4,1,FIND(".",C4)-1),IF(C4-MID(C4,
1,FIND(".",C4)-1)<0.4166,MID(C4,1,FIND(".",C4)-1)+1/3,IF(C4-MID(C4,1,FIND(".
",C4)-1)<0.5833,MID(C4,1,FIND(".",C4)-1)+0.5,IF(C4-MID(C4,1,FIND(".",C4)-1)<
0.833,MID(C4,1,FIND(".",C4)-1)+0.666,ROUND(C4,0)))))

--
Thanks for your reply.

David Farber
L.A., CA

> David,
>
[quoted text clipped - 49 lines]
> > compare
> >> > "stops" in increments of either halves or thirds.

Reply to this Message