Decimal to inch fractions

[Davidmin]

Need to convert decimal to inch fractions
IE: 4.5 = 4 1/2 inches

 

[Legacy 12630]

what is the MROUND function in french

My French is not so good but it probably something like :-

Monsieur Ronde.

 

[Yogi Anand]

what is the MROUND function in french

My French is not so good but it probably something like :-

Monsieur Ronde.

Oh my goodness -- Ponsonby our resident creative linguist!

 

[Legacy 12630]

 

[PaddyD]

Monsieur Chien, surely?

 

[jimrward]

Hi

As promised from yesterdays posting, been thinking this one out in the bar, after 5 pints of strong beer, these being british imperial pints of european high strength beer, conversions to appear later, maybe.

How do we get 8/16 to equal 1/2, well it turns out a bit long winded as follows.

=INT(A3)&" "&CHOOSE((FLOOR(A3,1/16)-INT(A3))*16,"1/16","1/8","3/16","1/4","5/16","3/8","7/16","1/2","9/16","5/8","11/16","3/4","13/16","7/8","15/16")

(FLOOR(A3,1/16)-INT(A3))*16 will always resolve to a positive integer so we can use the CHOOSE function to pick from our edited list of preferred measurements.

Hope this helps Hic

 

[Legacy 12630]

Hi

As promised from yesterdays posting, been thinking this one out in the bar, after 5 pints of strong beer, these being british imperial pints of european high strength beer, conversions to appear later, maybe.

How do we get 8/16 to equal 1/2, well it turns out a bit long winded as follows.

=INT(A3)&" "&CHOOSE((FLOOR(A3,1/16)-INT(A3))*16,"1/16","1/8","3/16","1/4","5/16","3/8","7/16","1/2","9/16","5/8","11/16","3/4","13/16","7/8","15/16")

(FLOOR(A3,1/16)-INT(A3))*16 will always resolve to a positive integer so we can use the CHOOSE function to pick from our edited list of preferred measurements.

Hope this helps Hic

So I presume you don't like =MROUND(A1,1/16)

 

[Mark W.]

Or...

=FLOOR(A3,1/16)

...formatted as...

# ?/??

 

[rollinshultz]

fraction to nearest chosen denominator

I don't know if this is what you are looking for but I wrote it after finding your question while looking for a decimal to fraction converter. I realized it should be do-able in excel and proceeded over the next half hour to write this which is working great for me. It is made to convert decimals of less than an inch to a fraction in various denominators of a given accuracy. For example if I want my answer accurate to a 64th of an inch I input 64 ibto the denominator box. Like you I had trouble getting the fraction result. I divide the given decimal by the decimal version of the given accuracy level and I get a whole number as a numerator for for use in the fraction. Let's try one example I input .197 a somewhat odd decimal into column B for my decimal. I input 64 into the denominator column because I want accurcy too the nearest 64th inch. column D will tell me what my denominator is as a decimal 1/64" is .016. My decimal is then converted into a whole number of 13 indicating there are 13/64" in .197. Here is where I also had trouble. I would at first get the answer as 13/66 when I did divide column E by column C to display my number as a fraction. I realized even though my numerator showed a whole number of 13 the division was being handled by the actual value. I could not at first see this because I set my numerator column up as a number with 0 decimal places. Once I realized what the problem was it was simple enough to use the round() function in the final calculation of the fraction in column E. Below is the set-up The second row shows the formulas for each column.

Decimal denominator set divider convert fraction
0.250 64 =1/C2 =B2/C2 =ROUND(E2,0)/C2
0.500 128 0.008 64 1/2
0.250 32 0.031 8 1/4
0.187 64 0.016 12 3/16
0.197 64 0.016 13 13/64
0.388 16 0.063 6 3/8
Let me know if this works for you. It does what I need and that is convert a decimal to the nearest 64th" for the purpose of selecting a drill bit for that operation.

 

[i_am_jef]

Or...

=FLOOR(A3,1/16)

...formatted as...

# ?/??

IT's not a timesheet we are talking about, it'S all about a cutlist of some kind.
You bureaucrats, who don't see the impact of little tiny details
I strongly suggest NOT using that formula for the following reason: FLOOR formula rounds down to the nearest multiple, so .0624 would become "0", and, of course, that would not be the wanted result.

Ponsoby's was right:
The MRound formula is the correct formula tu use, and is way simpler compared to some "mod(int(text int/mod..." 15 feet long formulas (that work perfectly fine, but are way too complex for the same result)

Since I often have to work with a precision of 1/32 of an inch, I use
=MROUND([reference cell],1/32)
of course you can always substitute the 1/32 for any other fraction you would need, like 1/16th, or 1/8th.. or again 1/64th.. maybe 1/4 ? .. .. . ..

Don't forget to format the cell as following though:
Custom -> #??/??

Cheers!
JF

 

[SHEETMAGIC]

I know this is an old thread to be digging up. But I am just so thrilled to have found the final solution given here, I had to brush off the cobwebs and say a big THANK YOU to all those who contributed to getting this end result. It works brilliantly.
I was very happy to see there was a simpler way than one of the proposed reaaaaalllllly long formulas (as brilliant as they were).

I am using the =MROUND([reference cell],1/32) solution (with format set to "# ?/??")

I've taken it a step further and I have a separate cell devoted to selecting the fractional accuracy required. That cell has a standard Fraction ("Up to two digits") cell format. To make life even simpler I have set that cell to have a pick list with 1/2,1/4,1/8,1/16,1/32,1/64 as the options. These are picked up from a range of cells also formatted with the standard 2 digit fraction, as I needed to make sure excel treated these as fractions, and was no sure how to achieve that within the pick list box on the Validation Criteria.

So in my case the formula is =MROUND([reference cell],[pick list reference cell])

This way I can change the end result dynamically to different levels of accuracy.

Again, thank you very much for this very helpful solution.

Regards,


Jonathan