# Formating fractions for stocks

Posted by Bryan on March 26, 2000 6:27 PM

Hello all,

I'm trying to format cells to use reduced fractions with my stocks in a portfolio I'm working on. I can't seem to figure out how to reduce are force standard market fractions such as 64ths 32nds 16ths and so on. When I use the standard option for 16ths I get things like 8/16 instead of 1/2.

Thanks

Bryan

Posted by Celia on March 27, 2000 3:06 AM

Bryan

I dont 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

Posted by Ivan moala on March 27, 2000 3:22 AM

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

Ivan

Posted by Jaime on March 27, 2000 2:32 PM

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

Posted by Celia on March 27, 2000 4:01 PM

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

Posted by AB on March 28, 2000 6:27 AM

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.

Posted by Celia on March 28, 2000 5:14 PM

Aaron

Unless Im doing something wrong, I dont think either Ivans or Jaimes solutions give what is required.

For example, lets 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 (Bryans 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

Posted by Celia on March 28, 2000 5:47 PM

I think Ive 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

Posted by Celia on March 29, 2000 2:51 AM

I have just noticed that Mr Excels 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

Posted by Bryan on March 29, 2000 4:16 AM

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.

Posted by Jaime on March 29, 2000 5:56 AM

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.

Posted by AB on March 29, 2000 7:03 AM

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)

Posted by Celia on March 29, 2000 3:43 PM

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 dont think the purpose of Mr. Excels 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.