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.

 

[Scott Huish]

If it's just for visual, how about formatting the number as a fraction with the type of up to one digit?

 

[Johnegee]

Because that would allow results such as 1/7 and 3/5. I want to restrict the possible displays to what cooking amounts typically display as. To be more explicit, I want to restrict the fractional parts of any results to eighths, quarters, thirds, halves and, of course, integers, whichever is nearest to the initial value.

 

[mole999]

have a look at custom format, fractions are there

 

[Johnegee]

mole999, can you be more specific? If you were just making a suggestion, please know that I did examine the custom format a few days ago and couldn't figure out how to use it to accomplish my goal. I still can't. If you know that your idea will work, please provide more details. Thanks.

 

[Scott Huish]

I know that theoretically that would allow things like sevenths and such but recipes don't typically call for ingredients in those kinds of measurements and therefore doubling or tripling them shouldn't result in any fraction that is like that.

 

[Johnegee]

Scott Husih, I fear you really don't understand the question I am asking. It's how to do something in Excel, not whether the table I propose looks the way you want it to.

 

[shg]

You can't do it with formatting alone -- it would require discipline, a formula, or (overkill), VBA

 

[AkaTrouble]

there are few options for displaying fractions not sure if any meet your exact requirements

https://support.office.com/en-gb/ar...ractions-0121ecac-1773-4f2d-8cd3-7db51fd83b77

 

[Scott Huish]

Scott Husih, I fear you really don't understand the question I am asking. It's how to do something in Excel, not whether the table I propose looks the way you want it to.

I never said that. You said you were doing a cookbook. The first step in solving a problem is to get it to its simplest form. So the question of whether a fraction of 1/7th is possible is very valid. It was just a question.
If it solved your problem, then it would've been helped and you would've been happy but I'm going to bow out of this because asking questions seems to anger you.