Cookbook fractions

Factual Questions
1

As part of this 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 a number converted to a fraction to halves and quarters and thirds?

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

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

 If the number I want to convert to a fraction is 7.1 cups then I want the answer to be 7 cups, because that rounds closest to any half or quarter or third.

 If the number I want to convert to a fraction is 7.2 cups then I want the answer to be 7 1/4 cups, because that rounds closest to any half or quarter or third.

 If the number I want to convert to a fraction is 7.4 cups, then I want the answer to be 7 1/2, because that rounds closest to any half or quarter or third.

 But if the number I want to convert to a fraction is 7.3 cups, then I want the answer to be 7 1/3, because that rounds closest to any half or quarter or third.

Can this be done? Can Excel be forced into rendering numbers only as thirds and halves and quarters (and integers, of course)? I’ve played around with this for a couple hours, including a fair amount of online research, and I’m nowhere. The problem is that I’m a moron, so I’m hoping someone here who isn’t can answer my question.

2

Format>cells/rows/columns>choose fractions from drop down>choose one digit conversion

Any decimal amount will convert to the nearest fraction.

3

7.4 is closer to 7 1/3 than 7 1/2.

4

This is probably adequate for the stated purpose but be aware that this method does not convert the number, it just displays it in a certain way. If you were doing arithmetic with these numbers then results would be rife with rounding errors.

Also, for most cooking small difference in quantities don’t matter but baking is chemistry and you should not take a tested recipe then round numbers.

5

I’m not sure about this (I don’t have a copy of Excel handy to test), but I think the one-digit fraction method will allow fractions like 1/5 and 1/7 which aren’t desired.

Unless someone has a better solution, a brute force way to do it would be:
Multiply the value by 12 and round to the nearest integer.
Check the result modulo 12 and make these adjustments:



if 1, subtract 1
if 2, add 1
if 5, add or subtract 1
if 7, add or subtract 1
if 10, subtract 1
if 11, add 1

Finally, divide by 12 and display the result as a fraction.

6

running coach, your suggestion, at least using my Excel 2016, can produce results such as 1/7 and 3/5 but the question is how to restrict the fractional parts of the answers to halves, quarters and thirds. I think maybe the solution involves rounding and dividing and who knows what else?

CookingWithGas, you seem to have missed the same point running coach did, which is that “the stated purpose” is to restrict the fractional parts of the answers to halves, quarters and thirds.

pulykamell, you are right, and good on you. May I amend the 7.4 cups to 7.45?

Thanks to you three for responding.

7

Johnegee, you may also find you have a problem if the recipe calls for 1/3 of a teaspoon. In that case, your rounding-down algorithm would suggest that you put in a 0 amount of that item. For salt, probably a good thing; for baking powder in a baking recipe, probably not a good idea.

Also, you may want to expand your list of fractions to at include 1/8.

8

Probably not a bad idea to just skip the thirds and use 3/8 and 5/8 instead. Should be close enough.

Been a while since I programmed a worksheet but couldn’t you use a lookup function where if the decimal is a certain range you use a certain fraction?

9

Simple answer - do away with that vile measure - cups. Stick to ounces for weight and fluid oz for volume.

Much better still, use grams and millilitres.

You could easily include a conversion table to cups, handfuls or pinches.

10

February 16, 2017

PoppaSan, you seem to have misunderstood the purpose of the conversion table, which I realize I didn’t make clear enough. I now agree that I should add eighths to the list of quarters and thirds and halves, thanks to BobArrgh, but your suggestion to ignore thirds ignores that fact many U.S. recipes use thirds.

bob++, you seem to have even more thoroughly misunderstood the purpose of the conversion table I propose. I would have thought it was obvious I can’t do away with “that vile measure,” cups, since hundreds of thousands if not millions of recipes use them. Did you think that by not allowing them into my table everyone would suddenly pretend they don’t exist or that they would somehow miraculously disappear from all recipes and be replaced by some equivalent you approve of? And FWIW my table already includes the metric equivalents, but they’re irrelevant to my question how to restrict U.S. equivalents to eighths, quarters, thirds, and halves.

11

I doubt it. Most people don’t have measuring cups/spoons that include 1/8th measures, and it’s probably better to assume that people using a spreadsheet to do conversions are functionally innumerate and aren’t going to be able to work out that 5/8th is about 2/3.

I haven’t checked markn+'s math, but his suggestion is likely the right way to go.

12

OK, this works - it’s horribly inelegant, and I am not proud of it, but it is capable of exactly what you asked for

Enable the developer menu, as detailed here:

In the developer menu, click Visual Basic

In the Visual Basic menu, insert a new Module

In the module, paste in this code:



Function FuzzyRound(Inputvalue As Double) As String
Dim DecimalPortion As Double
Dim resultString As String
Dim JoinString As String
Dim FuzzyPart As String

If Int(Inputvalue) > 0 Then
    resultString = Str(Int(Inputvalue))
    JoinString = " and "
