Reducing fractions to lowest terms.

[Allen Sundholm]

Hi

For the purposes of my job in a government titles office, where at times I have to work out fractions resulting from people transfering parts of a part-share of ownership in land, I prepared a small one-page speadsheet where one can automatically calculate what fraction results when part of a fraction (being a share in land) is being transferred, and what the residue fraction will be. To verify that the calculations are correct, the sum of both of these fractions must always add up to 1 when entered correctly. (I use conditional formats to chnage cell colours to indicate incorrectly-entered fractions).

I use two rows of cells, the top row being for the numerators, and a second row beneath for the denominators. Thus I am not limited by Excel's built-in limitation of 3 digits in benominators in expressing numbers as fractions, and I can automatically calculate for very large unusual fractions I sometimes encounter, such as "what is 489/7896 th's of the transferor's 6784/762543 th share of the land, and what is the fraction of the whole of the land that the transferor keeps?", and so forth. Note that in my job all fractions, being for ownership shares in land, are "proper" fractions.

The spreadsheet all works beautifully. However, just "one leeeetle thing" still needs to be done: the resulting fractions are not, if reducable, automatically reduced to their "lowest terms". If they were able to be reduced, this would make life easier for the next guy who looks at that land title. So I would need to show the reduced fraction on the spreadsheet similarly, i.e. with the reduced numerator in the top cell and the reduced denominator in the cell beneath.

Alas, I don't know how to do this auto-reducing bit, as I am a bare beginner at using Excel. All I can tell you is that in order to do so, my searches on the net show that there are diferent ways of finding the lowest terms, but all appear to involve finding the numerator's and denominator's prime factors, in my case separately, and seeing if they divide wholly, or something like that (yeah, I hate maths too!) thus reducing a fraction to its "lowest terms".

Could some kind soul please possibly assist?

 

[Allen Sundholm]

Many many thanks for all your help. I still think your first solution is along the correct lines, and it works fine, until the fractions I am working with get too big (maybe?), when numbers start to appear after the decimal point. Or maybe Excel is simply falling apart! Your second solution now works, but Excel is rounding off both numerator and denominator when it automatically reduces the fractions, even when using a custom fraction with truckloads of zero's. I showed the prob to a guy here at work who did 5 years of maths at Uni and he cannot help, he suspects, as I do, that Excel itself is the problem. He will look into it further as there may be a "numerical method" (using numbers in computers) solution. I've given up for today, so please don't be surprised if I post the same question tomorrow. Many thanks again!