I often use feet and inch inputs for calulations. I prefer to input a typical feet & inch input into one cell using this format: ft-n.
example: 12ft 9in would input as 12-9
This would need to be converted into a decimal for calcualtions. Also I would like to convert from Decimal back to ft-in.
I have to make a excel document in which I have length and width in feet and inch format.
E.g. 10.01 in which 10 is feet and .01 is inch
I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .
Just like the calculator here does.. [URL] ........
Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...
Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????
Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000
I need a formula to convert:
3.759 to 3' 9"
3.974 to 4'
4.005 to 4'
I have this currently:
=INT(D9)&"' "&ROUND(TEXT((D9-INT(D9))*12,"#"),0)&CHAR(34)
However, 3.974 returns 3' 12" and 4.005 returns a #VALUE error. I'm picky about the spaces. I'd like it formatted as I have it now.
I am currently working on cut sheets for my current project. I need to take my decimal feet (ie 1.55) and display them as feet and inches (1' 6 5/8") not (1' 6 3/5")
I have a formula which is this
=INT(F17)&"' "&TEXT(12*(F17-INT(F17)),"# #/#")&CHAR(34),
It works but the problem is the fractional inches 6 3/5" i need them to display in 1/8 1/4 1/2 when applicable. Is there something i could add to this formula to make my inches read like a measuring tape (1' 6 5/8") 1/8 1/4 1/2??? I attached the spreedsheet for review.
Question.xlsx‎
I am working in excel 2003 and I need to convert feet and inches into decimal feet. Example: 15'-10 5/8" to 15.88542
15'-10 5/8" will be in say, column B, and the conversion will be in say, column Q.
This formula will be copied all the way down the page.
how to do this or can it even be done in this version?
Need to convert decimal to inch fractions IE: 4.5 = 4 1/2 inches
View 9 Replies View RelatedIs there a way to convert a value in a cell that is 2'-3 1/2" to adecimal such as 27.5"?
excel 2003 SP2
Is there a way to store a text style that will allow me to convert decimal numbers into feet and inches in the same cell?
I want to be able to type in a number like 3.5 and have it read 3'-6".
I am new to Excel but not programming and I am looking for a recommendation for the following. I have a spreadsheet that simply takes the length and width of an area and computes the square feet and yardage and other sundry items. I am entering the feet/inches as follows:
Example: 11.3 (equals 11/ft 3/inches)
The correct decimal conversion should be 11.25 but, obviously, it does not know that the number to the right of the decimal point is an indicator of inches. (ex.: .5=.42, .7=.58, .9=.75, .11=.92)
I have approached this from the stand point of an IF condition, finding the position of the "." and grabbing everything to the right (+1) but I understand that the limitation is 7 nested IFs.
Can someone get me kick-started on what the best approach would be to get my entry to convert to the true decimal equivalent? Currently, I am simply doing the conversion from memory but I would rather automate this sometimes errant approach.
This is a continuance of the Q and A below. Except the answer below converted a decimal like 1.44 to and answer like 1' 5.28".
except the final format I am after requires the 5.28" to be fractional such as:
1' - 5 1/4"
Is there a formula for this format?
Copying data from one spreadsheet to another using +, then sorting that data in multiple levels...problem...need to make each cell in the copied table FIXED...ie, +a3 to +$A$3...is there a shortcut to entering each cell by hand?
View 2 Replies View RelatedI design all my spreadsheets on a 19inch LCD however when I push them out to the network drive and our assoiciates open them on there 17 inch CRT'S the sheets dont fit correctly. What can I do to correct this on my end so when they open them they look and fit correctly on there screens?
View 2 Replies View RelatedI have a worksheet compiled with text and decimal numbers all the numbers are filled in via. a data validation drop down list. This fills in many cells in certain columns and rows with data from others sheets depending on what item is choosen from the list.
Is it possible to have a macro or two macro buttons that will convert the decimal numbers from inch to metric or vice versa. When clicked. Note that there are decimal numbers in 3 decimal places up to five decimal places.
I know to convert Decimal to Metric is .0000/.03937
Metric to Decimal is .0000*.03937
I am a woodworker. I have a CAD program that gives me a cut list of all the parts I need. I then take this list into Excel and do some stuff with the data.
all the dimensions are in fractional inches where ' is used to designate feet and " designates inches. I use the find and replace command to get rid of the ' and ". if the dimension is just a fraction like 3/4 Excel thinks this is a date and displays march 4th. I have tried formatting the cells to numbers before i do the find replace with no luck.
I have the following data (measured in 1/1000th of an inch)(I have rounded down in this example though!!!)
NameABCDEAGGReal RANK
AAAAA0.110.120.090.120.270.1422
BBBBB0.120.1140.080.1350.2610.1421
CCCCC0.350.170.170.160.110.1923
copied down to a minimum of 100 rows (names) The AGG is the sum(a:e/5)
When u use RANK(agg,agg1:agg100,1) It ties Names AAAA and BBBB as they both have AGG of 0.142. Problem is the rule book states ties are split by the smallest of the results for each competitor in columns A to E. In this example BBBB is 2nd as he has smallest result in Column C being 0.08.
I have tried the AGG+1/min(a:e) and still end up with incorrect results. The problem is the numbers are so small and you have to split ties all the way to the 100th person involved. I cant use sort either as the data has to be printed in alphabeticall name order all the way to 100 names with their ranks recorded against the names.
How can we determine Number of Logical Pixels per Inch based on the system configuration using API and return the value to Cell A1 of Sheet1.
I need to convert inches to feet and inches in this format:
88 1/2 = 7' 4-1/2"
...so that if 88 1/2 is in cell A1, cell B1 will show 7' 4-1/2".
The exact syntax of B1 must be as shown.
I have 6 rows A1-A6(sheetsize), they can be 1 of 8 numbers. (16,14,12,10, 5/8, 1/2, 3/8, 3/16) these are all metal gauges. The cell next to them B1-B6(sheetgauge) has a number that refers to how much sq ft each of these sheet metal sizes has. I would like a cell below A30 to add up all the sq ft in B1-B6 if it is 16 gauge. Then A31 will add up all the 14 gauge, etc.
How is this possible without having a huge formula.
Here is my standard formula, but it wont add the B cells together if more than one of the same sheet sizes.
=index(sheetsize,match(A31,sheetgauge,0))
I am making an excel spreadsheet that auto fills in a lot of items for a construction company. One of the most important ones is the Roof Pitch.
1/12 pitch is 1 inch rise every 12 inches.
2/12 pitch is 2 inch rise every 12 inches.
I would like it to auto calculate this and add it to the over all height of the building.
Example:
A house is 20 feet wide with a 2/12 pitch.
Since the Ridge is in the center we divide the Width in half.
So a 2/12 pitch over a span of 10 feet is 20 inches
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 2 Replies View RelatedBasically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.
View 9 Replies View RelatedI am creating a spreadsheet for some deliveries. The pieces I have are all in feet and inches and multiplied by a certain quantity. I need a formula to run this and give me feet as an answer.
I have 4 cells, in A1 I have feet without the foot mark (20) and in cell B1 I have inches without the " (5). In cell C1 I have 20. How do I write a formula to do the following:
20'-5" multiplied by 20 = Answer in D1 408'
I am trying to calculate volume in inches then in feet using 2 methods to check my work.
Method ....
I need help shrinking down my formula to make it fit in one cell.
Right now, the way i have it, it spans across (7) different cells to get the results i desire. Is there a way i can make this shorter?
A11 – This is where the decimal value of a number is inputted.
B11 – This is the final display after running A11 through the formulas below
Here are my formulas: ...
I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.
View 4 Replies View RelatedI 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.
I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.
View 2 Replies View RelatedI've got a column of numbers that represent the number of overs bowled in games of cricket. Whilst these are whole numbers (eg. 34 overs + 34 overs) the addition isn't a problem, but when they are incomplete overs (eg. 34.4 overs + 34.5 overs) then the addition if out of kilter as it sums them in base 10, and not in base 6. (As there are six balls in an over, not ten for anyone who doesn't know!)
View 2 Replies View RelatedI would like to sum the amount column of unique date that is in active status of each account.
Account Date Status Amount
11 jan 1 A 200
12 jan 1 A 5
13 jan 1 A 1000
11 jan 1 A 200
11 jan 3 A 100
12 jan 3 A 8
13 jan 4 A 1
13 jan 4 A 1
11 jan 6 I 50
12 jan 6 A 10
Below is the expected result
Account Amount
11 300 (200+100) [200 is only add once since jan 1 of 11 has appeared more than one time]
12 23 (5+8+10)
13 1001(1000+1)
I have entered this value into a cell on my worksheet. It is referencing a cell in another worksheet on a shared drive on my network.
='\servershare$[doc1.xls]Sheet1'!$A$1
All is fine until I re-open the document, whereby it has changed to:
='\serverusers$share$[doc1.xls]Sheet1'!$A$1
I have been advised to set the hyperlink base as \servershare$
...but this clearly does not work.