Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
feet and inches
i know there are a lot of posts and things on feet and inches. my question
is a little more specific. i want the format to be (for 11 feet 6 inches) to look like 11-6. so far, my formulas look like this: this formula would convert 11-6 to 11.6 feet =IF(F4=0,0,LEFT(F4,FIND("-",F4)-1)+((MID(F4,FIND("-",F4)+1,LEN(F4)))/12)) then this formula converts it back to 11-6 =CONCATENATE(LEFT(AC4,FIND(".",AC4)-1),"-",ROUND(((MID(AC4,FIND(".",AC4),LEN(AC4))))*12 ,1)) the problem i am having is that if it is 11-0, the second formula returns an error. i think it is because it can't FIND the "." i would appreciate any help. Steve |
#2
|
|||
|
|||
i understand why it doesn't work. if you type 6.00 into a cell, the formula
bar still only reads 6 In other words, it rounds itself in the formula bar. how can i correct this? thanks steve "steve" wrote: i know there are a lot of posts and things on feet and inches. my question is a little more specific. i want the format to be (for 11 feet 6 inches) to look like 11-6. so far, my formulas look like this: this formula would convert 11-6 to 11.6 feet =IF(F4=0,0,LEFT(F4,FIND("-",F4)-1)+((MID(F4,FIND("-",F4)+1,LEN(F4)))/12)) then this formula converts it back to 11-6 =CONCATENATE(LEFT(AC4,FIND(".",AC4)-1),"-",ROUND(((MID(AC4,FIND(".",AC4),LEN(AC4))))*12 ,1)) the problem i am having is that if it is 11-0, the second formula returns an error. i think it is because it can't FIND the "." i would appreciate any help. Steve |
#3
|
|||
|
|||
Say you have 4.5 in A1 then =INT(A1) returns 4 while =MOD(A1,1) returns 0.5
If A1 has 5, then you get 5 and 0, respectively So =INT(A1)&"-"&MOD(A1,1) would seem to do what you want but it give 4-0.5 This works better =TEXT(INT(A1),"###")&"-"&TEXT(MOD(A1,1)*10,"0") best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "steve" wrote in message ... i understand why it doesn't work. if you type 6.00 into a cell, the formula bar still only reads 6 In other words, it rounds itself in the formula bar. how can i correct this? thanks steve "steve" wrote: i know there are a lot of posts and things on feet and inches. my question is a little more specific. i want the format to be (for 11 feet 6 inches) to look like 11-6. so far, my formulas look like this: this formula would convert 11-6 to 11.6 feet =IF(F4=0,0,LEFT(F4,FIND("-",F4)-1)+((MID(F4,FIND("-",F4)+1,LEN(F4)))/12)) then this formula converts it back to 11-6 =CONCATENATE(LEFT(AC4,FIND(".",AC4)-1),"-",ROUND(((MID(AC4,FIND(".",AC4),LEN(AC4))))*12 ,1)) the problem i am having is that if it is 11-0, the second formula returns an error. i think it is because it can't FIND the "." i would appreciate any help. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Special format for feet - inches in a cell | Excel Discussion (Misc queries) | |||
is there a macro that will convert from inches to feet and inches | Excel Discussion (Misc queries) | |||
Calculating the Area of objects in Feet and Inches | Excel Discussion (Misc queries) | |||
How can I show Feet and inches in a Excel cell? | Excel Discussion (Misc queries) | |||
Converting inches to feet & inches. | Excel Worksheet Functions |