Feet Inches Sixteenths Number Format

garsard · Jan 19, 2024

I have started a new job in the construction industry and want to continue to be able to use excel in my day to day work as its a very powerful tool that I'm already familiar with. However I've run into a problem in that my firm use the FIS dimension formatting for everything. For those not familiar with this it is used to write imperial dimensions in the FFIISS format where F is number of feet, I is number if inches, and S is sixteenths of an inch.
For example you would write 1' 6 1/2" (one foot, six and a half inches) as 10608
Another example 23' 7 3/4" would be 230712
If the measurement is in feet alone it is often written as the number of feet with a "." afterwards, for example 23' would be written as 23. or 230000

I have found a few different formulas that will work to convert these into decimal format. Currently I am using this one

=IF(IFERROR(FIND(".",C1),0),C1*1,LEFT(REPT("0",6-LEN(C1))&C1,2)+(LEFT(RIGHT(C1,4),2)+RIGHT(C1,2)/16)/12)

It seems to work well to convert into decimals but I actually want to keep working in the FIS format. My goal is to be able to add, subtract, divide and multiply inside the spreadsheet with this number format. However I am not familiar enough with custom number format to be able to make this work. Is it even possible to make a custom number format like this? am I better off using rows or columns of hidden cells with this formula and then another one to convert the numbers to decimals and then back again? Any help would be very much appreciated.

felixstraube · Jan 19, 2024

As far as I know there is not the possibility to format a number as you need. You will have to convert to feet (or any unit of measurement) calculate and then convert back to FFIISS.

Phuynh · Jan 28, 2024

Very interesting number format, FIS, the way I see it could be done in 3 or 2 steps:
First, convert to one type of unit for calculations
Second, perform calulations
Then, convert result of calcuations back to whatever format you want to be

