Concatenating Fractions
Apr 6, 2008
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 9 Replies
ADVERTISEMENT
Aug 7, 2009
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 10 Replies
View Related
Feb 11, 2014
How do you limit a cell to only display fractions in sixteenths, eighths, quarters, and halves?
I'll give an example.....we use excel to cut list doors in our cabinet shop. On a three panel door it will spit out a fraction of 1/12" (or 5/12, or 7/12 etc), but this looks a lot like 1/2" and the guys frequently cut them the wrong size.
Is there any way to limit a cell so it will only display in these fractions 1/16, 1/8, 1/4, 1/2 etc? and I don't mean by just saying to format in sixteenths, because if I do that then it displays 12/16" when I want it to say 3/4".
I basically want it to round to the nearest sixteenth, but I want it to display to the closest sixteenth, eighth, quarter, half etc.
View 3 Replies
View Related
Nov 8, 2008
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 6 Replies
View Related
Jun 24, 2009
I need to convert the fraction at the beginning of a cells text into the decimal equivelent. for example 1/4-20 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 2 Replies
View Related
Jun 28, 2013
I have this in a cell Plam Base 36"w x 28.5"h x 22.5"d I want this Plam Base 36"w x 28 1/2"h x 22 1/2"d
How do I do it.
View 8 Replies
View Related
Dec 14, 2009
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/2-20 3A (becomes .5000)or
3/4-13 2A (becomes .7500)or
1-14 3A this one will not work with my current formula (should be 1.0000);
View 12 Replies
View Related
Feb 12, 2014
I have fractions and whole numbers in a column as per the following.
E13 = 3/4
E15 = 5
E17 = 1-1/2
I can use the formula =SUBSTITUTE(E17,"-"," ")+0 which gives the correct result of 2.5 and also correct
for E15 with the answer 5.
But I'm stumped how to get it correct for E13 where it is less than whole number.
I can use the formula =IF(E13=0,0,("0 "&E11)+0) but that won't work for the remainder of the cells.
View 1 Replies
View Related
Nov 19, 2008
1.25 is in cell A1. This number will often be buried in a longer part number that may look like ELL-1.25-S40.
I would like to use a formula in cell A2 to display the fractional equivalent (1-1/4).
View 9 Replies
View Related
Aug 11, 2009
=("0 "&C3)+0
have this formula for converting fractions to decimals which works great,
the problem i have is to get it to register 0 if there is no fraction in c3
will try and word my next question better
View 9 Replies
View Related
Sep 23, 2003
Need to convert decimal to inch fractions IE: 4.5 = 4 1/2 inches
View 9 Replies
View Related
Nov 11, 2006
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 3 Replies
View Related
Feb 14, 2014
I created a table for a check register. Column D is the transaction amount and column I is the updated balance. Column D has no formulas. It is where I enter the transaction amount. I checked all entries and even re-typed them. I extended the decimals out to the maximum and they are all. $$.CC000000000etc - no fractions of pennies. However the Subtotal 109 formula at the bottom of the table computes the answer with fractions of pennies even though there are no entries with fractions of pennies. And at row 118 in column I it also starts computing it as fractions of a penny.
Column I has a formula. Row 1 of course is titles. The second row is my begining balance with this formula: +"Table Name"[[#This Row],[Amount]] In other words what is in Column D row 2 as my first transaction, the beginning balance. The rest of the column has this formula +I(row above)+$D(that row) so in column 3 it is +I2+$D3 and so forth down the rest of the table. It had read +I2+"Table Name"[[#This Row],[Amount]] but I changed it to $D because I thought that might fix the error. It didn't
Columns D and I are the only ones with dollar amounts and really any numbers, except for the dates in column B. E is the envelope, F is the category, and G is the transaction status (cleared, pending, etc.). They have drop down boxes. Column H is notes. It is formatted as text. In the transaction amount column (D) I enter debits as negative numbers and credits as positive. e.g., a check written for $20 is -20.00 and a deposit of $20 is 20.00. Column I, my balance does not have any negative numbers. -fortunately-
The problem is minor and I could work around it. However it impacts my formula which tells me that I "balance." It will not return the value "Balanced" as it is computing a variance between my bank balance+uncleared items being equal to my CR balance by a fraction of a penny.
View 3 Replies
View Related
Jun 1, 2009
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/4-20 3A. I need to convert the fraction and save/copy paste the decimal answer to another cell.
View 3 Replies
View Related
Apr 26, 2006
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 12 Replies
View Related
Mar 12, 2008
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 9 Replies
View Related
Mar 30, 2007
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 3 Replies
View Related
Jun 19, 2007
I am currently trying to organize numerous numbers by fractions, and it just will not set up right.
1 1/2
1/2
3/4
2
1
2 X 3/4
1 X 1/2
1 1/2 X 3/4
What would be the best code used, or method, to ensure that these numbers organize correctly?
View 2 Replies
View Related
Feb 6, 2009
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 4 Replies
View Related
Jan 1, 2012
If I do =sin(radians(30)) I get .5 and =sin(radians(45)) I get .707106
These are of course correct, but I would like to get 1/2 and sq root 2/2 (with the actual radical sign).
View 9 Replies
View Related
Nov 25, 2009
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 10 Replies
View Related
Feb 1, 2013
E.g. bond price is quoted in 32 so 101-16 will be 101.50
I only know how to add them to my addin but how to write the code.
Function BONDPX()
End of Function
View 4 Replies
View Related
May 11, 2009
I have more than 30 Columns. I want to concatenate all these columns and put the value in one cell. When I do it, Exccel gives me an error, "You have entered too many arguments for this function"
View 4 Replies
View Related
Sep 21, 2009
I have a sheet that is going to list usernames in a range of cells (e13:e19). Who gets listed there depends on another condition. So, all the cells could have a name, or only a couple of them could have a name. In another cell I have a formula that is to concatenate the results of who is in e13:e19 range, each name to have a comma between. The problem results when not all of the cells have a name .. the commas still appear ... so results could be ted, mary, bill,,,,bob, jeff ..etc.
I am using the formula below but it still allows the non-needed commas to appear:
=MID(IF(ISTEXT(E13),","&E13,"") & IF(ISTEXT(E14),","&E14,"") & IF(ISTEXT(E15),","&E15,"") & IF(ISTEXT(E16),","&E16,"") & IF(ISTEXT(E17),","&E17,"") & IF(ISTEXT(E18),","&E18,"") & IF(ISTEXT(E19),","&E19,""),2,2000)
View 3 Replies
View Related
Nov 6, 2008
This is the concatenation macro I have for 2 columns. I want to make this more general in that I would like the program to insert a new column to the right and concatenate the 2 columns based on the current cell the cursor is on and the one adjacent to the left (-1). The loop works fine, the modification I need is for the following three (3) lines.
View 2 Replies
View Related
Jul 24, 2009
For a visual aid I am using a msgbox to view my results. I have "Scenerio 1" and 2.
Snippet of code (CommandButton1):
View 3 Replies
View Related
Feb 12, 2006
I have a list of about 1,500 email addresses. To send an email I need to have a comma between each entry. I know I can use the formula =(A1&,",",&A2) to concatenate cells A1 and 2, with a comma between the 2 strings, but is there a formula to automatication do it for A1 - A1500???
Or is there an entirely different approach I should consider?
View 9 Replies
View Related
Apr 9, 2014
I have a table where a field is called "Expected conversions at [X%]".
X% is a monthly calculation.
So to populate the table row name I tried ="Expected Conversions At"&" "&C28 where C28 refers to a percentage calculated elsewhere in the sheet.
Cell C28 is formated to 1 decimal place. But when I refer to it in this manner it displays to many decimal places.
The result:
Expected Conversions At X.XXXXXXXXXXX
It should say:
Expected Conversions At X.X%
How do I do this so as the % is dynamic and auto updates each month?
View 2 Replies
View Related
Oct 5, 2007
I've got a spreadsheet that is being somewhat auto-generated from another program that extracts four different lines of text and puts them into separate cells (Starting at W30, X30, Y30 AND Z30). I believe there are 15 possible combinations. What I would like to end up with is each cell concatenated with " - " separating each one, but ONLY if there is text there to separate.
The function code I have so far gets me very close to what I want, but there are a couple of small issues remaining. See Red Bolded Text.
Here's what I've got so far.
Code: ....
View 9 Replies
View Related
Jan 17, 2008
I need to combine data from two adjacent columns into one in a condensed version of a spreadsheet. As the spreadsheet itself is quite big (over 20,000 rows) I was wondering if there was a quick and efficient way to doing this.
Here is a sample of what I'm working on (I know this would be better off as an image but unfortunately Photobucket is blocked at work):
Contract: Apr-2007 4000 Calls
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000
20-Apr-07Apr-074000
Contract: Apr-2007 4000 Puts
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000..............
View 9 Replies
View Related