End If

DecimalPortion = Round(Inputvalue - Int(Inputvalue), 1)

Select Case DecimalPortion
Case 0, 0.1
FuzzyPart = ""
JoinString = ""
Case 0.2
FuzzyPart = "a quarter"
Case 0.3, 0.4
FuzzyPart = "a third"
Case 0.5, 0.6
FuzzyPart = "a half"
Case 0.7
FuzzyPart = "two thirds"
Case 0.8
FuzzyPart = "three quarters"
Case 0.9
resultString = Str(Int(Inputvalue) + 1)
JoinString = ""

End Select

FuzzyRound = resultString & JoinString & FuzzyPart

End Function

(you might want to tweak the actual value-to-phrase settings in the case statement)

Save your spreadsheet as macro-enabled

Supposing your decimal cup measure value is in cell A1, go to cell B1 and enter =FuzzyRound(A1)

It should translate the values as per the following examples:



0.1	
0.2	a quarter
0.3	a third
0.4	a third
0.5	a half
0.6	a half
0.7	two thirds
0.8	three quarters
0.9	 1
1	 1
1.1	 1
1.2	 1 and a quarter
1.3	 1 and a third
1.4	 1 and a third
1.5	 1 and a half
1.6	 1 and a half
1.7	 1 and two thirds
1.8	 1 and three quarters
1.9	 2
2	 2
2.1	 2
2.2	 2 and a quarter
2.3	 2 and a third
2.4	 2 and a third
2.5	 2 and a half
2.6	 2 and a half
2.7	 2 and two thirds
2.8	 2 and three quarters
13

Hm, that’s similar to my algorithm but less accurate due to your use of tenths rather than twelfths. For example, yours will report 1.44 as “1 and a third” rather than “1 and a half” (error of 7.4% rather than 4.2%).

14

Agreed - if the input figures are already rounded to 1 decimal place, converting them fairly to quarters and thirds is a horrible mess.

If the input values are not rounded, I would probably replace the Select Case with If-Then_ElseIf, using ranges (e.g. >=0 and < 0.3)

15

Oh, English Nations on the Planet!

You have given us so much: science, knowledge, poetry!

Can’t You, for one, accept that pesky, French, decimal notation? That 1 1/2 pt actually equals (it’s not like English is a god-given default!) 0.6 l?

Things would be so much easier then!
Then, let’s proceed to the Fahrenheits…

16

Sure - if I’m creating recipes, and the ingredients require any kind of precision (which is not always), I use metric weights and volumes, except for the small amounts, which may be in teaspoons or tablespoons.

But in the USA, many people don’t have kitchen scales, because they have measuring cups, and because they have measuring cups, they want recipes in cups, and because they get recipes in cups, they don’t need scales.

17

February 18, 2017

You’ll remember that the problem is how to display fractions only as eighths, quarters, thirds, and halves.

Finding no solutions I could figure out how to implement after trying on several forums for several days, with thanks to markn+ and Mangetout I bore down and tried harder, and this is all I came up with.

=IF(D52 - INT(D52) >= 0.9375,INT(D52)+1,IF(D52 - INT(D52) >= 0.8125,INT(D52) + 7/8,IF(D52 - INT(D52) >= 0.708333,INT(D52) + 3/4,IF(D52 - INT(D52) >= 0.6458333,INT(D52) + 2/3,IF(D52 - INT(D52) >= 0.5625,INT(D52) + 5/8,IF(D52-INT(D52) >= 0.4375,INT(D52) + 1/2,IF(D52 - INT(D52) >= 0.3541666,INT(D52) + 3/8,IF(D52 - INT(D52) >= 0.2916666,INT(D52) + 1/3,IF(D52 - INT(D52) >= 0.1875,INT(D52) + 1/4,IF(D52 - INT(D52) >= 0.06,INT(D52) + 1/8,INT((D52))))))))))))

where D52 is the number the user enters in a row labelled tablespoons or deciliters or gallons or some other unit to be converted. The fractions will appear only for results in U.S. traditional units, not any of the metric answers.

The constants in the formula are meant to be exactly halfway between any two consecutive fractions such as 3/8 and 1/2. The >= rather than > favors adding less, doesn’t it? Is this a good idea?

Also, notice at the tiny end of the range how I’ve changed what would be 0.0625 to 0.06. I figure if it’s that much in the original recipe it is meant to be included.

Is this the best that can be done? Is there a more elegant solution?

And most important, did I get this 100% right? Can you break this formula?

18

>= is useful if you have multiple ranges and you want them to run right up to each others’ edges - for example, if you have:

if X >0 and X<3 then Y=1
if X >3 and X<6 then Y=2

When X is exactly 3, it satisfies neither condition, and the process falls down a gap (and Y is not assigned any value) - so you need:

if X >0 and X<3 then Y=1
if X >=3 and X<6 then Y=2