Tracker
HOME    TRACKER    Excel
 Advertisements:

# Converting Feet/inches To Decimal Equivalent

## 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.

Sponsored Links:

Related Forum Messages:
Converting Decimal To Feet - Inches (fractional)?
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?

Decimal Numbers To Feet And Inches
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".

Convert Feet/inches To Decimal
Is 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

Calculating Inches Then Converting To Feet
I am trying to calculate volume in inches then in feet using 2 methods to check my work.

Method ....

Feet And Inches
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.

Feet & Inches Formula
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

Convert Decimals To Feet & Inches
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: ...

Decimal To Fraction (Inches)
Could someone please tell me what is wrong with this formula. I have been looking at it for so long, I can't even concentrate .....

Inches And Decimal Fraction
I am working on a simple calculater, what I want to do is figure sq. inches my problem is every thing I try the decimals won't calculate right,, this is a example of what I want to do

8.25x10.75= 81 sq"

Conversions To & From Feet-Inch To Decimal/base 10
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.

Converting Comma To Decimal Point
I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.

Converting Units And Decimal Places.
I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered.

In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4.
Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?

Converting Decimal Numbers To Minutes And Seconds
I have an excel sheet with a row of numbers example below. These numbers represent the length of time that a telephone call was. The problem is they are decimal numbers (units of 100) but they represent seconds and minutes of a phone call which change up one number when the seconds hit 60 ...

Time Sheets - Converting Decimal Point
I have just started a job and given a task to input some timesheets that were done manually. The problem is that I used decimal point instead of the colon. When I try and format the cells I do not get the real time.

Here is an example: ....

Converting Decimal Numbers To Text With Dot Numbers
we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:

Sub ForceToNumber()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
With wSheet
. Range("IV65536") = vbNullString
.Range("IV65536").Copy
.UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End With
Next wSheet
End Sub

Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

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:...

Formatting Group Of Cells To Have Different Decimal Rules Based On Decimal Value
Basically, 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.

Removing Decimal Point While Maintaining # Of Decimal Places
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.

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.

