|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I use Excel formulas in the Truss manufacturing industry. (We use a format to
enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )
How can I convert this into a decimal number for calculations? Or how can
this be separated into three cells for manipulation?
Thanks
|
|
Hi
Provided you always use double digits for each measurement (and assuming
lengths don't exceed 99 feet) then
=TEXT(LEFT(A1,2),"#0")&" feet "
&TEXT(MID(A1,3,2),"#0")&" inches "
&TEXT(RIGHT(A1,2),"#0")&" sixteenths"
--
Regards
Roger Govier
"Trussman" <Trussman[ at ]discussions.microsoft.com> wrote in message
news:0E7106B4-CB8C-4422-94AE-BBB593746087[ at ]microsoft.com...
[Quoted Text] >I use Excel formulas in the Truss manufacturing industry. (We use a
>format to
> enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"]
> 10907
> equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )
>
> How can I convert this into a decimal number for calculations? Or how
> can
> this be separated into three cells for manipulation?
>
> Thanks
|
|
Hi,
With your input in cell A1 :
1. Feet =VALUE(LEFT(A1,2))
2. Inches =VALUE(MID(A1,3,2))
3. Sixteenths =VALUE(RIGHT(A1,2))
HTH
Cheers
Carim
|
|
Maybe something like this:
With a value in A1 of the format FFIISS
Where
FF = feet
II = inches
SS = sixteenths of an inch
This formula returns the decimal equivalent of that number in FEET
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12
Alternatively, this formula returns the decimal equivalent of that number in
INCHES
B1: INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16
For 120608
The first formula returns: 12.54166667
The second formula returns: 150.5
Am I on the right track here?
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"Trussman" wrote:
[Quoted Text] > I use Excel formulas in the Truss manufacturing industry. (We use a format to
> enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
> equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )
>
> How can I convert this into a decimal number for calculations? Or how can
> this be separated into three cells for manipulation?
>
> Thanks
|
|
You could use Data | Text to Columns to separate 120608 into three cells
with 12, 06 and 08, respectively
OR
1. Feet =--LEFT(A1,2)
2. Inches =--MID(A1,3,2)
3. Sixteenths =--RIGHT(A1,2)
If you want the values in feet units, then
1. Feet =--LEFT(A1,2)
2. Inches =--MID(A1,3,2)/12
3. Sixteenths =--RIGHT(A1,2)/(12*16)
To get one cell with the value in feet:
=--LEFT(A1,2)+--MID(A1,3,2)/12+--RIGHT(A1,2)/(12*16)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Trussman" <Trussman[ at ]discussions.microsoft.com> wrote in message
news:0E7106B4-CB8C-4422-94AE-BBB593746087[ at ]microsoft.com...
[Quoted Text] >I use Excel formulas in the Truss manufacturing industry. (We use a format
>to
> enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
> equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )
>
> How can I convert this into a decimal number for calculations? Or how can
> this be separated into three cells for manipulation?
>
> Thanks
|
|
Roger, thanks so much for responding. I tried the formula, but 10907 yields
10 feet, 90 inches, 7 sixteenths, instead of 1 ft, 9 in, 7 sxt. The real
trouble, though, remains that the result isn't a decimal format to be used in
another formula. ie. adding two lengths together, or calculating a pitch.
"Roger Govier" wrote:
[Quoted Text] > Provided you always use double digits for each measurement (and assuming
> lengths don't exceed 99 feet) then
>
> =TEXT(LEFT(A1,2),"#0")&" feet "
> &TEXT(MID(A1,3,2),"#0")&" inches "
> &TEXT(RIGHT(A1,2),"#0")&" sixteenths"
> Regards
>
> Roger Govier
|
|
Your example 120608 suggest you used two digits for inches.
What would 2 ft 0 inch and 5 sixteenths be coded as? 205 ?
We need to know as much as possible about the way data is entered if we are
to be of any help
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Trussman" <Trussman[ at ]discussions.microsoft.com> wrote in message
news:457F7A8D-3A95-4361-9992-DADA50416655[ at ]microsoft.com...
[Quoted Text] > Roger, thanks so much for responding. I tried the formula, but 10907
> yields
> 10 feet, 90 inches, 7 sixteenths, instead of 1 ft, 9 in, 7 sxt. The real
> trouble, though, remains that the result isn't a decimal format to be used
> in
> another formula. ie. adding two lengths together, or calculating a pitch.
>
> "Roger Govier" wrote:
>> Provided you always use double digits for each measurement (and assuming
>> lengths don't exceed 99 feet) then
>>
>> =TEXT(LEFT(A1,2),"#0")&" feet "
>> &TEXT(MID(A1,3,2),"#0")&" inches "
>> &TEXT(RIGHT(A1,2),"#0")&" sixteenths"
>> Regards
>>
>> Roger Govier
|
|
Sorry. You guys are SO close. The format is that used in the truss program we
use from MiTek Industries. Their expanation:
"Understanding the MiTek F-I-S Rules
Dimensions are entered in dialog boxes using feet, inches, and sixteenths,
or in decimal feet without any special characters such as dashes or commas.
280308
28 03 08
Feet inches sixteenths
28 feet
3 inches
8/16th's
Examples:
360000 = means 36 ft, 00 inches, 00 sixteenths
250408 is 25-4-8
400 is four inches (0-4-0)
3 is three sixteenths
7267 would be 72 inches and 67 sixteenths
A span of twenty-eight and one-half feet would be entered as: 280600 or 28.5
A three and a half inch bearing is 308
A two-foot overhang is either 20000, 2400 or 2.0
A quarter inch butt cut is 4 (NOT .25 - .25 would be 3 inches)
A six-inch bearing width could be 600"
I hope this helps. You guys are so close. Ron Coderre's suggestion is
dead-on except when the sixteenths exceed 9. (Formula ignores the "1" in 10)
Otherwise it's perfect. Your suggestion is works if there are exactly 6
digits.
Thanks,
Trussman
"Bernard Liengme" wrote:
[Quoted Text] > Your example 120608 suggest you used two digits for inches.
> What would 2 ft 0 inch and 5 sixteenths be coded as? 205 ?
> We need to know as much as possible about the way data is entered if we are
> to be of any help
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Trussman" <Trussman[ at ]discussions.microsoft.com> wrote in message
> news:457F7A8D-3A95-4361-9992-DADA50416655[ at ]microsoft.com...
> > Roger, thanks so much for responding. I tried the formula, but 10907
> > yields
> > 10 feet, 90 inches, 7 sixteenths, instead of 1 ft, 9 in, 7 sxt. The real
> > trouble, though, remains that the result isn't a decimal format to be used
> > in
> > another formula. ie. adding two lengths together, or calculating a pitch.
> >
> > "Roger Govier" wrote:
> >> Provided you always use double digits for each measurement (and assuming
> >> lengths don't exceed 99 feet) then
> >>
> >> =TEXT(LEFT(A1,2),"#0")&" feet "
> >> &TEXT(MID(A1,3,2),"#0")&" inches "
> >> &TEXT(RIGHT(A1,2),"#0")&" sixteenths"
> >> Regards
> >>
> >> Roger Govier
>
>
>
|
|
Thanks, Ron. You are totally on track. Refer to the posted reply to Bernard
above. Your formula does exactly what I need unless the sixteenths exceed
9/16". It ignores the second digit from the right. (ie. 12 equals 1/8"
instead of 12/16 or 3/4".)
"Ron Coderre" wrote:
[Quoted Text] > Maybe something like this:
>
> With a value in A1 of the format FFIISS
> Where
> FF = feet
> II = inches
> SS = sixteenths of an inch
>
> This formula returns the decimal equivalent of that number in FEET
> B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12
>
> Alternatively, this formula returns the decimal equivalent of that number in
> INCHES
> B1: INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16
>
> For 120608
> The first formula returns: 12.54166667
> The second formula returns: 150.5
>
> Am I on the right track here?
>
> Is that something you can work with?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Trussman" wrote:
>
> > I use Excel formulas in the Truss manufacturing industry. (We use a format to
> > enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
> > equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )
> >
> > How can I convert this into a decimal number for calculations? Or how can
> > this be separated into three cells for manipulation?
> >
> > Thanks
|
|
I think Ron simply left out a zero in the last MOD:
=INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12
Should be
=INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,100)/16)/12
--
HTH,
Bernie
MS Excel MVP
"Trussman" <Trussman[ at ]discussions.microsoft.com> wrote in message
news:77E12B4C-5CBC-4370-9BA1-14ED6872A951[ at ]microsoft.com...
[Quoted Text] > Sorry. You guys are SO close. The format is that used in the truss program we
> use from MiTek Industries. Their expanation:
> "Understanding the MiTek F-I-S Rules
> Dimensions are entered in dialog boxes using feet, inches, and sixteenths,
> or in decimal feet without any special characters such as dashes or commas.
> 280308
> 28 03 08
> Feet inches sixteenths
>
> 28 feet
> 3 inches
> 8/16th's
> Examples:
> 360000 = means 36 ft, 00 inches, 00 sixteenths
> 250408 is 25-4-8
> 400 is four inches (0-4-0)
> 3 is three sixteenths
> 7267 would be 72 inches and 67 sixteenths
>
> A span of twenty-eight and one-half feet would be entered as: 280600 or 28.5
> A three and a half inch bearing is 308
> A two-foot overhang is either 20000, 2400 or 2.0
> A quarter inch butt cut is 4 (NOT .25 - .25 would be 3 inches)
> A six-inch bearing width could be 600"
>
> I hope this helps. You guys are so close. Ron Coderre's suggestion is
> dead-on except when the sixteenths exceed 9. (Formula ignores the "1" in 10)
> Otherwise it's perfect. Your suggestion is works if there are exactly 6
> digits.
>
> Thanks,
> Trussman
>
> "Bernard Liengme" wrote:
>
>> Your example 120608 suggest you used two digits for inches.
>> What would 2 ft 0 inch and 5 sixteenths be coded as? 205 ?
>> We need to know as much as possible about the way data is entered if we are
>> to be of any help
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Trussman" <Trussman[ at ]discussions.microsoft.com> wrote in message
>> news:457F7A8D-3A95-4361-9992-DADA50416655[ at ]microsoft.com...
>> > Roger, thanks so much for responding. I tried the formula, but 10907
>> > yields
>> > 10 feet, 90 inches, 7 sixteenths, instead of 1 ft, 9 in, 7 sxt. The real
>> > trouble, though, remains that the result isn't a decimal format to be used
>> > in
>> > another formula. ie. adding two lengths together, or calculating a pitch.
>> >
>> > "Roger Govier" wrote:
>> >> Provided you always use double digits for each measurement (and assuming
>> >> lengths don't exceed 99 feet) then
>> >>
>> >> =TEXT(LEFT(A1,2),"#0")&" feet "
>> >> &TEXT(MID(A1,3,2),"#0")&" inches "
>> >> &TEXT(RIGHT(A1,2),"#0")&" sixteenths"
>> >> Regards
>> >>
>> >> Roger Govier
>>
>>
>>
|
|
Hi
I did say that provided each measurement was double digit.
100907 would have yielded the correct result.
If you want to add the data, then you will first need to split it into
its components.
Again, all measurements must be a 6 character string with double digit
for each of the component measurements.
In B1
=IF(A1="",0,--LEFT(A1,2))
in C1
=IF(A1="",0,--(MID(A1,3,2)))
in D1
=IF(A1="",0,--(RIGHT(A1,2)))
copy down as appropriate
in B6
=SUM(B1:B5)+INT((SUM(C1:C5)+INT(SUM(D1:D5)/16))/12)
in C6
=MOD(SUM(C1:C5)+INT(SUM(D1:D5)/16),12)
in D6
=MOD(SUM(D1:D5),16)
in A6
=TEXT(B6,"00")&text(C6,"00")&TEXT(D6,"00")
--
Regards
Roger Govier
"Trussman" <Trussman[ at ]discussions.microsoft.com> wrote in message
news:457F7A8D-3A95-4361-9992-DADA50416655[ at ]microsoft.com...
[Quoted Text] > Roger, thanks so much for responding. I tried the formula, but 10907
> yields
> 10 feet, 90 inches, 7 sixteenths, instead of 1 ft, 9 in, 7 sxt. The
> real
> trouble, though, remains that the result isn't a decimal format to be
> used in
> another formula. ie. adding two lengths together, or calculating a
> pitch.
>
> "Roger Govier" wrote:
>> Provided you always use double digits for each measurement (and
>> assuming
>> lengths don't exceed 99 feet) then
>>
>> =TEXT(LEFT(A1,2),"#0")&" feet "
>> &TEXT(MID(A1,3,2),"#0")&" inches "
>> &TEXT(RIGHT(A1,2),"#0")&" sixteenths"
>> Regards
>>
>> Roger Govier
|
|
Thanks to you all for solving this problem! It's doing exactly what I want it
to.
"Bernie Deitrick" wrote:
[Quoted Text] > I think Ron simply left out a zero in the last MOD:
>
> =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12
>
> Should be
>
> =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,100)/16)/12
>
> --
> HTH,
> Bernie
> MS Excel MVP
>
>
|
|
Thanks for the feedback.....I'm glad you could work with that.
....and thanks for catching the typo in my formula.
You're right about the sixteenths.
Instead of FEET:
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12
and INCHES:
B1: =INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16
The formulas SHOULD BE:
B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,100)/16)/12
and
B1: =INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,100)/16
for FEET and INCHES, respectively.
***********
Regards,
Ron
XL2002, WinXP
"Trussman" wrote:
[Quoted Text] > Thanks, Ron. You are totally on track. Refer to the posted reply to Bernard
> above. Your formula does exactly what I need unless the sixteenths exceed
> 9/16". It ignores the second digit from the right. (ie. 12 equals 1/8"
> instead of 12/16 or 3/4".)
>
> "Ron Coderre" wrote:
>
> > Maybe something like this:
> >
> > With a value in A1 of the format FFIISS
> > Where
> > FF = feet
> > II = inches
> > SS = sixteenths of an inch
> >
> > This formula returns the decimal equivalent of that number in FEET
> > B1: =INT(A1/10000)+(MOD(INT(A1/100),100)+MOD(A1,10)/16)/12
> >
> > Alternatively, this formula returns the decimal equivalent of that number in
> > INCHES
> > B1: INT(A1/10000)*12+MOD(INT(A1/100),100)+MOD(A1,10)/16
> >
> > For 120608
> > The first formula returns: 12.54166667
> > The second formula returns: 150.5
> >
> > Am I on the right track here?
> >
> > Is that something you can work with?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Trussman" wrote:
> >
> > > I use Excel formulas in the Truss manufacturing industry. (We use a format to
> > > enter Feet, Inches, Sixteenths. 112 equals 1 In., 12 Sxth. [1-3/4"] 10907
> > > equals 1 Ft, 9 In., 7 Sxth. [1' 9-7/16"] )
> > >
> > > How can I convert this into a decimal number for calculations? Or how can
> > > this be separated into three cells for manipulation?
> > >
> > > Thanks
|
|
|