Extracting parts of a fraction in Excel 2

[rharr]

Hello.

I use Excel a lot for metric conversions as I draw in both inch and metric in AutoCAD. The problem with AutoCAD comes with using fractions - AutoCAD doesn't like fractions with decimals.
Because of this, I use 5/127 or 127/5 for metric conversion instead of 25.4.

How can I get Excel to extract the denominator of a fraction for a multiplier?
If the answer to a formula is 74.4mm, I'll use 372/127 to draw in inches. That's easy. Multiply 74.4 by 5/127.
If the answer is 204.75, it isn't so easy. I need to multiply by 4 first to remove the decimal portion.
By putting the answer in fraction form, 204.75 becomes 819/4. Now I want to pull the 4 out and use it as an additional multiplier with 5/127.

Can this be done? Can anyone suggest a work around?

 

[MintJulep]

Don't use fractions?

If your fractions are in Excel as a STRING such as "1/234.5", then

=VALUE(RIGHT(A1,LEN(A1)-FIND("/",A1)))

will return everything to the right of the "/", as a number.

 

[rharr]

Excel-ent!

The fraction is the answer to a formula formated as a fraction, so it wasn't entered as a string. However, using the text function, and a helper cell, it can be converted to a string and then your formula works great.

Thank you.

 

[HgTX]

Why don't I understand this question at all? (I understood the answer, just not the question.)

Hg

Eng-Tips policies: faq731-376

 

[IFRs]

In Autocad why not use alternate dimensions and get both imperial and metric?

 

[MintJulep]

Truth be told, I didn't understand the question beyond "extract the denominator of a fraction".

 

[rharr]

HgTX,

Do you use AutoCAD?
My final drawings for the shop floor are dimensioned in inches. A lot of the parts I redesign and draw were originally metric based designs. When I copy a feature and want to displace it 25mm I use 125/127 as a displacement {(5*25)/127}. That way I can draw in both metric and inch at the same scale. My version of AutoCAD won't let me use 25/25.4 as a displacement because of the decimal in the denominator.

During the redesign process a calculation may come up with a value that includes a decimal. Multiplying 204.75, for example, by 5 doesn't give me an integer.

The helper cell, with a text function and a fraction format, has a fraction that can be picked apart by MintJulep’s formula. The original cell contains the number without altering it’s integrity.

I then take the original answer and multiply it by 5 and by MintJulep’s result and place it in a cell called “numerator”. Another cell, called “denominator”, contains 127 times MintJulep’s result.

Using 204.75 as an example, MintJulep’s formula returns a 4 from the fraction 819/4.
The cell “Numerator” contains 4095.
The cell “Denominator” contains 508.
Now, I can enter 4095/508 as a displacement for 204.75mm in AutoCAD.

Is that clearer?

Maybe you have a better work around in AutoCAD. I've drawn portions of a part in metric, scaled it by 5/127, and married it to the remainder of the part, but that seems to take more time with some designs.

 

[MintJulep]

It's been a long, long time since I've needed to do any AutoLisp programming.

Does AutoCAD still use AutoLisp, or is there some other extension language now?

Regardless, certainly you could program a custom button or menu pick that would do all of that without having to resort to external calculations.

 

[MechEng2005]

Use your conversion factor and just format the cell as special and enter "??/####"

Cell A1: 204.75
Cell B1 (with special formatting): =A1*5/127

And the result is 4095/508.

Easy as that!

- MechEng2005

 

[rharr]

MechEng2005,

Wow. It really is that easy.
I guess I was trying to get Excel to do what I've been doing in my head not thinking it could reverse the steps. I've been doing the simple multiplication to remove the decimal and then multiplying by 5. Excel can easily make 1023.75/127 a fraction without the decimal.

IFRs,

How do you draw in both units? We have existing parts drawn in inches, and a shop floor that thinks in inches, and redesigned metric parts that need to work in both of those environments.
If I'm detailing it completely as an original part, I have no problem using alternate dimensioning.
The problem occurs when I'm thinking metric in an inch AutoCAD file.

MintJulep,

We have AutoCADLT 2000. It doesn't have the LISP capability.

Thanks for all the help. I learned a few things today along the way. And, isn't that what it's all about, anyway?

 