=ConvertBackToFormat(Sum(Lambda(ConvertFIS_to_II))

It may work, I guess!

Phh

Phuynh · Jan 30, 2024

Convert from FIS to decimal inches
FIS format in form of: FFIISS, IISS, SS, FF., FF.## (in a cell A1)

Excel Formula:

=IF(ISBLANK(A1),VALUE(0),
 IF(ISERR(FIND(".",A1)),
 IF(LEN(ABS(A1))<3,
   ABS(A1)/16*SIGN(A1),
   IF(LEN(ABS(A1))>4,
     (SUM(ABS(LEFT(A1,LEN(A1)-4)*12),VALUE(LEFT(RIGHT(A1,4),2)),RIGHT(A1,2)/16)*SIGN(A1)),
     (LEFT(ABS(A1),LEN(ABS(A1))-2)+RIGHT(A1,2)/16)*SIGN(A1))),
 VALUE(A1)*12)
 )

or with lambda function

Excel Formula:

=LAMBDA(ffiiss,
 IF(ISBLANK(ffiiss),VALUE(0),
 IF(ISERR(FIND(".",ffiiss)),
 IF(LEN(ABS(ffiiss))<3,
   ABS(ffiiss)/16*SIGN(ffiiss),
   IF(LEN(ABS(ffiiss))>4,
     (SUM(ABS(LEFT(ffiiss,LEN(ffiiss)-4)*12),VALUE(LEFT(RIGHT(ffiiss,4),2)),RIGHT(ffiiss,2)/16)*SIGN(ffiiss)),
     (LEFT(ABS(ffiiss),LEN(ABS(ffiiss))-2)+RIGHT(ffiiss,2)/16)*SIGN(ffiiss))),
 VALUE(ffiiss)*12))
 )

Then do calculations in decimal inches...

Then convert back to FFIISS format: (assuming A1 contains decimal inches)

Excel Formula:

=VALUE(ROUNDDOWN(ABS(A1)/12,0)*SIGN(A1)&TEXT(INT(MOD(ABS(A1),12)),"00")&TEXT(MOD(MOD(ABS(A1),12),1)*16,"00"))

or

Excel Formula:

=LAMBDA(decInch,
 VALUE(ROUNDDOWN(ABS(decInch)/12,0)*SIGN(decInch)&TEXT(INT(MOD(ABS(decInch),12)),"00")&TEXT(MOD(MOD(ABS(decInch),12),1)*16,"00"))
 )

Some test

Phh

Phuynh · Feb 6, 2024

Correction from previous post
Move *SIGN(A1) and *SIGN(decInch) to end of line to correct convert negative number greater than -1 and less than 0

The correct formula should be

Excel Formula:

=VALUE(ROUNDDOWN(ABS(A1)/12,0)&TEXT(INT(MOD(ABS(A1),12)),"00")&TEXT(MOD(MOD(ABS(A1),12),1)*16,"00"))*SIGN(A1)

and

The correct formula should be

Excel Formula:

=LAMBDA(decInch,
 VALUE(ROUNDDOWN(ABS(decInch)/12,0)&TEXT(INT(MOD(ABS(decInch),12)),"00")&TEXT(MOD(MOD(ABS(decInch),12),1)*16,"00"))*SIGN(decInch)
 )

Sorry, my bad!

Phh

Phuynh · Apr 4, 2024

I think VBA is easier to deal with FFIISS number format in Excel.
Don't mean to be an expert but the following VBA codes with added 8 functions for conversion and simple range (SUM) calculations onto existing VBA code, in this thread in this forum.
I did some test, these functions look OK to me, let me know if there any problem or I need to improve!

Phh

Full VBA code version

VBA Code:

''###################################################################
''## Added                                                         ##
''## 8 Functions to deal with FIS (feet inches sixteen) format     ##
''## in form of FFIISS or IISS or SS or FF. and FF.#               ##
''## By Phh, 4/3/2024                                              ##
''##---------------------------------------------------------------##
''## fis2dec()     Convert to decimal inches (from FFIISS format)  ##
''## dec2fis()     Convert to FFIISS format (from decimal inches)  ##
''## imp2fis()     Convert imperial to FFSSII format               ##
''## fis2impa()    Convert FFIISS to imperial, architect format    ##
''##               with optional precision argument, default 1/16" ##
''## fis2impe()    Convert FFIISS to imperial, engineering format  ##
''##               with optional precision argument, default 1/16" ##
''## sumfis2dec()  Similar to SUM function, decimal format         ##
''## sumfis2impa() Similar to SUM function, architectural format   ##
''## sumfis2impe() Similar to SUM function, engineering format     ##
''###################################################################
''## 7 Functions to deal with feet-inches format in Excel          ##
''## in form of [#'-#"] or [#'-# #/##"] or [#'-#.##"]              ##
''## Functions update to work with negative feet-inches            ##
''## By Phh, 2010, last update 6/26/2022                           ##
''##---------------------------------------------------------------##
''## todec()       Convert to decimal                              ##
''## toimpe()      Convert to imperial, engineering format         ##
''##               with optional precision argument, default 1/16" ##
''## toimpa()      Convert to imperial, architectural format       ##
''##               with optional precision argument, default 1/16" ##
''## sumtodec()    Similar to SUM function, decimal format         ##
''## sumtoimpa()   Similar to SUM function, architectural format   ##
''## sumtoimpe()   Similar to SUM function, engineering format     ##
''## frac2num()    Sub function, convert fraction to decimal       ##
''###################################################################

Option Explicit

''##############################
''## Start 8 FFIISS functions ##
''##############################
Public Function fis2dec(strX As String) As Double
  Dim startPos, dotPos, signofNum As Integer
  strX = Trim$(strX)
  If Left$(strX, 1) = "-" Then
    signofNum = -1
  Else
    signofNum = 1
  End If
  strX = Replace(strX, "-", "")
  startPos = 1
  dotPos = InStr(startPos, strX, ".", vbTextCompare)
  If dotPos > 0 Then
    fis2dec = Val(strX) * 12 * signofNum
    Exit Function
  End If
  If Len(strX) < 3 Then
    fis2dec = Val(strX) / 16 * signofNum
    Exit Function
  End If
  If Len(strX) > 4 Then
     fis2dec = ((Left$(strX, Len(strX) - 4) * 12) + (Left$(Right$(strX, 4), 2)) + (Right$(strX, 2) / 16)) * signofNum
  Exit Function
  End If
  fis2dec = (Left$(strX, Len(strX) - 2) + (Right$(strX, 2) / 16)) * signofNum
End Function

Public Function dec2fis(aNum As Double)
Dim signofNum As Integer
Dim ffNum As Double
Dim iiNum, ssNum As String
If Left$(aNum, 1) = "-" Then
 signofNum = -1
Else
 signofNum = 1
End If
'Abs & Round number to 1/16"
 aNum = Excel.WorksheetFunction.Round(Abs(aNum) / 0.0625, 0) * 0.0625
 ffNum = Fix(aNum / 12)
 iiNum = Excel.WorksheetFunction.Text(Fix(aNum - (ffNum * 12)), "00")
 ssNum = Excel.WorksheetFunction.Text((aNum - Fix(aNum)) * 16, "00")
 dec2fis = CDbl(ffNum & iiNum & ssNum) * signofNum
End Function

Public Function imp2fis(strX As String) As Double
 ''Note: This imp2fis() function will use dec2fis() and todec() as sub-functions
 ''to convert to FFIISS format.
 
 strX = Trim$(strX)
 imp2fis = dec2fis(todec(strX))
End Function

Public Function fis2impa(strX As String, Optional argRd As Variant = 16) As String
 ''Note: This function will use toimpa() as sub-function
 ''to convert to imperial architectural format.
 
  Dim rdLen, rawLen, argRdNum As Double
  Dim startPos, dotPos, signofNum As Integer
  If argRd >= 1 Then
    argRdNum = 1 / Fix(argRd)
  ElseIf argRd < 1 And argRd > 0 Then
    argRdNum = argRd
  End If
  strX = Trim$(strX)
  If Left$(strX, 1) = "-" Then
    signofNum = -1
  Else
    signofNum = 1
  End If
  strX = Replace(strX, "-", "")
  startPos = 1
  dotPos = InStr(startPos, strX, ".", vbTextCompare)
  If dotPos > 0 Then
    fis2impa = toimpa(Val(strX) * 12 * signofNum, argRdNum)
    Exit Function
  End If
  If Len(strX) < 3 Then
    fis2impa = toimpa(Val(strX) / 16 * signofNum, argRdNum)
    Exit Function
  End If
  If Len(strX) > 4 Then
     fis2impa = toimpa(((Left$(strX, Len(strX) - 4) * 12) + (Left$(Right$(strX, 4), 2)) + (Right$(strX, 2) / 16)) * signofNum, argRdNum)
  Exit Function
  End If
  fis2impa = toimpa((Left$(strX, Len(strX) - 2) + (Right$(strX, 2) / 16)) * signofNum, argRdNum)
End Function

Public Function fis2impe(strX As String, Optional argRd As Variant = 16) As String
 ''Note: This function will use toimpe() as sub-function
 ''to convert to imperial engineering format.
 
  Dim rdLen, rawLen, argRdNum As Double
  Dim startPos, dotPos, signofNum As Integer
  If argRd >= 1 Then
    argRdNum = 1 / Fix(argRd)
  ElseIf argRd < 1 And argRd > 0 Then
    argRdNum = argRd
  End If
  strX = Trim$(strX)
  If Left$(strX, 1) = "-" Then
    signofNum = -1
  Else
    signofNum = 1
  End If
  strX = Replace(strX, "-", "")
  startPos = 1
  dotPos = InStr(startPos, strX, ".", vbTextCompare)
  If dotPos > 0 Then
    fis2impe = toimpe(Val(strX) * 12 * signofNum, argRdNum)
    Exit Function
  End If
  If Len(strX) < 3 Then
    fis2impe = toimpe(Val(strX) / 16 * signofNum, argRdNum)
    Exit Function
  End If
  If Len(strX) > 4 Then
     fis2impe = toimpe(((Left$(strX, Len(strX) - 4) * 12) + (Left$(Right$(strX, 4), 2)) + (Right$(strX, 2) / 16)) * signofNum, argRdNum)
  Exit Function
  End If
  fis2impe = toimpe((Left$(strX, Len(strX) - 2) + (Right$(strX, 2) / 16)) * signofNum, argRdNum)
End Function

Public Function sumfis2dec(ParamArray Xrange() As Variant) As Double
  Dim sumArray As Double
  Dim theVal As Variant
  Dim I As Integer
  For I = LBound(Xrange) To UBound(Xrange)
   If TypeOf Xrange(I) Is Range Then
   For Each theVal In Xrange(I)
    sumArray = sumArray + fis2dec(CStr(theVal))
   Next theVal
   Else
    sumArray = sumArray + CDbl(Xrange(I))
   End If
  Next
  sumfis2dec = sumArray
End Function

Public Function sumfis2impa(ParamArray Xrange() As Variant) As String
  Dim sumArray As Double, argRdNum As Double
  Dim theVal As Variant
  Dim I As Integer
  For I = LBound(Xrange) To UBound(Xrange)
   If TypeOf Xrange(I) Is Range Then
   For Each theVal In Xrange(I)
    sumArray = sumArray + fis2dec(CStr(theVal))
   Next theVal
   Else
    sumArray = sumArray + CDbl(Xrange(I))
   End If
  Next
  argRdNum = (1 / 16) 'Set round-off 1/16" for FFIISS format
  sumArray = Excel.WorksheetFunction.Round(sumArray / argRdNum, 0) * argRdNum
  If sumArray <= -12 Or sumArray >= 12 Then
    sumfis2impa = (Fix(sumArray / 12)) & "'-" & Excel.WorksheetFunction.Text(Abs(sumArray - (12 * Fix(sumArray / 12))), "0 #/####") & """"
  ElseIf sumArray < 12 And sumArray > -12 Then
    If (sumArray - Fix(sumArray)) = 0 Then
      sumfis2impa = sumArray & """"
    Else
      sumfis2impa = Excel.WorksheetFunction.Text(sumArray, "# #/####") & """"
    End If
 End If
End Function

Public Function sumfis2impe(ParamArray Xrange() As Variant) As String
  Dim sumArray As Double, argRdNum As Double
  Dim theVal As Variant
  Dim I As Integer
  For I = LBound(Xrange) To UBound(Xrange)
   If TypeOf Xrange(I) Is Range Then
   For Each theVal In Xrange(I)
    sumArray = sumArray + fis2dec(CStr(theVal))
   Next theVal
   Else
    sumArray = sumArray + CDbl(Xrange(I))
   End If
  Next
  argRdNum = (1 / 16) 'Set round-off 1/16" for FFIISS format
  sumArray = Excel.WorksheetFunction.Round(sumArray / argRdNum, 0) * argRdNum
  If sumArray <= -12 Or sumArray >= 12 Then
    sumfis2impe = (Fix(sumArray / 12)) & "'-" & Abs(sumArray - (12 * Fix(sumArray / 12))) & """"
  ElseIf sumArray < 12 And sumArray > -12 Then
    sumfis2impe = sumArray & """"
  End If
End Function
''############################
''## End 8 FFIISS functions ##
''############################


Public Function todec(strX As String) As Double
  Dim startPos, ftPos, frPos, signofNum As Integer
  Dim rdLen As Double
  strX = Trim$(strX)
  If Left$(strX, 1) = "-" Then
    signofNum = -1
  Else
    signofNum = 1
  End If
  strX = Replace(Replace(strX, """", ""), "-", "")
  startPos = 1
  ftPos = InStr(startPos, strX, "'")
  frPos = InStr(startPos, strX, "/")
  If ftPos = 0 And frPos = 0 Then
    todec = Val(strX) * signofNum
    Exit Function
  End If
  If ftPos = 0 And frPos > 0 Then
    todec = frac2num(strX) * signofNum
    Exit Function
  End If
  rdLen = CDbl(Left$(strX, ftPos - 1)) * 12
  If frPos = 0 Then
    rdLen = rdLen + (Val(Mid$(strX, ftPos + 1, Len(strX))))
    todec = rdLen * signofNum
    Exit Function
  End If
  rdLen = rdLen + frac2num(Mid$(strX, ftPos + 1, Len(strX)))
  todec = rdLen * signofNum
End Function

Public Function toimpe(rawLen As Double, Optional argRd As Variant = 16) As String
 Dim rdLen As Double, argRdNum As Double
 If argRd >= 1 Then
   argRdNum = 1 / Fix(argRd)
    rdLen = Excel.WorksheetFunction.Round(rawLen / argRdNum, 0) * argRdNum
 ElseIf argRd < 1 And argRd > 0 Then
   argRdNum = argRd
    rdLen = Excel.WorksheetFunction.Round(rawLen / argRdNum, 0) * argRdNum
 Else
    rdLen = rawLen
 End If
 If Abs(Excel.WorksheetFunction.Round(rawLen / argRdNum, 0)) < Abs(argRdNum) Then
    toimpe = "0"""
    Exit Function
 End If
 If rdLen <= -12 Or rdLen >= 12 Then
    toimpe = (Fix(rdLen / 12)) & "'-" & Abs(rdLen - (12 * Fix(rdLen / 12))) & """"
 ElseIf rdLen < 12 And rdLen > -12 Then
      toimpe = rdLen & """"
 End If
End Function

Public Function toimpa(rawLen As Double, Optional argRd As Variant = 16) As String
 Dim rdLen As Double, argRdNum As Double
 If argRd >= 1 Then
   argRdNum = 1 / Fix(argRd)
    rdLen = Excel.WorksheetFunction.Round(rawLen / argRdNum, 0) * argRdNum
 ElseIf argRd < 1 And argRd > 0 Then
   argRdNum = argRd
    rdLen = Excel.WorksheetFunction.Round(rawLen / argRdNum, 0) * argRdNum
 Else
    rdLen = rawLen
 End If
 If Abs(Excel.WorksheetFunction.Round(rawLen / argRdNum, 0)) < Abs(argRdNum) Then
    toimpa = "0"""
    Exit Function
 End If
 If rdLen <= -12 Or rdLen >= 12 Then
    toimpa = (Fix(rdLen / 12)) & "'-" & Excel.WorksheetFunction.Text(Abs(rdLen - (12 * Fix(rdLen / 12))), "0 #/####") & """"
 ElseIf rdLen < 12 And rdLen > -12 Then
    If (rdLen - Fix(rdLen)) = 0 Then
      toimpa = rdLen & """"
    Else
      toimpa = Excel.WorksheetFunction.Text(rdLen, "# #/####") & """"
    End If
 End If
End Function

Public Function sumtodec(ParamArray Xrange() As Variant) As Double
  Dim sumArray As Double
  Dim theVal As Variant
  Dim I As Integer
 
  For I = LBound(Xrange) To UBound(Xrange)
   If TypeOf Xrange(I) Is Range Then
   For Each theVal In Xrange(I)
    sumArray = sumArray + todec(CStr(theVal))
   Next theVal
   Else
    sumArray = sumArray + CDbl(Xrange(I))
   End If
  Next
  sumtodec = sumArray
End Function

Public Function sumtoimpe(ParamArray Xrange() As Variant) As String
  Dim sumArray As Double, argRdNum As Double
  Dim theVal As Variant
  Dim I As Integer
  For I = LBound(Xrange) To UBound(Xrange)
   If TypeOf Xrange(I) Is Range Then
   For Each theVal In Xrange(I)
    sumArray = sumArray + todec(CStr(theVal))
   Next theVal
   Else
    sumArray = sumArray + CDbl(Xrange(I))
   End If
  Next
 
''########################################################################
''## Set precision round-off to 1/512" as default, change if required!  ##
''########################################################################
  argRdNum = (1 / 512)
 
  sumArray = Excel.WorksheetFunction.Round(sumArray / argRdNum, 0) * argRdNum
  If sumArray <= -12 Or sumArray >= 12 Then
    sumtoimpe = (Fix(sumArray / 12)) & "'-" & Abs(sumArray - (12 * Fix(sumArray / 12))) & """"
  ElseIf sumArray < 12 And sumArray > -12 Then
    sumtoimpe = sumArray & """"
  End If
End Function

Public Function sumtoimpa(ParamArray Xrange() As Variant) As String
  Dim sumArray As Double, argRdNum As Double
  Dim theVal As Variant
  Dim I As Integer
  For I = LBound(Xrange) To UBound(Xrange)
   If TypeOf Xrange(I) Is Range Then
   For Each theVal In Xrange(I)
    sumArray = sumArray + todec(CStr(theVal))
   Next theVal
   Else
    sumArray = sumArray + CDbl(Xrange(I))
   End If
  Next
 
''########################################################################
''## Set precision round-off to 1/512" as default, change if required!  ##
''########################################################################
  argRdNum = (1 / 512)
 
  sumArray = Excel.WorksheetFunction.Round(sumArray / argRdNum, 0) * argRdNum
  If sumArray <= -12 Or sumArray >= 12 Then
    sumtoimpa = (Fix(sumArray / 12)) & "'-" & Excel.WorksheetFunction.Text(Abs(sumArray - (12 * Fix(sumArray / 12))), "0 #/####") & """"
  ElseIf sumArray < 12 And sumArray > -12 Then
    If (sumArray - Fix(sumArray)) = 0 Then
      sumtoimpa = sumArray & """"
    Else
      sumtoimpa = Excel.WorksheetFunction.Text(sumArray, "# #/####") & """"
    End If
 End If
End Function

Function frac2num(ByVal X As String) As Double
  Dim P As Integer
  Dim N As Double, Num As Double, Den As Double
  X = (X)
  P = InStr(X, "/")
  If P = 0 Then
    N = Val(X)
  Else
    Den = Val(Mid$(X, P + 1))
    If Den = 0 Then Error 11
      X = Trim$(Left$(X, P - 1))
      P = InStr(X, " ")
    If P = 0 Then
      Num = Val(X)
    Else
      Num = Val(Mid$(X, P + 1))
      N = Val(Left$(X, P - 1))
    End If
  End If
  If Den <> 0 Then
    N = N + Num / Den
  End If
  frac2num = N
End Function

Feet Inches Sixteenths Number Format

garsard

New Member

Excel Facts

felixstraube

Well-known Member

Phuynh

New Member

Phuynh

New Member

Phuynh

New Member

Phuynh

New Member

Similar threads

Forum statistics

Share this page

Feet Inches Sixteenths Number Format

garsard

New Member

Excel Facts

felixstraube

Well-known Member

Phuynh

New Member

Phuynh

New Member

Phuynh

New Member

Phuynh

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock