Formating fractions for stocks

Bryan
I don’t know a way to get the format you want with any standard Excel tools or worksheet functions, but the following VBA code should do it for 16ths. The code formats the cells depending upon the remainder left when the divider is 16.
You can add similar code to handle 32nds and 64ths:-

Dim cell As Range
Dim n As Single
For Each cell In Selection
n = (cell.Value * 16) Mod 16
If n = 8 Then
cell.NumberFormat = "# ?/2"
ElseIf n = 4 Or n = 12 Then
cell.NumberFormat = "# ?/4"
ElseIf n = 2 Or n = 6 Or n = 10 Or n = 14 Then
cell.NumberFormat = "# ?/8"
Else
cell.NumberFormat = "# ?/16"
End If
Next

Celia

Bryan try using the Format/cells/Fractions = upto 3 digits(312/943)

Ivan

What about putting # ??/?? In the CUSTOM NUMBER FORMAT, In the FORMAT-CELLS menu

Jaime
This works if the data input to the cells consists of multiples of 16ths (either exactly or by rounding).
However, if the data input is not like this (for instance, it may be the result of a formula), the fractions will not necessarily be 16ths, 8ths, Qtrs and Halfs as is required for stock price purposes.
For example, a fraction of 15/19ths would not be appropriate.
Is there a way of formatting without VBA that will give 16ths and also change automatically to 8ths, Qtrs or Halfs where appropriate.
Celia

Is there a way of formatting without VBA that will give 16ths and also change automatically to 8ths, Qtrs or Halfs where appropriate.

Ivan already posted the answer to this.

Aaron
Unless I’m doing something wrong, I don’t think either Ivan’s or Jaime’s solutions give what is required.
For example, let’s say that 250 shares are bought at 4 1/16 and 540 are bought at 4 5/16. If the average purchase price is required, the formats that have been suggested produce the following :-

#???/??? gives 4 144/617 (Ivan)
#??/?? gives 4 7/30 (Jaime)
#??/16 gives 4 4/16 (Bryan’s original question)

The result that is wanted for the above example is 4 1/4.

The above results are without any rounding. If rounding is used, different fractions may result but will still not produce 4 1/4.

Celia

I think I’ve worked it out.

Per my previous example, assume the following :-

Total cost = $3,344 .375 (in Cell A1)
Total shares = 790 (in Cell B1)
Format = #??/?? (in Cell C1)

Enter the following formula in Cell C1:-

=ROUND((A2/B2)/0.0625,0)*0.0625

Celia

I have just noticed that Mr Excel’s Tip of the Week for 1/30/00 addresses this very problem of reducing fractions.
His suggestion is the following formula. I think this formula is the worksheet function equivalent of the approach used in my macro posted earlier :-

=IF(MOD(INT(16*(+B2-INT(B2)+0.0312)),16)=0,TEXT(B2,"#"),IF(MOD(INT(16*(+B2-INT(B2)+0.0312)),8)=0,TEXT(B2,"# 0/2"),IF(MOD(INT(16*(+B2-INT(B2)+0.0312)),4)=0,TEXT(B2,"# 0/4"),IF(MOD(INT(16*(+B2-INT(B2)+0.0312)),2)=0,TEXT(B2,"# 0/8"),TEXT(B2,"# 0/16")))))

Celia

Thanks for all the suggestions, I won't have the time until the weekend to try them out. Celia, your right about changing from different formats. For instance one little tool that seemed simple was a decimal to fraction to decimal converter. Excel's standard function just don't do it or I haven't figured them out. I'm looking forward to trying out the suggestions this weekend so thanks again.
Bryan
PS: I'll post the results.

But these last formulas imply having to either change his original formula or adding more cells.
Which is fine nut might there be a way of doing it using only the custom number format.
U can format conditionally by using "[]"
fFor example,
[>.0625]# ??/8,[>.13] ??/16

But I can't find a way of putting the mod formula in here.

That formula in the tips is hideous. I believe it's intended to show how someone had been doing it wrong.

That tip doesn't go far enough to explain how to reduce fractions to a specific interval.

The simplest solution is to use Ivan's suggested format. You need to think of how it would behave as applied to this formula.

=MROUND(A1,1/16)

That formula in the tips is hideous. I believe it's intended to show how someone had been doing it wrong.

I agree that the formula is hideous and was not suggesting its use. I don’t think the purpose of Mr. Excel’s formula was to show how not to do it, but it certainly achieved this purpose.
Did you read my previous suggestion of using the formula =ROUND(A1*0.0625,0)/0.0625 ? - this could also be stated as =ROUND(A1 / 16)*16.
Your formula is a little neater.
Personally, in the absence of a better way(so far), if I had a need to round figures to 16ths and to reduce them further, I would use a toolbar button with the following macro :-

Sub Sixteenths()
Dim cell As Range
Selection.NumberFormat = "# ??/??"
For Each cell In Selection
If cell <> "" And IsNumeric(cell) Then
cell = Round(cell * 16, 0) / 16
End If
Next
End Sub

Celia
PS . You may well be pleased to know that my intention is to make this my last message on the subject.