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
Beege said:
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
David Farber said:
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
Ron Coderre said:
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.
--
Thanks for your reply.
David Farber
L.A., CA
