 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008show fractions rounded up to nearest 1/16th inch
I have cells formatted as fraction, up two digits. We are a mfg. company cutting metal and if it is not on a tape measure, we don't use it. If we input in an odd decimal value, we can get stuff like 17/20 or other wierd unusable fractions. Is there a way to have the same cell of input to show a result rounded up to the absolute nearest 1/16th of an inch? Wouldn't the formatting to sixteenths work? If you go to Format-->Cells-->Number-->Fraction Then click on As Sixteenths? Or does that not meet your criteria? > I have cells formatted as fraction, up two digits. We are a mfg. company > cutting metal and if it is not on a tape measure, we don't use it. If we > input in an odd decimal value, we can get stuff like 17/20 or other wierd > unusable fractions. Is there a way to have the same cell of input to show a > result rounded up to the absolute nearest 1/16th of an inch? I looked at that..., but if it amounts to 1/4", we want it to show 1/4", not 4/16", that will slow down the process doing the conversion in the shop. I know, picky, picky. :-) John > Wouldn't the formatting to sixteenths work? > [quoted text clipped - 9 lines] > > unusable fractions. Is there a way to have the same cell of input to show a > > result rounded up to the absolute nearest 1/16th of an inch? Well, if you do want to be picky, try =IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"# ?/2"),IF(MOD(CEILING(A1,1/16),0.5)=0.25,TEXT(CEILING(A1,1/16),"# ?/4"),IF(MOD(CEILING(A1,1/16),0.25)=0.125,TEXT(CEILING(A1,1/16),"# ?/8"),TEXT(CEILING(A1,1/16),"# ?/16")))) -- David Biddulph >I looked at that..., but if it amounts to 1/4", we want it to show 1/4", >not [quoted text clipped - 20 lines] >> > show a >> > result rounded up to the absolute nearest 1/16th of an inch? David, Can I nest your code with =IF(F10+$H$9=$H$9,0,F10+$H$9) This references the cells that we want to have rounded up values in. This simply adds a number such as 3/4" or 7/8" or another arbitrary number to f10 for a finish size. > Well, if you do want to be picky, try > =IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"# [quoted text clipped - 28 lines] > >> > show a > >> > result rounded up to the absolute nearest 1/16th of an inch? David, This works great, but the results in the cell show a outlined square between my round number and my fraction. Not displaying a space properly. Wierd. But thanks for the code. Now to do something about the space being displayed by a square. I tried to paste it here but it did not show. > Well, if you do want to be picky, try > =IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"# [quoted text clipped - 28 lines] > >> > show a > >> > result rounded up to the absolute nearest 1/16th of an inch? >Well, if you do want to be picky, try > =IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"# [quoted text clipped - 3 lines] >-- >David Biddulph Might be simpler to just use: =CEILING(A2,1/16) and then custom format the cell as # ??/?? --ron Ron, That works and without a oddly displayed spacing character. This site has been a gem to use. Thanks again. John > >Well, if you do want to be picky, try > > =IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"# [quoted text clipped - 10 lines] > and then custom format the cell as # ??/?? > --ron Ron, How do I meld your answer with this =IF(F10+$H$9=$H$9,0,F10+$H$9) in the same cell? > >Well, if you do want to be picky, try > > =IF(MOD(CEILING(A1,1/16),1)=0.5,TEXT(CEILING(A1,1/16),"# [quoted text clipped - 10 lines] > and then custom format the cell as # ??/?? > --ron Presumably =IF(F10=0,0,CEILING(F10+$H$9,1/16)) ? -- David Biddulph > Ron, > [quoted text clipped - 15 lines] >> and then custom format the cell as # ??/?? >> --ron If you don't have "As 16th (8/16)" as an option in the Fraction formatting list then try a Custom format of # ??/16  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I have cells formatted as fraction, up two digits. We are a mfg. company > cutting metal and if it is not on a tape measure, we don't use it. If we > input in an odd decimal value, we can get stuff like 17/20 or other wierd > unusable fractions. Is there a way to have the same cell of input to show > a > result rounded up to the absolute nearest 1/16th of an inch? Not in the same cell, but =CEILING(A7,1/16) and format as # ??/16 -- David Biddulph >I have cells formatted as fraction, up two digits. We are a mfg. company > cutting metal and if it is not on a tape measure, we don't use it. If we > input in an odd decimal value, we can get stuff like 17/20 or other wierd > unusable fractions. Is there a way to have the same cell of input to show > a > result rounded up to the absolute nearest 1/16th of an inch? >I have cells formatted as fraction, up two digits. We are a mfg. company >cutting metal and if it is not on a tape measure, we don't use it. If we >input in an odd decimal value, we can get stuff like 17/20 or other wierd >unusable fractions. Is there a way to have the same cell of input to show a >result rounded up to the absolute nearest 1/16th of an inch? I'm not sure what you mean by "rounded up to the absolute nearest 1/16th of an inch". Rounding UP does not necessarily go to the *nearest* 1/16th. For example, 1/16 + 1/64 would ROUND to 1/16 but would ROUNDUP to 2/16 (or 1/8). Either can be done in the cell of input, using a macro, but you need to decide which you want, and also whether it is OK to actually change the value that you have inputted to be rounded, or rounded up, to the appropriate 16th. Finally, you won't be able to ROUNDUP in the cell of input and NOT change the inputted value. --ron Ron, I have taken everything that you have suggested and came up with a solution that works just fine. Thank goodness we aren't going to the moon on my work here. We make storefront or window framing here, and we have a plus minus of 1/8". So rounding up to another 16th of inch will not really hurt us. What it does is keeps us covered on the plus side for material amounts and costs. Better to be a tad up that low. We can always take some away, but adding on when not budgeted can be problematic. Thanks for all your help. I should have posted in the newbie area. John > >I have cells formatted as fraction, up two digits. We are a mfg. company > >cutting metal and if it is not on a tape measure, we don't use it. If we [quoted text clipped - 13 lines] > inputted value. > --ron Hi John, I think my dad is in the same industry as you and he told me to do something like yours. I'm stuck and google for solution when i come across this website. I convert everything into mm to do some calculation but I don't know how to convert it back to the fraction in inch. Do you mind sharing how you go about doing it? Thanks. Assume you have 10 mm in a A1 In B1 enter =A!/25.4 Format to Number>Fractions>Sixteenths Display will be 6/16 Gord Dibben MS Excel MVP >Hi John, > >I think my dad is in the same industry as you and he told me to do something like yours. I'm stuck and google for solution when i come across this website. I convert everything into mm to do some calculation but I don't know how to convert it back to the fraction in inch. Do you mind sharing how you go about doing it? Thanks. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.