Fractions To Decimal Conversion
I have received help on this topic in the past and I though I had solved the issue, however I realized recently that my formula will not work on any fractions larger than 1 inch. I am converting machine threads in fraction form to a decimal equivalent. here is an example of the what the entry looks like before it is converted.
ex,
1/220 3A (becomes .5000)or
3/413 2A (becomes .7500)or
114 3A this one will not work with my current formula (should be 1.0000);
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Convert 1/220 3a Fractions Into Decimal
I need to convert the fraction at the beginning of a cells text into the decimal equivelent. for example 1/420 3A would give .2500. I have this formula so far that works perfect for fractions that only contain 1 number on each side of the division sign such as 1/2.
View Replies!
View Related
Unique Fractions To Decimal Formula
I need to find a way to convert the fraction located at the beginning of a text string to decimals. Here is an example of what the cell containing the fraction will look like; 1/420 3A. I need to convert the fraction and save/copy paste the decimal answer to another cell.
View Replies!
View Related
Convert Fractions To Decimals And Decimal To Fraction
I know that if I enter =3/4 in a cell it will give me .75 but is there is formula to convert fractions into decimals or is this the best way to do it? I was also wondering if there is some way to convert the decimal amount to a fraction for measurement conversions I know that =B3*25.4 will convert a decimal to mm and so will the formula =CONVERT(E4,"in","mm") and to convert mm to a decimal a few formula that work are =CONVERT(H3,"mm","in") =MROUND(H4/25.4, 1/8) =H5/25.4 =INT(H6/25.39999918*8)/8 But is there a way to convert a fraction to the decimal and is there a way to convert mm to a fraction instead of the decimal equivalent of the fraction? or maybe a formula to convert mm to inches but instead of the result being in decimal format it would be in fraction format?
View Replies!
View Related
Abnormal Decimal Conversion: Autoimport Statistics From The Web Via Web Query
I am autoimporting statistics from the web via web query. This information imported has a number in the form of a decimal stated as either x.1, or x.2. x.1 represents x1/3 (onethird) and x.2 represents x2/3 (twothirds). I need to use these numbers in a calculation after importing them, but using x.1 or x.2 obviously does not give me accurate results as the numbers should actually be x.3333333 and x.6666666 respectively. What is the easiest way to convert the imported numbers to their actual decimals?...
View Replies!
View Related
Paste Two Decimal Number In Excel Without Extra Decimal Places Appearing
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
View Replies!
View Related
Convert Or Format Decimal To X Digits Without Decimal Point
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently: =IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&""&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&""&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2)))) However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
View Replies!
View Related
Fractions To Numbers?
I have some numbers that I've imported from the internet. Some of them contain fractions (i.e. 7 1/2) where the 1/2 is a single fraction character. Is there a function I can use to automatically turn these fraction characters into numbers when they're imported to excel via web query?
View Replies!
View Related
Concatenating Fractions
I'm trying to create a model for woodworking where I can enter fractional wood sizes and have Excel create panel sizes and board lengths for cabinetry. I can easily get the inputs formatted in fractions, but then I'd like to have a concatenation formula that joins two fractions and displays a panel size as a fraction. However, when I try to do this, the concatenation formula changes the fractions to decimals. Example: cell A1 is 3/4" formatted as fraction. Cell A2 is 1 1/2" formatted as fraction. Let's say I want a to concatenate so that A3 reads 3/4 x 1 1/2 But when I use concatenate (A1," x ",A2) the answer in A3 reads .75 x 1.5
View Replies!
View Related
Formatting Cells As Fractions
I have a spreadsheet to calculate piping offsets, center to center. The formula in B:1 is simple. Take the length in cell A:1 and multiply it by the square root of 2. I then increment the length by 1/8 of an inch through cell A:180 and those results are place in B:2 thru B:180. The results are display to the nearest 1/16 of an inch. But if the actual dimension is eight and one half inch, it is displayed as 8 8/16. For aesthetic reason i wish to display the reducible fractions reduced. So that 8 8/16 is displayed as 8 1/2 inch. I understand that i may go to each cell and individually format it accordingly as halves as quarters as eights but was wondering if there is an easier method.
View Replies!
View Related
Fractions In UserForm TextBox
I know I can use fractions in Excel, eg. 1/4 will yield .25 when formatted for number. I wanted to allow users to enter fractions in a userform, but alas, it seems to come out as text. So I gave up on the idea, and then it hit me that this forum might be able to provide the answer. If so, maybe I'll include it before I finish my project. In a nutshell, I want to have users put in fractions, decimals or whole numbers in the userform so I can use it in formulas. (I haven't given any thought to handling combinations like 1 1/2. Later for that). Is there any way for the Excel to know that the textbox on the userform is a number?
View Replies!
View Related
Time Fractions And User Input
What I am doing is creating a spreadsheet solution to tack race times. These show up in past performances in the following formats in paper copies: 45 2/5 seconds. The end user would have to put in maybe 30 of these times based on the number of contestants. Keeping in mind that the 45 2/5 number will be used in some calculations, I want to give the user the easiest input interface. Meaning instead of inputting like this; 0:45.4 (mm:ss/00 format), I would like them to just enter 45.4. the spreadsheet would input format this cell into a time format that would be able to have calculations done to it. Another example might be 1:35.60 (mm:ss.00 format) which is 1 minute 35 and 3/5 seconds. This would be entered as 135.6. A few questions first: 1) Would it be easier to leave the number inputed alone until the calculations ar finished then translate into a time factor? or 2) would it be necessary to convert into a time factor as inputted be cause of the 60 factor involved with the time factor? Having asked those questions, setting this all up with the exact scope I am working towards, any help or sugesstions as to the best way to proceed would be fantastic!
View Replies!
View Related
CONCATENATE Cells Formated As Fractions
I want to concatenate several cells, some of which are formated as fractions, and have it display those numbers as a fraction. When I perform the function, it displays the cell in its decimal form. Example: =CONCATENATE(E2," ( ",F2," in. "," ",G2," ",H2," in. ", I2," ",J2," in."," ) "," color #") Where: F2, H2 and J2 are fractions. Result: 12" Stretcher ( 11.625 in. x 23.625 in. x 3.625 in. ) color #
View Replies!
View Related
Format Cells As Fractions Of Seconds
I am trying to set up a workbook to record times for our swimming club. This will involve recording the swimmer's time as minutes, seconds, tenths and hundredths. I would then want to be able to compare the new time to the swimmer's previous personal best and calculate the difference.
View Replies!
View Related
Importing Measurement Fractions As Numbers Not Dates
I am creating a spreadsheet for use in a manufacturing environment to create a costing and cut list spreadsheet for production set up. The intent is that anyone can use this by copying and pasting an Excel Bill Of Material (BOM) that is generated out of a CAD program onto sheet 1. On sheet 2 (which they don’t touch) is the costing and cut list spreadsheet that references sheet 1 and automatically generated the numbers to create cut sizes and costs. The first challenge I had was that the measurements include the inch symbol. For example if the imported text 15 5/8" is in cell A1 I am able to remove the inch symbol by using the following formula on sheet 2: =LEFT(Sheet1!A1,LEN(Sheet1!A1)1). The result is: 15.6250 which is exactly what I want. Here is the problem I am having. When the listed dimension is less than 1” and is 5/8” for example I get ###### because I have the cell formatted as a 4 decimal place number. If I change the formatting to number with zero decimal places it becomes 39941 which is how many days it has been from January 1, 1900 to May 8, 2009. The question is what formula should I use to change the 5/8” imported text so it says 0.6250 and can be used in math formulas? Please keep in mind that the imported text could be a measurement with a small number like 1/16” to a large one such as 284 3/4" and that the imported text “is what it is” and cannot be manually changed or adjusted (because that presents the risk of people creating errors and costing the company money by ordering the wrong material sizes).
View Replies!
View Related
PDF Conversion Code
The following codes publish the worksheets "Estimate" & "Photos" to a PDF file, it all works great accept that the sheets remain selected in the WB after the sheets have been published. How do I unselect the estimate and photos sheets?
View Replies!
View Related
Yen Conversion Macro
I have a worksheet with values in yen in the C column. My boss (who will not get it through her head that I am not a programmer) wants a macro that will take a yen/$ conversion rate (e.g., 88 yen/$) in cell J1 and convert all the amounts in column C into dollars with a 5% markup and rounding up. If I was doing this as a worksheet calculation rather than a macro, the calculation would look like this:
View Replies!
View Related
Number Conversion
I have two sets of data that I need which is obtained from one source. Basically, the stats I am working with in excel are Processing Time (minutes/seconds) and Volume Per Hour. I desperately need a formula that will allow me to convert the processing time into Volume per hour. Example: Average Handle Time, 7min 7 seconds = x per hour. If it was just 7 minutes flat I could simply divide that into 60. I also can't make it 7.7 and divide that because 7.7 doesn't equal 7m7s. Does anyone have any thoughts on a way to do this? I found a conversion chart online that allowed me to do this work manually 'Conversion Chart: Fraction of an Hour to Minutes', but I haven't determined a way to make a formula out of this.
View Replies!
View Related
Hyperlink Conversion
I have just been told that one of our servers is being replaced this weekend. I have thousands of hyperlinks in excel docs that reference this server. Other than manually reestablishing the links one by one to the new server, is there a way to do some kind of global change to replace the old server name with the new server name? These are not hyperlink formulas, but rather Insert>>Hyperlink. Or, is there a way to convert these hyperlinks to hyperlink formulas that would omit reference to the server and thus not be broken during the conversion? Grasping at straws because I cannot envision having to relink these one by one.
View Replies!
View Related
Scoring Conversion
If I have a scoring grade for different categories (8 in total) & the grades looks something like that: 1a=500, 1b=400, 1c=300, 2a= 250, 2b=210...& so on till 4c, but with different numbers for each category. I need to come up with a total score of points if a person is given a certain grade from the above. ex. if they scored 2a on a certain category, 1c on another & with all the rest of 8 categories scored. The number i need to come up with is the total points.
View Replies!
View Related
Time Conversion
I have an spreadsheet that is monitoring the productivity of agents, as well as the time that it takes to complete each task. My workforce keeps time as .50 = 30 minutes. So 7 and half hours is actually 7.5. Agents are putting the actual time of it took such as 15 minutes however I would like to insert a formula that will convert this for me manually. I know I could give them a conversion sheet, but it took quite a while to get them to actually be able to work in excel. So I would like to add this formula.
View Replies!
View Related
Currency Conversion
I am working on a sheet that logs sales enquiries and quotations. The quotations may be made in either Dollars, Euros or Pounds. However, I want the 'reporting' column to be Pounds. I would appreciate any advice on the simplest way of achieving this. Imagine column A is dollars, column B is Euros and column C is pounds. Most of the time the quotations are done in pounds and the other two columns will be blank. Also, there will only ever be one figure quoted per row. Would it be easier to create a fourth column (D) to consolidate the three separate figures together? We are using fixed exchange rates for the year, so to convert to pounds we will multiply any dollar quotations by 0.5 and any euro quotations will be multiplied by 0.69.
View Replies!
View Related
Date Conversion
I am trying to convert numbers from three cells into a date in one cell. I tried using the =DATE function, but it did not work. In A1, B1, C1, I have 1, 15, 2002. I would like cell D1 to take the info from the preceeding three cells and display "January 15, 2002" Presently, I have A1...C1 formatted for "General." D1 is formatted for "Date." Yet I get the answer "August 23, 1907" (or 2792 as 'General').
View Replies!
View Related
IBM DB2 Date Format Conversion
I have come across an issue and I am not sure what the best way is to achieve my goal or if it's even possible. We are importing dates that are in IBM DB2 Date format. Example: the date is imported as 1060318(C/YY/MM/DD), which is is actually 2006 March 18th. I would like to convert 1060318 to something that Excel can format into March 18th 2006.
View Replies!
View Related
Conversion From 2003 To 2007
My company used to have Excel 2003. I had a PivotTable in a worksheet that accessed data in another, completely different worksheet. When we converted to Excel 2007, I saved many of my Excel files from .xls to .xlsm. My PivotTable that once accessed data from another .xls file is still trying to access that same .xls file. What I want, though, is for it to access the new .xlsm file.
View Replies!
View Related
2000 To 97 Conversion Issue
I have built a nice employee time tracking device in Excel 2000. It is pretty self explanitory ("IN" time, "OUT" time, total time, etc). What I am having problems with is the VB code that is behind the "OUT" time. In essence, once an "OUT" time is entered, the ms error message box is overwritten to ask what type of break the person took (No Lunch, 1/2 hour, 1 hour) and takes the response and implements it into the Total hours calculation. Works great in 2000, but the users are getting problems in 97. i don't have 97 and can't seem to trouble shoot it. Could some guru with 97 have a look at this and see what exactly I am missing in 97. The code is a manipulation of Ken Getz's brilliance by Stratos. Which can be viewed at the following: http://groups.google.com/groups?hl=e....warwick.ac.uk
View Replies!
View Related
Amount To Words Conversion
I am using excel for invoice making...In the end i have a total amount. I need to type it in words for every invoice at present. How can i convert it to words automatically? Is there any formula to it? Or is there anything else? Please suggest me friends.
View Replies!
View Related
Date Conversion Macro
I use the following codes to convert YYYYMMDD into DD/MM/YYY =DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) I use this on quite a regular basis, I would like to convert this to a macro so I can simply select the cell/cells and press the button. Firstly is this possible and can anyone provide a script?
View Replies!
View Related
Time Conversion Formula
I have 4 columns: A B C D 0:00 1:17 56.15 0.06 Column A is in hours:minutes:seconds.milliseconds Column B is in hours:minutes:seconds.milliseconds Column C is in seconds.milliseconds Column D is in hours:minutes:seconds.milliseconds I would like to represent all these columsn in this format: hours:minutes:seconds.milliseconds Is there a simple formula I can use? I was looking at the CONVERT function, but it doesn't appear to work for milliseconds.
View Replies!
View Related
Automatic Currency Conversion
I'm using excel 2000 Is there anyway to format cells such that I can enter values as Euros and they automatically convert to £ eg If I enter 14.60 (Euros) it automatically displays as £10.00, my conversion rate of 1.46 is constant throughout I know I can do the copy paste special divide thing but the above would be a much nicer solution
View Replies!
View Related
Currency Conversion: Convert In GBP
i have an order book which lists the value of each order against the currency it was ordered in. So in column A i have a list of currencies (THB, GBP, AUD, USD, the user clicks the appropriate one) and in column B i have the figure (23.50). I want a formula that can convert all these in GBP so that i can report the order totals in one currency in column C. =(IF A1="THB", B1/50, "") can i adapt this to include all variables? I will add a table with the currency conversion rates.
View Replies!
View Related
CInt() Conversion & Inputbox
as i understand CInt() converts STRING variables to INTEGER variables. I am trying to create an inputbox, with input validation. So, if the user types in an integer, it will continue to a msgbox; if they type a number in string format, as in 'two', it will convert this into an integer and again display it in a msgbox; if they press nothing a msgbox will appear telling them there is no input; and if they press cancel the programme closes.
View Replies!
View Related
Numerous If Conditions? GPA Conversion
I am trying to create a spreadsheet that will take all my grades from the semester (I'm a college student) and compute my GPA. I managed to get a weighted average for each class, but the grade is on a scale of 1100. I need to convert that to a letter grade, and then a 4.0 scale. How can I set it up so that it will return "A" if 90<Grade<100, "B" if 80<Grade<90, etc.? I'm apologize if this is something simple, but I've been trying to find the right function for a long time.
View Replies!
View Related
Date Time Conversion
Basically my requirement is to find out the time difference in terms of minutes between two days of time. For example. the difference between(in minutes) 27/04/2006 10.01 and 29/04/2006 11.02. The reasults i want is in terms of minutes only.
View Replies!
View Related
Character To Number Conversion
I have a spreadsheet which I automatically generate using VBA. One of the columns lists account numbers. After the macro is complete, the account numbers are nonnumeric and left justified. However if I select one of the account numbers by clicking on its cell, and then edit the account number in the formula window, it changes the cells property to numeric and rightjustifies the cell.
View Replies!
View Related
Numeric To Text Conversion
I often use vlookups that fail because of numeric/text discrepancies. I was always taught that if you're not going to do math on a field, it should be text. Such as zip codes or social security number. Anyway, I know the easy way to convert text to numbers is paste special > multiply by 1. But what's the easiest way to convert numeric to text?
View Replies!
View Related
Currency Conversion By Dropdown
I have a sheet of prices in USD, and want to be able to convert those numbers to Euro. I realize there is no formula to do this... I was going to list USD prices in cell A (which I was going to hide), and then in B have a formula to multiple the contents of cell A with the exchange rate. Is there a way I can create a dropdown cell and based on the contents of the dropdown change the currency items in cell B?
View Replies!
View Related
Date Conversion To Number
I'm trying to convert a number from word to a number in excel. Example: 1012 input in word to a cell in excel. Unfortunately it treats it as a date an gives me 39002. I want to be able to use both numbers and add them together to get 2 not 39002. How to format the excel spreadsheets in order to complete this calculation.
View Replies!
View Related
Min & Max (30+ Cells Selection) + Date Conversion
Excel won't take more than 30 cells when using Min or Max function. I am trying to get min & Max of the following: =min(M3,O3,Q3,S3,U3,W3,Y3,AA3,AC3,AE3,AG3,AI3,AK3,AM3,AO3,AQ3,AS3,AU3,AW3,AY3,BA3,BC3,BE3,BG3,BI3,BK3,BM3,BO3,BQ3,BS3,BU 3,BW3,BY3,CA3,CC3,CE3,CG3,CI3) Is there an alternate to this? The selection is every alternate cell starting M3, O3 & so on. Also, All of the above reference columns have date stored in "2002 Aug 02" format. Is there a quick way of selecting the columns automatically & converting it to DDMMMYY? I tried various methods but nothing works unless I manually enter the date. I am looking for an easy way because I have 100's of sheets to fix with 30+ columns. It will be a big and tedious job if I try to create a separate column beside each and then convert using a formula.
View Replies!
View Related
Data Conversion From Month To Weeks
I am working on this "maintenance schedule" that is currently sorted out into tabs that are organized into the 12 months, jan, feb and so on. I have to write either a command or a program that would be efficient enough to easily convert these monthly data in weekly ones. So the command would have to convert those "jan, feb, mar... ...dec" tabs into "week 1, week 2, week 3... ...week 52". (prefably by linking them up some how)
View Replies!
View Related