[cdi12]

rharr:
You can type in (/ 25 25.4) on AutoCAD command prompt. Will this help?

 

[rharr]

cdi12,

How?
I tried it exactly as you have it typed and as soon as I type a space after the / I get an invalid point notice. I also get the invalid point notice with 25/25.4.

What version of AutoCAD do you work with? My AutoCADLT 2000 won't let me enter fractions with decimals at the command line.

 

[cdi12]

rharr:
You have to type in both parenthesis (/ 25 25.4)

 

[rharr]

It ain't workin' here.
Either I'm doing something wrong or it just won't work with my version of AutoCAD.
As soon as I enter the space after the / I get the invalid point message.
Are you doing this for displacement of a feature being copied or is this a different command?
I tried to enter this without it being part of the copy routine and AutoCAD came back with "LISP command is not available." immediately after I entered the space after the /.

 

[cdi12]

I forgot you use AutoCADLT and it doesn't support AutoLISP. You may try LT-Extender 2000 Plus

http://www.cadopolis.com/shareware/...ory=&TheSubcategory=&StartNo=260&TheKeywords=

 

[zuccus]

In AutoCAD draw everything to full scale. If it is in inches, then keep the same. If it is mm, then scale the part down using the cal command.

Pick your base point.

When it ask you for you scale factor.
type in the following
'cal
1/25.4

Then when you dimension your part. Goto your dimension stadnard and turn on your alternate units to display the metric equiv. in mm.

Zuccus

 

[rharr]

I can't get 'cal to work. Maybe that's because I'm in AutoCADLT 2000.

Thanks for all the advice. Some of your suggestions bring up another topic. Am I the only one who needs to draw in both inches and millimeters on the same part?

The simplest example might be putting an M24x3.0 thread in the end of a part that has a diameter of 2.50".

Sure, I can draw the thread in millimeters, scale it down, and place it properly. Or, I can scale up the part, put the thread in, and scale it back down. If it's a simple part, that's not a big deal. But, by using the 5/127 factor I can draw the major diameter as 120/127 and the minor diameter as 105/127 and I'm done.

 

[satchmo]

Set your snap to 10/254 or .03937007 to draw in mm. Do as zuccus says and draw everything full scale.

 

[rharr]

That's an interesting solution. But, yuk. Drawing with the snap to grid on is annoying. I feel boxed in. I'd like to watch one of you do that to see how you overcome some of the issues I see.

You say "Set your snap to 10/254 or .03937007 to draw in mm." and "draw everything full scale." How do you do both? Aren't you forcing the grid to be a conversion from mm to inch. You aren't really drawing in mm, are you? You're drawing in inches because that's what the grid is set in, right? I must be missing something.

Let's say we draw a line in space. (Not attached to anything else in the drawing.) The ends will be snapped to the grid. Now you want to offset that line 1.5mm. Don't I need to convert that to inches? Or, use 15/254?

Now try to draw a line from the end of the offset line. It's not on the grid. So, unless I zoom out to a level where the grid is smaller than my pick box, I can't grab the end of the line.

Do you turn the snap on and off all the time? What about the object snaps? Do you not have any selected and you select them as needed?

Over the years I've found it quickest to have the snap to grid off and the object snaps on and set to my most frequent modes (endpoint, midpoint, center, node, intersection, and insertion). Why should I specify the snap mode each and every time I place something?

I would really like to watch someone who knows what they're doing. I've been drawing in AutoCAD for about 13 years and except for someone shadowing me the first week, I'm pretty much self-taught. I admit there's a lot I don't know.

 

[IFRs]

Now I see your problem. In the same drawing you draw one drawing unit equals one MM and ALSO one drawing unit equals one inch. I guess if I were you, I would draw in the unit system that was most common, create a macro or button that would scale selected objects to the other unit system. So, for instance, I would draw in inches and when I needed a metric bolt I would draw that pretending that each drawing unit was 1 MM and then scale the objects by 1/25.4 to make them suit the inch based drawing. Another option would be to draw the bolt in another drawing and insert it as a block scaled 1/25.4. Unfortunatly I don't think there is an AutoCAD solution to your problem.