Restricting fractions to 1/8, 1/4, 1/3 and 1/2

[Johnegee]

February 16, 2017


Greetings from a Newbie to this Forum,


(I use Office 365.)


As part of a cookbook I’m editing I want to offer buyers an Excel spreadsheet that converts various weights and measures, just as one so often sees elsewhere. My question has to do with converting to fractions. How can I restrict the fraction of a number converted with a fractional part to to halves, thirds, quarters and eighths?


Cups, for example, are often expressed as halves (7.5 cups) or quarters (7.25 cups). But they are also expressed as thirds (7 1/3 cups). Indeed, the two liquid cup measures in my ******* are both delineated into thirds as well as halves and quarters of a cup.


I need eighths because small amounts of a critical ingredient such as yeast could get rounded up to 0.25 or, worse, down to 0.


How in Excel can I restrict any number’s conversion only into halves, thirds, quarters and eighths?


Can this be done? Can Excel be forced into rendering numbers only to the nearest eighth or quarter or third or half (and integers, of course)? I’ve played around with this for a few hours, including a fair amount of online research, and I’m nowhere. The problem is that I’m a moron, so I’m hoping people on this forum who aren't can offer a solution or two.


In case it helps, one suggestion that I don't understand and cannot figure out how to implement in Excel but that does seem to work sometimes somehow involves the MOD(ulo) function.


Thanks to anyone who understands the problem and has some idea how to solve it.

 

[xenou]

I think the problem is with thirds. You could conceivably round to the nearest 1/8 for everything else (since 2/8, 4/8 will get you quarters and halves by reducing fractions). But how would you let Excel "decide" for you whether 0.29 should be expressed as 1/3 or 1/4? So maybe you'd have to round to the nearest 1/3 and nearest 1/8 and then take the closer of the two.

 

[Eric W]

I believe it can be done with a complicated set of 4 Conditional formatting rules. Somewhere in your workbook set up a table like I have shown here in A1:A9:

	A	B	C	D	E
1	1/8		Conditional Formatted	Decimal	Fraction formatted up to 1 digit
2	1/4
3	1/3
4	3/8		1/2	0.5	1/2
5	1/2		1/3	0.333333	1/3
6	5/8		1/4	0.25	1/4
7	2/3		1/4	0.2	1/5
8	3/4		1/8	0.166667	1/6
9	7/8		1/8	0.142857	1/7
10			1/8	0.125	1/8
11			2/3	0.666667	2/3
12			1/4	0.23456	1/4
13			4 3/4	4.76	4 3/4
14			1 1/3	1.35	1 1/3
15			2 3/8	2.4	2 2/5
16			2	2	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet25​

I formatted them as fractions, but the decimal equivalent would be fine. Now choose the column where you want the custom formatting to go. Click Conditional Formatting > New Rule > Use a Formula > then enter this formula:

=CHOOSE(MATCH(MIN(ABS(MOD(C4,1)-$A$1:$A$9)),ABS(MOD(C4,1)-$A$1:$A$9),0),1,2,4,1,3,1,4,2,1)=1

Then click Format... > Number tab > Fraction > As eighths, and click OK.

Repeat the process on the same column, changing the red 1 in the formula to a 2, and choose As quarters.

Repeat again, changing the 1 to a 3, and choose As halves.

Repeat once more, change the 1 to a 4, click Format ... > Number tab > Custom > and enter:

# ?/3

in the Type: box, and click OK.

The formula essentially finds the closest fraction to the fractional part of the number, and chooses a number pointing to the appropriate custom format. The number as you see it may be slightly rounded to make it fit the closest fraction.

 

[Tetra201]

@Johnegee:

If you don't mind rounding your numbers to the nearest fraction, try the following rounding formula (custom format the result as # ?/?):

=INT(A1)+LOOKUP(MOD(A1,1)*48,{0,3,9,14,17,21,27,31,34,39,45},{0,6,12,16,18,24,30,32,36,42,48})/48

 

[pvr928]

Cross posted here: Cookbook fractions - Straight Dope Message Board

 

[Phoenix84]

I think the problem is with thirds. You could conceivably round to the nearest 1/8 for everything else (since 2/8, 4/8 will get you quarters and halves by reducing fractions). But how would you let Excel "decide" for you whether 0.29 should be expressed as 1/3 or 1/4? So maybe you'd have to round to the nearest 1/3 and nearest 1/8 and then take the closer of the two.

Try this formula it is reducing decimals to the closes 1/8

=MROUND(D4,1/8)