Calculate Decimal Portion Of Decimal Number`
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.

Check If Cell Represented In Inches
well in one of my excel reports, i am using a dsum formula and plotting a chart against it. i do not want to show the zeros on the graph, is there anyway i can do that, i could not find anything in the tools -> options.

Formula To Convert Inches To Square Foot
I need a formula to automatically convert inches to square feet. I have =IF(G5>12,G5/144). and G5 is the cell used to enter your inch value. The formula wrks great, but only if you enter over 12 inches. I'm pretty sure Im on the right track, just need to know how to add in the part about if its less than 12 inches it should be multiplied by 12.

IFERROR Equivalent
I've got a long formula here. If the resulting expression is equal to "0-0" I want it to go blank as if it was an error, and if it isn't, I want it to show the resulting expression as normal.

Concatenate: Result With The Inches " Symbol On The End
I'm trying to get the following formula to give me a result with the inches " symbol on the end.

=IF(SPECS!B2="K1318","1.388",IF(SPECS!B2="K13518","1.985",".950"&""""))

So I want either 1.388" or 1.985" or .950". The current formula will only give me .950".

VBA Equivalent Of Weekday()
I'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.

Length Equivalent For Numbers
When I tried len(nYear) where nYear is a number like 2006, I don't get the number of characters in the number. I know that's confusing strings with numbers, but...

Is there a VBA function that returns the number of numbers deep in an integer; ex. if nYear = 2006, the function would return 4?

I could easily do a < or > line of code, but I'm just curious if there's another way to do it.

Equivalent Code For A Formula
I have two worksheets: "Report" & "Hou Jobs". In Report worksheet, I need a code equivalent to following formula so that it returns value in cell D5.

If the values in cells B5 & B8 change, the value should update itself.

=VLOOKUP(B5,'HOU Jobs'!A4:B667,2,FALSE)

Do Until Loop Or Equivalent Required
I need a loop function (i guess a do while or do until code) so whenever the word 'Non-Current' appears in colum A enter a 1 in colum E until the word 'Total Non-C' is reached at which point the loop must end.
Such as:
A B C D E
NON-CURRENT 6 4 5 1*
ABSA 4 5 2 1*
BARCLAYS 3 2 8 1*
NED 0 8 6 1*
TOTAL NON-C 4 6 7 0

Finding Out Whether 4 Lengths Are Equivalent
I have 4 lengths in four columns in a random order, and need to compare them to see whether they are equal lengths.

I Have figured out how to order them so I can compare them, but can't think of a formula to show whether they are equivilent (eg 1000m = 1km) True or False outcome is fine.

Eomonth() Equivalent In VBA?
if there is an eomonth() equivalent in VBA?

Finding Equivalent Of Maximum Value In Another Sheet
I have to sets of data, each in a sheet with the first column as identical for both sheets. Sheet2 contains two series of 6 rows, each for a specific first column (""B Code").

Now I want to find the values of "TCT-C" column in sheet1 (for each range of 6 rows that the "B Code" is match) which corresponds to the row number that "TCT" in sheet2 is maximum (again for 6 rows). The point is, I need to shift to another series of 6 rows in sheet2 once the "B Code" does not match.

SumIf: Change The Criteria To Be The Equivalent Of A1 OR B2
I've searched the boards & haven't found anything as uncomplicated as what I'm looking for. I know I could use Sumproduct, but for something this simple is there anything I can do with Sumif? This in effect is my worksheet.
AB
1AD200=SUMIF('Sheet2’!B:B, A1, 'Sheet2’!C:C)

All I want to do is change the criteria to be the equivalent of A1 OR B2 (ie checking the range for either match. No way to do that in a sumif? I'm looking for the least complicated, easiest to adjust/edit formula possible.

Function That Is The Equivalent Of WORKDAY But For Hours Instead
I am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below:

In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for

* Weekends
* Fixed Holidays
* Operations running as seamlessly as possible

Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.

VBA Equivalent For Shift-Arrow Keys
Does anyone know the VBA equivalent to using the Shift-Arrow keys on the keyboard? I am wanting a macro to do this for me. Specifically I am wanting to use Shift-Arrow Right.

Change A Date To Its Equivalent In Terms Of Quarters System
I have the formula where i can change a date to its equivalent in terms of quarters system with a date:

=CHOOSE(MATCH(MONTH(A2),{1,4,7,10}),"Winter","Spring","Summer","Fall")&YEAR(A2)

This is a school year configuration.

ex.
A2 = 10/1/2005: with the formula up there it turns into Fall 2005
i want to be able to add any number of years and the formula will still come up with the quarters system

also i would like A2 to be stationary and create a list of quarters for each year i add on

ex.
A2= 10/1/2005 B2=Fall 2005
B3=Winter 2006
B3=Spring 2006
B5=Summer 2006
B6=Fall 2006
etc.

If this is all possible lastly I would like to negate summer quarters

USD Equivalent In Column Based On The Exchange Rate At The Time
Dates in Column A
Currency in Column B (expressed as EUR, CHF or GBP)
Amount in Column C

And I'm trying to get the USD equivalent in column D based on the exchange rate at the time.

In Sheet 2 I have a table of the historical exchange rates like this:

DateEURGBPCHF
08/05/20060.786270.538151.2277
09/05/20060.784470.536751.2224
10/05/20060.781360.536271.2185
11/05/20060.778050.530761.2108
12/05/20060.775880.528811.2019
15/05/20060.779780.530911.2089

Worksheet/formula That Automatically Look For The Equivalent Score In A Given Transmutation Table
how can i make a worksheet/formula that automatically look for the equivalent score in a given transmutation table.

example: i have an exam of 10 items, what will be my formula to so that it will automatically look for the equivalent score under the transmutation table of 10 items

Using VBA To Get System Time In GMT Equivalent Instead Of Local Time
if there is a simple way to get the GMT equivalent in vba code. I am being a date/timestamp but realized that timezone differences may complicate matters, therefore I would like to "standardize" the timestamps into their GMT equivalent rather then using the computer's local time. I had been using

Cursor Home (equivalent Of CTRL (Home) To Move)
Does anyone know the equivalent of CTRL (Home) to move the cursor to the a1 position in VBA? I have tried Range(cells(1,1),cells(1,1)).select but with no success

"MAXIF" Function, Or Equivalent?
I have a spreadsheet that I'm working on that compiles survey data from an online survey. I have averages, high scores, low scores, etc. figuring off of my data to product charts and graphs for a client. I am attempting to find the high and low scores for individual surveys (there are 1054 surveys total right now). I know of max and min, but I really need something that would function like a "maxif" (which I realize does not exist).

Here's the problem:

In column A, I have a list of insurance provider names per survey (so, 1054 entries total). I then have some columns in between that show scores on questions from the survey, and then column AB contains my averages for each individual survey. Column AD has a list of the insurance provider names in alphabetical order (there are 167 unique provider names used in the 1054 surveys). Column AF is my high score column and lines up with column AD (so, I am looking to have 167 high scores in total as I want the high score per each provider, not survey). Let's pretend A2:A10 say "Anthem Insurance." I want to create a formula that would basically say something like:

Count what rows in column A = "Anthem Insurance," then match those cells to AB (so, it would figure since A2:A10 are the targeted cells, then I also want to correspond to AB2:AB10) and give me the high score (MAX) for that range of cells. Is there a way to do this? Otherwise, I have to manually go through all 1054 rows and see what range of cells equal a certain insurance provider name to get those 167 high scores. I can keep doing this if I have to, but this data changes every single week and it eats up a lot of time.

Round Off Decimal
I have excelsheet with the following data

A - 1.3
B - 1.3

SUM: 2.6

Now when I want get sum of A and B it shoud be 2

i.e. Round figure of

A - 1
B - 1

SUM : 2

Whereas I'm getting sum as 3 instead of 2 as round figure of 2.6 is 3

If Function Decimal

I need to create a formula in a spreadsheet so that when KWD or BHD is entered into a cell then another cell changes to 8 decimal places if neither of these are in the box then it needs to be 7 decimals! But the box that is changing needs to be alterable.

I have posted this on a non excel specialised forums and i got this answer:

in cell x you have "kwd"
in cell y you put: =IF(cell X="kwd",TEXT(cell Z,"#,##0.00000000"),TEXT(CELL z,"#,##0.0000000"))
in cell Z you put the value that you want to be alterable

Whole Number As Decimal
I would like to enter whole numbers but have them convert to decimal. I have searched and found a solution, but it only references to one column and I need to reference other columns as well. I tried to edit but I’m not very knowledgeable with code. Here is an example of what I am looking for, columns E31:E52, F31:F52, L31:L52, M31:M52, N31:N52. Could someone provide a code to acquire these results?

Decimal To Binary?
Does anyone know how to convert a 4 digit number to binary in excel? I used the =DEC2BIN formula, but it only works on a 3 digit number that is smaller than 512.

Extract Value With Decimal
Extract 2 digits to the right of a decimal when it ends in 0, AND keep it a value.

Ex: .69 .75 .50 .70 = 69 75 50 70.

Text to columns won't work because it has to calculate from other cells' data. This value is then used in an IF function.

Convert [s] To Decimal
I'm trying to find out how many 40 hour shifts we had in a week by dividing the total seconds staffed (2989957) by total seconds in a week (144000) to get a 2 digit decimal result. I have a field formatted as [s] that I need to convert to decimal but when I do a calculation using that field it comes out as [s}.

Cell A1: 2989957 formatted as [s]
Cell B2: =A1/144000 formatted as number with 2 decimals
Result shows 0.00

If I use that calculation in a cell with a general format, I get 21 and it reverts to [s] format.

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/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);

Variable Decimal Format
Cell C9: 1.25773
Cell C10: 20.0
Cell C11: 2.25%
Cell C15: =C9+((C10*C11)/10000)

C9 is a user entered value, currently formated general
C10 is a user entered value, currently formated decimal with one decimal
C11 is a user entered percentage, currently formated percent with two decimals
C15 is a formula calculation, currently formated general

I've tried formating cells to general (and/or) text and the values appear to be correct but still don't show what I want.

The most popular entries for C9 will be:
123.12
123.123
1.1234
1.12345
Any of the above could have one or more trailing zeros.

I would like C15 to show the same amount of decimal places that the user enters in C9. If user enters one, two, three, four, five, etc...decimal places in C9 then show the same amount of decimal places in C15 after the calculation is done and include any trailing 0's that are needed to match the number of decimals in C9.

I've tried different If statements with custom format to try to get the format of C9 transfered to C15 but haven't come across the right way to do this.

Phantom Decimal Points
I've tried to look for a solution on the forum, but nothing seems to come up. I've attached a file to help show what I'm trying to resolve.

Column A of the file shows an amount, when summed, give a total of -3.5725E-09.

Each of the figures in column A only has 2 decimal points and if I manually total up the numbers on a calculator it give me zero.

Does anyone know how I can get rid of the -3.5725E-09 without another formula? I need the balance to be zero.

Increment Second Decimal Point
During finalization I have to convert the numbers into millions. But due to rounding factor, the total doesnot match with the displayed fig. how can I sum only upto two digits of the fig? A sample file is attached.

Copyright © 2005-08 www.BigResource.com, All rights reserved