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 |
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 |
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 |
All times are GMT +1. The time now is 05:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com