Using Improper Fractions as a Formula

Question

Level 1

8 points

Using Improper Fractions as a Formula

Hello,

I am trying to use Numbers to convert a decimal into an improper fraction. The cell that the improper fraction must appear in is the result of a formula. I.e. if a decimal is in cell A1 then I want a formula to make the fraction equivalent of that decimal appear in cell A2 (but it has to be an improper fraction)

Example: 2.63 converted through a formula becomes 1 5/8. How do I make it appear as an Improper Fraction, in this example 13/8?

I know how to do this in Excel by creating a custom formula of ??/?? but I can't figure out how to make a custom format that will do this for Numbers. Formatting the cell as text doesn't work because it has to be the realist of a formula.

Any help appreciated. Thanks.

MacBook Pro (13-inch, 2018, 4 TBT3), macOS Mojave (10.14)

Posted on Nov 6, 2018 7:17 AM

Reply

Answer 1

Badunit

Level 7

30,456 points

Nov 7, 2018 7:09 PM in response to TigersLair

While it is much easier to do this using multiple columns to create the improper fraction, I think it can be done within a cell. Here is my go at it. I make no claims that it works in every case nor do I make any claims about its efficiency and use of the best functions. It does not cover the case where there is no fraction (see row 8 for an example of that) but you can add an IF statement around the whole thing to catch that case if you want. Same thing about blank rows. The formula as written is only good up to 5 decimal places. If your number has more decimal places than that, it will put decimals in the denominator. You can increase it by adding additional zeros to all the 100000's in the formula.

The formula in C2 is =(A2×(100000÷GCD(ROUND((A2−INT(A2))×100000,0),100000)))&"/"&(100000÷GCD(ROUND(( A2−INT(A2))×100000,0),100000))

"Back to decimal" multiplies the value in column B by 1 to make it a number again.

Reply

Answer 2

Badunit

Level 7

30,456 points

Nov 7, 2018 7:34 PM in response to Badunit

Continuing on, my post was just a proof of concept using numeric formulas to try to do it all within a cell. If you do like Ian did, using a column where Numbers formats it as a proper fraction, you can probably make a text formula that would convert that to an improper fraction in an additional column (vs multiple columns).

Unfortunately, I see no way to format a cell as an improper fraction using Number's cell formats or custom formats. It is easy to do in Excel.

Reply

Answer 3

Badunit

Level 7

30,456 points

Nov 8, 2018 1:59 AM in response to Badunit

My go at a text format formula.

Column F is the decimal formatted by Numbers into a proper fraction. The formula in G2 is

=ROUND(F2×IFERROR(RIGHT(F2,LEN(F2)−FIND("/",F2)),1),0)&IFERROR("/"&RIGHT(F2,LEN( F2)−FIND("/",F2)),"")

If you remove the ROUND part of the formula, the results will be exact but there may be decimals in the numerator of the improper fraction.

Reply

Answer 4

Yellowbox

Level 7

21,939 points

Nov 8, 2018 1:44 AM in response to TigersLair

Hi TigersLair,

This may get us started.

Column A is formatted as Number. Enter a decimal number.

Column B =A formatted as Fraction.

Column C =LEN(B) (length, the number of characters in B)

Column D =IFERROR(FIND(" ",B2),0) find the position of the space (if any) in B

Column E =FIND("/",B2) find the position of the division symbol / in B

Over to you.

Column F find the integer (if any)

Column G find the Numerator

Column H find the Denominator.

Numbers is very good at converting text to numbers, but that is as far as I have got.

Happy Numbering!

Regards,

Ian.

Reply

Answer 5

Badunit

Level 7

30,456 points

Nov 8, 2018 5:27 AM in response to TigersLair

Anywhere in the formula that has F2, replace it with (F2−1).

Reply

Answer 6

Barry

Level 9

60,023 points

Nov 7, 2018 1:06 AM in response to TigersLair

"Example: 2.63 converted through a formula becomes 1 5/8. How do I make it appear as an Improper Fraction, in this example 13/8?"

Ummmm…

two and sixty three hundredths is not going to convert to one and five eighths in any notation.

2.625 is 2 5/8

2.63 is not expressible as an improper fraction with any denominator less than 100.

"…because it has to be the realist of a formula."

What definition are you using for "realist" in this statement? OED does not appear to supply one that fits.

Regards,

Barry

Reply

Answer 7

TigersLair Author

Level 1

8 points

Nov 8, 2018 2:11 AM in response to Badunit

Hello Badunit,

Thank you for your reply. This is the closest to what I need. It would be easier to be able to split the problem down across multiple cells, however I needed a formula to make it happen in one cell. Your above recommendation is excellent in forcing the result to be an improper fraction because, as you say, numbers does not appear to allow this to be custom formatted as you can with excel.

Just one more addition to the problem which I neglected in my initial post... I'll continue with the example of the same decimal as before to try and keep this understandable. 2.63 converted by numbers to fraction is 2 5/8. I need this number to be minus 1 (so it equals 1 5/8). Again this is easy on its own... If 2 5/8 is in cell A1 then in A2 we put =A1-1

What is the best way of combining that with your formula? : =ROUND(F2×IFERROR(RIGHT(F2,LEN(F2)−FIND("/",F2)),1),0)&IFERROR("/"&RIGHT(F2,LEN ( F2)−FIND("/",F2)),"")

The easy way would be to have 3 cells, as below, with the first cell containing the decimal 2.63 changed by numbers into a fraction, the second cell = a1-1, and the third cell being your formula relating to cell a2.

2 5/8

1 5/8

13/8

How do I achieve this with only 2 cells, as below - any ideas?

2 5/8

13/8

Thanks again.

Reply

Using Improper Fractions as a Formula

Similar questions