Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
reducing a fraction
Is there a functions that will reduce a fraction in a cell?
I'm running a macro that returns whole numbers and fractions as it's end result. Sometimes the fractions are in 1/16's other times the fraction is in 1/8's and 1/4's. I need the fraction that is being returned to always be reduced. Right now if I format the value of the cell to be a fraction in sixteenths and the returned value from the macro is 22 5/8 the cell reads 22 10/16. And if I format the value of the cell to be a fraction in eighths and the returned value from the macro is 22 5/16 the cell reads 22 3/8. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
reducing a fraction
GreenMonster wrote:
Is there a functions that will reduce a fraction in a cell? I'm not aware of one, but then I'm not very "up" on the Excel object model either. From a VB(A) perspective, this is something you can certainly code up, though. Take a look at http://vb.mvps.org/samples/Fraction for a drop-in ready class that handles rational numbers. Really, all you need is the GCD: Private Function Gcd(ByVal a As Long, ByVal b As Long) As Long ' Find greatest common denominator. If (b = 0) Then Gcd = a Else Gcd = Gcd(b, a Mod b) End If End Function You might like the class, though, as it supports all sorts of other fractional fun. -- Working Without a .NET? http://classicvb.org/petition |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
reducing a fraction
format the cell as up to 2 digits under the Fractions portion of the number
format tab. Just be aware that excel will "round" it to meet the two digits. The rounding is only for display purposes - it will retain its decimal value. -- Regards, Tom Ogilvy "GreenMonster" wrote in message ... Is there a functions that will reduce a fraction in a cell? I'm running a macro that returns whole numbers and fractions as it's end result. Sometimes the fractions are in 1/16's other times the fraction is in 1/8's and 1/4's. I need the fraction that is being returned to always be reduced. Right now if I format the value of the cell to be a fraction in sixteenths and the returned value from the macro is 22 5/8 the cell reads 22 10/16. And if I format the value of the cell to be a fraction in eighths and the returned value from the macro is 22 5/16 the cell reads 22 3/8. Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
reducing a fraction
Hi GreenMonster ,
if you want fractions in 1/16's or reduced value you must Round the cell value to 1/16 like : (1/16)*INT(A1/(1/16) (also use Complementary function MROUND(A1,1/16) then format the cell with two digits ie : #" "??/?? 9,75 9 3/4 =(1/16)*INT(A1/(1/16)) 6,50 6 1/2 =(1/16)*INT(A2/(1/16)) 7,34 7 5/16 =(1/16)*INT(A3/(1/16)) 7,17 7 1/8 =(1/16)*INT(A4/(1/16)) 7,02 7 =(1/16)*INT(A5/(1/16)) You can also change rounding to 1/32's ou 1/64's Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show fraction as fraction even if answer is 1 | Charts and Charting in Excel | |||
Reducing the size. | Excel Discussion (Misc queries) | |||
Text fraction to fraction format | Excel Worksheet Functions | |||
How do I change fraction to fraction characters? | Excel Worksheet Functions | |||
Reducing A Number By 1 (one) | Excel Programming |