phuynh Posted February 21, 2022 Posted February 21, 2022 Hello everyone, Just a collection of functions that I learn and post here, hope they are usefull to you just as I do in my day to day job. Phh ''################################################################# ''## Functions to deal with feet-inches format ## ''## in a form of [#'-#"] or [#'-# #/##"] or [#'-#.##"] ## ''## By Phh, 2010, last update 2021 ## ''################################################################# ''## 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 ## ''## sumtoimpe() Similar to SUM function, engineering format ## ''## sumtoimpa() Similar to SUM function, architectural format ## ''## frac2num() Sub function, convert fraction to decimal ## ''################################################################# Option Explicit Public Function todec(strX As String, Optional argDivBy As Double) As Double Dim startPos As Integer, ftPos As Integer, frPos As Integer Dim rdLen, argDivNum As Double If argDivBy > 0 Then argDivNum = argDivBy Else argDivNum = 1 End If strX = Replace(Replace(strX, """", ""), "-", "") strX = WorksheetFunction.Trim(strX) startPos = 1 ftPos = InStr(startPos, strX, "'") frPos = InStr(startPos, strX, "/") If ftPos = 0 And frPos = 0 Then todec = (Val(strX) / argDivNum) Exit Function End If If ftPos = 0 And frPos > 0 Then todec = (frac2num(strX) / argDivNum) Exit Function End If rdLen = CDbl(Left(strX, ftPos - 1)) * 12 If frPos = 0 Then rdLen = rdLen + (Abs(Val(Mid(strX, ftPos + 1, Len(strX))))) todec = (rdLen / argDivNum) Exit Function End If rdLen = rdLen + frac2num(Mid(strX, ftPos + 1, Len(strX))) todec = (rdLen / argDivNum) 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 toimpe(aLen As Double, Optional argRd As Variant = 16) As String Dim rdLen As Double, argRdNum As Double If argRd >= 1 Then argRdNum = 1 / Fix(argRd) ElseIf argRd < 1 And argRd > 0 Then argRdNum = argRd ElseIf argRd = 0 Then toimpe = (Fix(aLen / 12)) & "'-" & (aLen - (12 * Fix(aLen / 12))) & """" Exit Function End If rdLen = excel.WorksheetFunction.Round(aLen / argRdNum, 0) * argRdNum toimpe = (Fix(rdLen / 12)) & "'-" & (rdLen - (12 * Fix(rdLen / 12))) & """" End Function Public Function toimpa(aLen As Double, Optional argRd As Variant = 16) As String Dim rdLen As Double, argRdNum As Double If argRd >= 1 Then argRdNum = 1 / Fix(argRd) ElseIf argRd < 1 And argRd > 0 Then argRdNum = argRd ElseIf argRd = 0 Then toimpa = (Fix(aLen / 12)) & "'-" & excel.WorksheetFunction.Text((aLen - (12 * Fix(aLen / 12))), "0 ##/####") & """" Exit Function End If rdLen = excel.WorksheetFunction.Round(aLen / argRdNum, 0) * argRdNum toimpa = (Fix(rdLen / 12)) & "'-" & excel.WorksheetFunction.Text((rdLen - (12 * Fix(rdLen / 12))), "0 ##/####") & """" 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 of to 1/256" as default, change if required! argRdNum = (1 / 256) sumArray = excel.WorksheetFunction.Round(sumArray / argRdNum, 0) * argRdNum sumtoimpe = (Fix(sumArray / 12)) & "'-" & (sumArray - (12 * Fix(sumArray / 12))) & """" 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 of to 1/256" as default, change if required! argRdNum = (1 / 256) sumArray = excel.WorksheetFunction.Round(sumArray / argRdNum, 0) * argRdNum sumtoimpa = (Fix(sumArray / 12)) & "'-" & excel.WorksheetFunction.Text((sumArray - (12 * Fix(sumArray / 12))), "0 ##/####") & """" 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 = Trim$(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 2 Quote
phuynh Posted August 27, 2022 Author Posted August 27, 2022 (edited) Update functions to work with negative feet-inches Remove optional "argument_format" Phh ''################################################################# ''## 7 Functions to deal with feet-inches format in Excel ## ''## in form of [#'-#"] or [#'-# #/##"] or [#'-#.##"] ## ''## By Phh, 2010, last update 6/26/2022 ## ''## Functions update to work with negative feet-inches ## ''################################################################# ''## 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 ## ''## sumtoimpe() Similar to SUM function, engineering format ## ''## sumtoimpa() Similar to SUM function, architectural format ## ''## frac2num() Sub function, convert fraction to decimal ## ''################################################################# Option Explicit 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 Edited September 4, 2022 by phuynh Replace "Excel.WorkSheetFunction.Trim()" with "Trim$()", .Mid() with Mid$, .Left() with Left$() 1 Quote
phuynh Posted September 20, 2022 Author Posted September 20, 2022 (edited) Just plain Excel formula for quick convert from decimal to Imperial feet-inches, when I do BOM, schedule for my drawings, or share workbook to ppl don't use VBA (like my boss and my co-worker, Mac users) Update formula to resolve rounding & text formatting issue (11/5/2022), see post below From decimal (inches) to Imperial architectural format round-off 1/64 =IF(NOT(ISNUMBER(A1)),"n/a", IF(OR(MROUND(A1,SIGN(A1)*1/64)>=12,MROUND(A1,SIGN(A1)*1/64)<=-12), ROUNDDOWN(MROUND(A1,SIGN(A1)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(A1),1/64),12),"0 #/##")&"""", TEXT(MROUND(A1,SIGN(A1)*1/64),"# #/##")&"""")) From decimal (inches) to Imperial engineering format round-off 1/64 =IF(NOT(ISNUMBER(A1)),"n/a", IF(OR(MROUND(A1,SIGN(A1)*1/64)>=12,MROUND(A1,SIGN(A1)*1/64)<=-12), ROUNDDOWN(MROUND(A1,SIGN(A1)*1/64)/12,0)&"'-"&MOD(MROUND(ABS(A1),1/64),12)&"""", MROUND(A1,SIGN(A1)*1/64)&"""")) From decimal (feet) to Imperial architectural format round-off 1/64 =IF(NOT(ISNUMBER(A1)),"n/a", IF(OR(MROUND(A1*12,SIGN(A1)*1/64)>=12,MROUND(A1*12,SIGN(A1)*1/64)<=-12), ROUNDDOWN(MROUND(A1*12,SIGN(A1)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(A1*12),1/64),12),"0 #/##")&"""", TEXT(MROUND(A1*12,SIGN(A1)*1/64),"# #/##")&"""")) From decimal (feet) to Imperial engineering format round-off 1/64 =IF(NOT(ISNUMBER(A1)),"n/a", IF(OR(MROUND(A1*12,SIGN(A1)*1/64)>=12,MROUND(A1*12,SIGN(A1)*1/64)<=-12), ROUNDDOWN(MROUND(A1*12,SIGN(A1)*1/64)/12,0)&"'-"&MOD(MROUND(ABS(A1*12),1/64),12)&"""", MROUND(A1*12,SIGN(A1)*1/64)&"""")) Phh Edited November 23, 2022 by phuynh Update formula 2nd line(s) to resolve rounding and text formatting issue, Ex: 119.999 when convert round-off should display 10'-0" instead of 9'-12" (my apology!) 1 Quote
phuynh Posted October 31, 2022 Author Posted October 31, 2022 (edited) Update formula (all) 2nd lines (see post above) to resolve rounding and text formatting issue, For example: 119.999 when convert and round-off should display 10'-0" instead of 9'-12" Update MROUND() using with SIGN() to correct issue with negative number My apology! Phh Edited October 31, 2022 by phuynh 2 Quote
Danielm103 Posted November 4, 2022 Posted November 4, 2022 Good stuff! I the approach when importing from excel to cad using C++. 1, read the number raw value from excel. 2, read the number format ID; 3, read the format, example #,##0.00, #.##0.00 or "\'" etc. 4, translate using cad functions, LUPREC, LUNITS, acutCvUnit etc. For tables, I use AcValue and a format string %lu2%pr2 if I see #,##0.00 There’s a thing for the thousand’s separator, %th I think Not sure if that stuff in VBA 1 Quote
phuynh Posted November 4, 2022 Author Posted November 4, 2022 (edited) There is something I am still learning, some minor issue with rounding when combine with text formatting, and fact that I work it out but still test and debugging! Let try this sequence with round-off 1/64 for example: From decimal (inches) to Imperial architectural format round-off 1/64 Cell A1 11.9 = 11 29/32" [Correct] 11.99 = 11 63/64" [Correct] 11.999 = 12" [Correct but some what!] I expect the format would display 1'-0" but end up 12" ?!?! what I learn is that on the 1st line of the formula, the second IF() statement the value of A1 not the same value of A1 on 2nd and 3rd lines because of MROUND()! To correct this issue, the value of A1 should be exact the same throughout! Here the update version I am currently testing... but not final, please bear with me for my hiccup! =IF(OR(MROUND(A1,SIGN(A1)*1/64)>=12,MROUND(A1,SIGN(A1)*1/64)<=-12), ROUNDDOWN(MROUND(A1,SIGN(A1)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(A1),1/64),12),"0 #/##")&"""", TEXT(MROUND(A1,SIGN(A1)*1/64),"# #/##")&"""") Please note that I sacrifice first IF() because it is too long, move SIGN() inside MROUND() and remove ABS(), shorten format numerator (the denominator number digits are importance and must match with number of digits of rounding, ex. 1/2, 1/8 -> #/# ; 1/16, 1/32, 1/64 -> #/## ; 1/4096 -> #/####, etc.) Will keep you post, and sorry for my bad! 11/5/2022 Update formula to resolve rounding & text formatting issue (see post above) Rearrange MROUND() and SIGN() for consistent and easy to read Phh Edited November 6, 2022 by phuynh 1 Quote
phuynh Posted November 21, 2022 Author Posted November 21, 2022 (edited) Excel LAMBDA Function In Excel, Office 365 or recent version of Excel if LAMBDA function available, you can take advance of looooong calculation formula by using LAMBDA function! Per MS: Quote Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions. You can create a function for a commonly used formula, eliminate the need to copy and paste this formula (which can be error-prone), and effectively add your own functions to the native Excel function library. Furthermore, a LAMBDA function doesn’t require VBA, macros or JavaScript, so non-programmers can also benefit from its use. Syntax =LAMBDA([parameter1, parameter2, …,] calculation) Expand Define Name: give_a_meaningful_name Clear "Refer to" box: And paste your calculation formula to create custom user define function. In this case, for example: I create name "str2impa" and replace "A1" to "in" which is the parameter. =LAMBDA(in, IF(NOT(ISNUMBER(in)),"n/a", IF(OR(MROUND(in,SIGN(in)*1/64)>=12,MROUND(in,SIGN(in)*1/64)<=-12), ROUNDDOWN(MROUND(in,SIGN(in)*1/64)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(in),1/64),12),"0 #/##")&"""", TEXT(MROUND(in,SIGN(in)*1/64),"# #/##")&"""")) ) Phh Edited November 24, 2022 by phuynh 1 Quote
phuynh Posted October 1, 2023 Author Posted October 1, 2023 (edited) Tweak version of lambda function from previous post with added flexibility of conversion and simple range calculations if you know your conversion factor and round-off number of your choice. Lambda function for convert decimal value to imperial feet-inches fraction, text format [#'-# #/#"] Name: testfunc (or some meaningful name) usage: testfunc(varNum, cFact, rdOff) Parameters notes: varNum = Required, decimal number value to be converted (ex: value in a cell A1, B10, C15, etc.. or range A:A, or multiple ranges (A:A,B:B) - note of enclosed parenthesis and ranges separated by comma) cFact = Required, conversion factor from value (1 = inch, 1/12 = feet, 1/36 = yard, 25.4 = millimeter, 2.54 = centimeter, 0.0254 = meter, etc...) Or define Excel Name to hold conversion factor value, ex: in=1 ft=1/12 yd=1/36 mm=25.4 cm=2.54 m=0.0254 ... ... ... rdOff = Required, rounded to the desired multiple (ex: 1, 1/2, 1/4, 1/8, 1/16, 1/64, 1/4096, etc...) =LAMBDA(varNum, cFact, rdOff, IF(NOT(ISNUMBER(SUM(varNum))),"n/a", IF(OR(MROUND(SUM(varNum)/cFact,SIGN(SUM(varNum))*rdOff)>=12,MROUND(SUM(varNum)/cFact,SIGN(SUM(varNum))*rdOff)<=-12), ROUNDDOWN(MROUND(SUM(varNum)/cFact,SIGN(SUM(varNum))*rdOff)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(SUM(varNum)/cFact),rdOff),12),"0 #/####")&"""", TEXT(MROUND(SUM(varNum)/cFact,SIGN(SUM(varNum))*rdOff),"# #/####")&"""")) ) Phh Edited April 9, 2024 by phuynh 1 Quote
LKW Posted January 2, 2024 Posted January 2, 2024 phuynh, I'm totally lost. I have 2 ranges of cells (M6:M2307) & (O6:O2307) where the values are entered as decimal feet & inches. Ex. 10'-10" is entered in cell as 10.8333. Would you be so kind as to show me how to get your LAMBDA function to work? Pretty new to this this type of advanced formula so please be patient with my ignorance. Thanks, LKW Quote
phuynh Posted January 3, 2024 Author Posted January 3, 2024 (edited) Hi LKM, If having multi-range, enclosed with parenthesis and ranges separated by comma to become 1 element represent varNum, the function should work. ex. testfunc((M6:M2307,O6:O2307),1,1/64) Phh Edited April 9, 2024 by phuynh Quote
phuynh Posted May 29, 2024 Author Posted May 29, 2024 (edited) LAMBDA function for convert Imperial feet-inches (in various formats) to decimal value, with optional argument conversion factor [cFact]. or define "Excel Name" to hold conversion factor such as: in=1 (default inch if not specify) ft=1/12 yd=1/36 mi=1/63360 (mile) mm=25.4 cm=2.54 m=0.0254 ... ... ... UDF/ Excel Name: todec() =LAMBDA(imperial,[cFact], LET(si,IF(LEFT(imperial,1)="-",-1,1), ft,IFERROR(ABS(VALUE(LEFT(imperial,(FIND("'",imperial)-1)))),0), in,TRIM(SUBSTITUTE(SUBSTITUTE(IFERROR(RIGHT(imperial,LEN(imperial)-FIND("'",imperial)),imperial),"-",""),"""","")), si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in)))/IF(ISOMITTED(cFact),1,1/cFact)) ) Some test Phh Edited December 19, 2024 by phuynh 1 Quote
phuynh Posted December 19, 2024 Author Posted December 19, 2024 (edited) Friends, This is an update: LAMBDA function for convert decimal value to imperial architect format, with optional argument conversion from factor [cFact], similar format from previous post for consistency with a fixed 1/64 round-off. UDF/ Excel Name: toimpa() =LAMBDA(decimal,[cFact], LET(si,IF(LEFT(decimal,1)="-",-1,1), rd,1/64, cf,IF(ISOMITTED(cFact),1,cFact), IF(NOT(ISNUMBER(decimal)),"n/a", IF(OR(MROUND(decimal/cf,si*rd)>=12,MROUND(decimal/cf,si*rd)<=-12), ROUNDDOWN(MROUND(decimal/cf,si*rd)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(decimal/cf),rd),12),"0 #/####")&"""", TEXT(MROUND(decimal/cf,si*rd),"# #/####")&"""")) ) ) In addition, here are some test/experiment functions, these will work with imperial array: Note that these functions use todec() and toimpa() functions from above and in previous post. UDF/Excel Name: sumtoimpa() Similar to Excel SUM() function =LAMBDA(imperials,toimpa(SUM(MAP(imperials,todec)))) UDF/Excel Name: sumtodec() - with optional argument convert to factor [cFact] Similar to Excel SUM() function =LAMBDA(imperials,[cFact],(SUM(MAP(imperials,todec))*IF(ISOMITTED(cFact),1,cFact))) UDF/Excel Name: averageimpa() Similar to Excel AVERAGE() function =LAMBDA(imperials,toimpa(AVERAGE(MAP(imperials,todec)))) UDF/Excel Name: minimpa() Similar to Excel MIN() function =LAMBDA(imperials,toimpa(MIN(MAP(imperials,todec)))) UDF/Excel Name: maximpa() Similar to Excel MAX() function =LAMBDA(imperials,toimpa(MAX(MAP(imperials,todec)))) UDF/Excel Name: rangeimpa() Similar to Excel (MAX() - MIN()) functions =LAMBDA(imperials,toimpa(MAX(MAP(imperials,todec))-MIN(MAP(imperials,todec)))) UDF/Excel Name: largeimpa() Similar to Excel LARGE() function =LAMBDA(imperials,rank,toimpa(LARGE(MAP(imperials,todec),rank))) UDF/Excel Name: smallimpa() Similar to Excel SMALL() function =LAMBDA(imperials,rank,toimpa(SMALL(MAP(imperials,todec),rank))) Let me know if there any problem. Happy holidays! Phh Some test Edited December 20, 2024 by phuynh 1 Quote
phuynh Posted February 11 Author Posted February 11 (edited) For simple cell conversion, those formula from previous posts, they work as intended. However when using with range there are some issue with formatted display & errors, for example: UDF/ Excel name: toimpa() - See below It seem like I dig my own hole! and now I try to get out! My apology! To address these unintended issues, update are necessary, some UDF functions refine to work with conversion factor from & to directions, see function note for details. Phh Excel UDF Revision 1.1 - 2/10/2025 By Phh Update Notes: Updated formula to work with range(s), fixed some format issuse after converion display specially toimpa(), toimpe() If using range, range must be in consecutive (ex. A3:A20), if having multiple ranges on various locations, use Excel VSTACK() function to pack them up (ex. VSTACK(A3:A6,A11:A20,C3:D6). Beaware that convert from_factor and convert to_factor, depend on function used they have different effect for example: toimpa(), sumtoimpa() - when using range with mixed imperials and decimal value in range, only decimal values are converted based on supplied conversion factor, see function note for details. Strongly recommended but not required to define "Excel Name" to hold conversion factors such as: in=1 (default inch if not specify) ft=1/12 yd=1/36 mi=1/63360 (mile) mm=25.4 cm=2.54 m=0.0254 km=1/39370 ... ##**************************************************************************************# ## 1. UDF/ Excel name: todec() - Convert various imperial feet-inches to decimal # ## with optional argument convert to-factor [to_cFactor] # ## Rev. 1.1 - 2/10/2025 # ## Update to correct convert number with scientific notation (ex: -3.94571E-06...) # ##**************************************************************************************# =LAMBDA(imperial,[to_cFactor], IF(ISNUMBER(imperial),imperial/IF(ISOMITTED(to_cFactor),1,1/to_cFactor), LET(si,IF(LEFT(imperial,1)="-",-1,1), ft,IFERROR(ABS(VALUE(LEFT(imperial,(FIND("'",imperial)-1)))),0), in,TRIM(SUBSTITUTE(SUBSTITUTE(IFERROR(RIGHT(imperial,LEN(imperial)-FIND("'",imperial)),imperial),"-",""),"""","")), si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in)))/IF(ISOMITTED(to_cFactor),1,1/to_cFactor))) ) ##**********************************************************************************************# ## 2. UDF/ Excel name: toimpa() - Convert decimal to imperial feet-inches, architectural format # ## with optional arguments convert from-factor [from_cFactor], and round-off [rdOff] # ## Notes: Convert from-factor only apply for cell contains number or valuated as a number by # ## Excel VALUE() function, for others form of imperial feet-inches these will not # ## apply for the conversion such as 1'-2", 3', 16", 1/4" etc. # ## Argument round-off [rdOff], number such as 2, 4, 8, 16, 32, 64, 128... equaling # ## 1/2, 1/4, 1/8, 1/16, 1/32, 1/64, 128..., also argument round-off can be enter in # ## the form .5, .25, .3125, .0625... or 1/2, 1/4, 1/16...etc. for number less than 1. # ## By default, if there no specify, the round-off number will be 64 (aka 1/64) # ## Rev. 1.1 - 2/10/2025 # ##**********************************************************************************************# =LAMBDA(varNum,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), de,IF(ISBLANK(varNum),NA(),IF(ISNUMBER(varNum),varNum/cf,IFERROR(todec(varNum),VALUE(varNum)))), MAP(de,BYROW(de,LAMBDA(r,rd)), LAMBDA(d,r, IFERROR(IF(AND(MROUND(d,SIGN(d)*r)<12,MROUND(d,SIGN(d)*r)>-12),(TEXT(MROUND(d,SIGN(d)*r),"# #/####")&""""), (ROUNDDOWN(MROUND(d,SIGN(d)*r)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(d),r),12),"0 #/####")&"""")), NA()) ) ) ) ) ##**********************************************************************************************# ## 3. UDF/ Excel name: toimpe() - Convert decimal to imperial feet-inches, engineering format # ## with optional arguments convert from-factor [from_cFactor], and round-off [rdOff] # ## Notes: Convert from-factor only apply for cell contains number or valuated as a number by # ## Excel VALUE() function, for others form of imperial feet-inches these will not # ## apply for the conversion such as 1'-2", 3', 16", 1/4" etc. # ## Argument round-off [rdOff], number such as 2, 4, 8, 16, 32, 64, 128... equaling # ## 1/2, 1/4, 1/8, 1/16, 1/32, 1/64, 128..., also argument round-off can be enter in # ## the form .5, .25, .3125, .0625... or 1/2, 1/4, 1/16...etc. for number less than 1. # ## By default, if there no specify, the round-off number will be 64 (aka 1/64) # ## Rev. 1.1 - 2/10/2025 # ##**********************************************************************************************# =LAMBDA(varNum,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), de,IF(ISBLANK(varNum),NA(),IF(ISNUMBER(varNum),varNum/cf,IFERROR(todec(varNum),VALUE(varNum)))), MAP(de,BYROW(de,LAMBDA(r,rd)), LAMBDA(d,r, IFERROR(IF(AND(MROUND(d,SIGN(d)*r)<12,MROUND(d,SIGN(d)*r)>-12),MROUND(d,SIGN(d)*r)&"""", (ROUNDDOWN(MROUND(d,SIGN(d)*r)/12,0)&"'-"&MOD(MROUND(ABS(d),r),12)&"""")), NA()) ) ) ) ) ##**************************************************************************************# ## 4. UDF/ Excel name: sumtodec() - Similar to Excel SUM(), optional arguments convert # ## to-factor [to_cFactor] # ## Rev. 1.1 - 2/10/2025 # ##**************************************************************************************# =LAMBDA(varNum,[to_cFactor], LET(cf,IF(ISOMITTED(to_cFactor),1,to_cFactor), de,IF(ISBLANK(varNum),NA(),IFERROR(todec(varNum),VALUE(varNum))), SUM(de)*cf ) ) ##**************************************************************************************# ## 5. UDF/ Excel name: sumtoimpa() - Similar to Excel SUM(), optional arguments convert # ## from-factor [cFactor], see note convert from-factor in UDF/ Excel name: toimpa() # ## above for details # ## Rev. 1.1 - 2/10/2025 # ##**************************************************************************************# =LAMBDA(imperials,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), toimpa(SUM(todec(MAP(imperials,MAP(imperials,LAMBDA(a,cf)),toimpa))),1,rd)) ) ##**************************************************************************************# ## 6. UDF/ Excel name: sumtoimpe() - Similar to Excel SUM(), optional arguments convert # ## from-factor [cFactor], see note convert from-factor in UDF/ Excel name: toimpe() # ## above for details # ## Rev. 1.1 - 2/10/2025 # ##**************************************************************************************# =LAMBDA(imperials,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), toimpe(SUM(todec(MAP(imperials,MAP(imperials,LAMBDA(a,cf)),toimpe))),1,rd)) ) ##******************************************************************************# ## 7. UDF/ Excel name: averageimpa() - Similar to Excel AVERAGE() function # ## Rev. 1.1 - 2/10/2025 # ## Update, added convert from-factor [from_cFactor] # ##******************************************************************************# =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), toimpa(AVERAGE(MAP(imperials,todec)/cf))) ) ##******************************************************************************# ## 8. UDF/ Excel name: minimpa() - Similar to Excel MIN() function # ## Rev. 1.1 - 2/10/2025 # ## Update, added convert from-factor [from_cFactor] # ##******************************************************************************# =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), toimpa(MIN(MAP(imperials,todec)/cf))) ) ##******************************************************************************# ## 9. UDF/ Excel name: maximpa() - Similar to Excel MAX() function # ## Rev. 1.1 - 2/10/2025 # ## Update, added convert from-factor [from_cFactor] # ##******************************************************************************# =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), toimpa(MAX(MAP(imperials,todec)/cf))) ) ##******************************************************************************# ## 10. UDF/ Excel name: rangeimpa() - Similar to Excel MAX() - MIN() functions # ## Rev. 1.1 - 2/10/2025 # ## Update, added convert from-factor [from_cFactor] # ##******************************************************************************# =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), toimpa(MAX(MAP(imperials,todec)/cf)-MIN(MAP(imperials,todec)/cf))) ) ##******************************************************************************# ## 11. UDF/ Excel name: smallimpa() - Similar to Excel SMALL() functions # ## Rev. 1.1 - 2/10/2025 # ## Update, added convert from-factor [from_cFactor] # ##******************************************************************************# =LAMBDA(imperials,rank,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), toimpa(SMALL(MAP(imperials,todec)/cf,rank))) ) ##******************************************************************************# ## 12. UDF/ Excel name: largeimpa() - Similar to Excel LARGE() functions # ## Rev. 1.1 - 2/10/2025 # ## Update, added convert from-factor [from_cFactor] # ##******************************************************************************# =LAMBDA(imperials,rank,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), toimpa(LARGE(MAP(imperials,todec)/cf,rank))) ) Edited February 12 by phuynh Quote
phuynh Posted March 15 Author Posted March 15 (edited) Friends, An other round of update, refine functions to work with blank cells, added Feet-Inches-Sixteenths number format for conversion and simple calculations. The list of Excel UDF now grow to 34, and it long, so instead of list all in this post, see text file (or Excel 365 Test and Debug if you refer) attached in the end of this post. Please note that all Excel UDF are consider experiments, error may occur, use at your own risk! Let me know if you find any bug or error, Thank you! Phh Excel UDFs Revision 1.3 - 3/15/2025 By Phh Disclaimer: All Excel UDFs listed below consider experiments - (No VBA/macros), although they are intensively tested and debugged, error may occur, use at your own risk! Update Notes: Functions now work with range. Added Feet-Inches-Sixteenths (FFIISS or fis format) conversion/ simple calculations. Replace UDFs those return Error #N/A! are replaced with text string "n/a" instead, to prevent SUM() stopping the calculations when blank cells in range occur. Update UDFs those with the line with MAP() function for consistency calculations. Updated formula to work with range, fixed some format issues after conversion display specially toimpa(), toimpe() If using range, range must be in consecutive (ex. A3:A20), if having multiple ranges on various locations, use Excel VSTACK() function to pack them up (ex. VSTACK(A3:A6,A11:A20,C3:D6). Be aware that convert from_factor and convert to_factor, depend on function they have different effect for example: toimpa(), sumtoimpa() - when using range with mixed imperials and decimal value in range, only decimal values are converted based on the conversion factor, see function note for details. Update averageimpa(), averageimpe() incorrect result when cell(s) are blank Update functions when empty (blank) cell(s) or error in dynamic range/ range will output literal text "n/a" instead of function NA() so excel SUM() function continues to calculate. UDF name scope, ending with: ...dec -> Return decimal value, default decimal inch. ...fis -> Return number represent Feet-Inches-Sixteenths format, ex. 120608 (aka 12'-6 1/2") ..impa -> Return imperial architectural format, ex. 9 1/4", 12'-1 5/16", 7/16" etc. ..impe -> Return imperial engineering format, ex. 9.25", 12'-1.3125", 0.4375" etc. ... UDF name scope, begin with/ or [Excel function nane]+: imp... -> Imeprial to, convert to, etc. fis... -> Feet Inches Sixteenths (fis) to, convert to, etc. ... Strongly recommended but not required to define "Excel Name" to hold conversion factors such as: in=1 (default inch if not specify) ft=1/12 yd=1/36 mi=1/63360 (mile) mm=25.4 cm=2.54 m=0.0254 km=1/39370 ... Fuction List: 1. todec() 2. toimpa() 3. toimpe() 4. sumtodec() 5. sumtoimpa() 6. sumtoimpe() 7. averageimpa() 8. averageimpe() 9. minimpa() 10. minimpe() 11. maximpa() 12. maximpe() 13. rangeimpa() 14. rangeimpe() 15. largeimpa() 16. largeimpe() 17. smallimpa() 18. smallimpe() 19. fis2dec() 20. dec2fis() 21. imp2fis() 22. fis2impa() 23. fis2impe() 24. sumfis2dec() 25. sumfis2impa() 26. sumfis2impe() 27. averagefis() 28. minfis() 29. maxfis() 30. rangefis() 31. smallfis() 32. largefis() 33. sumfis() 34. sumimp2fis() ##************************************************************************************* ## 1 UDF/ Excel name: todec() - Convert various imperial format feet-inches to decimal ## with optional argument convert to-factor [cFactor] ## Rev. 1.3 - 3/5/2025 ## Update to correct convert number with scientific notation (ex: -3.94571E-06...) ##************************************************************************************* =LAMBDA(imperial,[to_cFactor], LET(cf,IF(ISOMITTED(to_cFactor),1,to_cFactor), si,IF(LEFT(imperial,1)="-",-1,1), IF(ISBLANK(imperial),"n/a", IF(ISNUMBER(imperial),imperial*cf, LET(ft,IFERROR(ABS(VALUE(LEFT(imperial,(FIND("'",imperial)-1)))),0), in,TRIM(SUBSTITUTE(SUBSTITUTE(IFERROR(RIGHT(imperial,LEN(imperial)-FIND("'",imperial)),imperial),"-",""),"""","")), IFERROR(si*(ft*12+VALUE(IF(ISERR(AND(FIND(" ",in),FIND("/",in))),IFERROR(VALUE("0 "&in),in),in)))*cf,"n/a") ) ) ) ) ) ##********************************************************************************************* ## 2 UDF/ Excel name: toimpa() - Convert decimal to imperial feet-inches, architectural format ## with optional arguments convert from-factor [from_cFactor], and round-off [rdOff] ## Notes: Convert from-factor only apply for cell contains number or valuated as a number by ## Excel VALUE() function, for others form of imperial feet-inches these will not ## apply for the conversion such as 1'-2", 3', 16", 1/4" etc. ## Argument round-off [rdOff], number such as 2, 4, 8, 16, 32, 64, 128... equaling ## 1/2, 1/4, 1/8, 1/16, 1/32, 1/64, 128..., also argument round-off can be enter in ## the form .5, .25, .3125, .0625... or 1/2, 1/4, 1/16...etc. for number less than 1. ## By default, if there no specify, the round-off number will be 64 (aka 1/64). ## This function uses todec() as sub-function. ## Rev. 1.3 - 3/5/2025 ##********************************************************************************************* =LAMBDA(varNum,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), de,IF(ISBLANK(varNum),"n/a",IF(ISNUMBER(VALUE(varNum)),VALUE(varNum)/cf,IFERROR(todec(varNum),VALUE(varNum)))), MAP(de,BYROW(de,LAMBDA(r,rd)),LAMBDA(d,r, IFERROR(IF(AND(MROUND(d,SIGN(d)*r)<12,MROUND(d,SIGN(d)*r)>-12),(TEXT(MROUND(d,SIGN(d)*r),"# #/####")&""""), (ROUNDDOWN(MROUND(d,SIGN(d)*r)/12,0)&"'-"&TEXT(MOD(MROUND(ABS(d),r),12),"0 #/####")&"""")), d) ) ) ) ) ##********************************************************************************************* ## 3 UDF/ Excel name: toimpe() - Convert decimal to imperial feet-inches, engineering format ## with optional arguments convert from-factor [from_cFactor], and round-off [rdOff] ## Notes: Convert from-factor only apply for cell contains number or valuated as a number by ## Excel VALUE() function, for others form of imperial feet-inches these will not ## apply for the conversion such as 1'-2", 3', 16", 1/4" etc. ## Argument round-off [rdOff], number such as 2, 4, 8, 16, 32, 64, 128... equaling ## 1/2, 1/4, 1/8, 1/16, 1/32, 1/64, 128..., also argument round-off can be enter in ## the form .5, .25, .3125, .0625... or 1/2, 1/4, 1/16...etc. for number less than 1. ## By default, if there no specify, the round-off number will be 64 (aka 1/64). ## This function uses todec() as sub-function. ## Rev. 1.3 - 3/5/2025 ##********************************************************************************************* =LAMBDA(varNum,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), de,IF(ISBLANK(varNum),"n/a",IF(ISNUMBER(VALUE(varNum)),VALUE(varNum)/cf,IFERROR(todec(varNum),VALUE(varNum)))), MAP(de,BYROW(de,LAMBDA(r,rd)),LAMBDA(d,r, IFERROR(IF(AND(MROUND(d,SIGN(d)*r)<12,MROUND(d,SIGN(d)*r)>-12),MROUND(d,SIGN(d)*r)&"""", (ROUNDDOWN(MROUND(d,SIGN(d)*r)/12,0)&"'-"&MOD(MROUND(ABS(d),r),12)&"""")), d) ) ) ) ) ##********************************************************************************** ## 4 UDF/ Excel name: sumtodec() - Similar to Excel SUM(), optional argument convert ## to-factor [to_cFactor] ## Note: This function uses todec() as sub-function. ## Rev. 1.3 - 3/5/2025 ##********************************************************************************** =LAMBDA(varNum,[to_cFactor], LET(cf,IF(ISOMITTED(to_cFactor),1,to_cFactor), de,IF(ISBLANK(varNum),"n/a",IFERROR(todec(varNum),VALUE(varNum))), SUM(de)*cf ) ) ##******************************************************************************* ## 5 UDF/ Excel name: sumtoimpa() - Similar to Excel SUM() function with optional ## argument convert from-factor [from_cFactor], imperial architectural format. ## Note: This function uses todec() & toimpa() as sub-functions ## Rev. 1.3 - 3/5/2025 ##******************************************************************************* =LAMBDA(imperials,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpa(SUM(de),1,rd) ) ) ##******************************************************************************* ## 6 UDF/ Excel name: sumtoimpe() - Similar to Excel SUM() function with optional ## argument convert from-factor [from_cFactor], imperial engineering format. ## Note: This function uses todec() & toimpe() as sub-functions. ## Rev. 1.3 - 3/5/2025 ##******************************************************************************* =LAMBDA(imperials,[from_cFactor],[rdOff], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), rd,IF(ISOMITTED(rdOff),1/64,IF(rdOff>1,1/rdOff,rdOff)), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpe(SUM(de),1,rd) ) ) ##************************************************************************************* ## 7 UDF/ Excel name: averageimpa() - Similar to Excel AVERAGE() function with optional ## argument convert from-factor [from_cFactor], imperial architectural format. ## Note: This function uses todec() & toimpa() as sub-functions. ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##************************************************************************************* =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpa(AVERAGE(de)) ) ) ##************************************************************************************* ## 8 UDF/ Excel name: averageimpe() - Similar to Excel AVERAGE() function with optional ## argument convert from-factor [from_cFactor], imperial engineering format. ## Note: This function uses todec() & toimpe() as sub-functions. ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##************************************************************************************* =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpe(AVERAGE(de)) ) ) ##****************************************************************************** ## 9 UDF/ Excel name: minimpa() - Similar to Excel MIN() function with optional ## argument convert from-factor [from_cFactor], imperial architectural format. ## Note: This function uses todec() & toimpa() as sub-functions. ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##****************************************************************************** =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpa(MIN(de)) ) ) ##****************************************************************************** ## 10 UDF/ Excel name: minimpe() - Similar to Excel MIN() function with optional ## argument convert from-factor [from_cFactor], imperial engineering format. ## Note: This function uses todec() & toimpe() as sub-functions. ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##****************************************************************************** =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpe(MIN(de)) ) ) ##******************************************************************************* ## 11 UDF/ Excel name: maximpa() - Similar to Excel MAX() function with optional ## argument convert from-factor [from_cFactor], imperial architectural format. ## Note: This function uses todec() & toimpa() as sub-functions ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##******************************************************************************* =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpa(MAX(de)) ) ) ##****************************************************************************** ## 12 UDF/ Excel name: maximpe() - Similar to Excel MAX() function with optional ## argument convert from-factor [from_cFactor], imperial engineering format. ## Note: This function uses todec() & toimpe() as sub-functions ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##****************************************************************************** =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpe(MAX(de)) ) ) ##***************************************************************************************** ## 13 UDF/ Excel name: rangeimpa() - Similar to Excel MAX() - MIN() functions with optional ## argument convert from-factor [from_cFactor], imperial architectural format. ## Note: This function uses todec() & toimpa() as sub-functions ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##***************************************************************************************** =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpa(MAX(de)-MIN(de)) ) ) ##***************************************************************************************** ## 14 UDF/ Excel name: rangeimpe() - Similar to Excel MAX() - MIN() functions with optional ## argument convert from-factor [from_cFactor], result imperial engineering format. ## Note: This function uses todec() & toimpe() as sub-functions. ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##***************************************************************************************** =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpe(MAX(de)-MIN(de)) ) ) ##********************************************************************************** ## 15 UDF/ Excel name: largeimpa() - Similar to Excel LARGE() function with optional ## argument convert from-factor [from_cFactor], imperial architectural format. ## Note: This function uses todec() & toimpa() as sub-functions ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##********************************************************************************** =LAMBDA(imperials,ranK,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpa(LARGE(de,ranK)) ) ) ##********************************************************************************** ## 16 UDF/ Excel name: largeimpe() - Similar to Excel LARGE() function with optional ## argument convert from-factor [from_cFactor], imperial engineering format. ## Note: This function uses todec() & toimpe() as sub-functions ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##********************************************************************************** =LAMBDA(imperials,ranK,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpe(LARGE(de,ranK)) ) ) ##********************************************************************************** ## 17 UDF/ Excel name: smallimpa() - Similar to Excel SMALL() function with optional ## argument convert from-factor [from_cFactor], imperial architectural format. ## Note: This function will use todec() & toimpa() as sub-functions ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##********************************************************************************** =LAMBDA(imperials,ranK,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpa(SMALL(de,ranK)) ) ) ##********************************************************************************** ## 18 UDF/ Excel name: smallimpe() - Similar to Excel SMALL() function with optional ## argument convert from-factor [from_cFactor], imperial engineering format. ## Note: This function will use todec() & toimpe() as sub-functions ## Rev. 1.3 - 3/5/2025 ## Update, added convert from-factor [from_cFactor] ##********************************************************************************** =LAMBDA(imperials,ranK,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), toimpe(SMALL(de,ranK)) ) ) ##******************************************************************************************* ## 19 UDF/ Excel name: fis2dec() - Convert fis (FFIISS format) to decimal inch (default) with ## optional argument convert to-factor [to_cFactor] ## Notes: fis format (Feet-Inches-Sixteenths) in form of: FFIISS, IISS, SS, FF., FF.## ## Error format checking (#VALUE!), except empty cell marks as text string "n/a": ## SS - Sixteenths (last 2 digits): must be less than 16 ## II - Inches (last 4 digits minus last 2 digits): must be less than 12 ## FF. - Note of "." (dot), example 23. would be equaling 23' or 230000 ## FF.## - Note of ".##" (dot and numbers after, would be equaling to decimal feet) ## Rev. 1.3 - 3/7/2025 ##******************************************************************************************* =LAMBDA(fis,[to_cFactor], LET(cf,IF(ISOMITTED(to_cFactor),1,to_cFactor), si,IF(LEFT(fis,1)="-",-1,1), IF(ISBLANK(fis),"n/a", IF(fis="n/a","n/a", IF(NOT(ISERROR(FIND(".",fis))),VALUE(fis)*12*cf, LET(d,ABS(fis), f,IF(LEN(d)>4,LEFT(d,LEN(d)-4),0)*12, i,IF(VALUE(LEFT(TEXT(RIGHT(d,4),"0000"),2))<12,VALUE(LEFT(TEXT(RIGHT(d,4),"0000"),2)),1/""), s,IF(VALUE(RIGHT(d,2))<16,VALUE(RIGHT(d,2))/16,1/""), (f+i+s)*cf*si ) ) ) ) ) ) ##************************************************************************ ## 20 UDF/ Excel name: dec2fis() - Convert decimal to fis with optional ## argument convert from-factor [from_cFactor], imperial FFIISS format. ## Note: Since FFIISS format, decimal value will be round-off to 1/16 ## Rev. 1.3 - 3/7/2025 ##************************************************************************ =LAMBDA(varNum,[from_cFactor], IF(ISBLANK(varNum),"n/a", LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), si,IF(LEFT(varNum,1)="-",-1,1), de,MROUND(varNum/cf,si*1/16), IFERROR(VALUE(ROUNDDOWN(ABS(de)/12,0)&TEXT(INT(MOD(ABS(de),12)),"00")&TEXT(MOD(MOD(ABS(de),12),1)*16,"00"))*si,"n/a") ) ) ) ##********************************************************************************* ## 21 UDF/ Excel name: imp2fis() - Convert imperial to fis, imperial FFIISS format. ## Note: This imp2fis() function use dec2fis() & todec() as sub-functions. ## Rev. 1.3 - 3/8/2025 ##********************************************************************************* =LAMBDA(imperials, LET(si,IF(LEFT(imperials,1)="-",-1,1), dec2fis(MROUND(todec(imperials),si*1/16)) ) ) ##******************************************************************************* ## 22 UDF/ Excel name: fis2impa() - Convert fis to imperial architectural format. ## Note: This function uses fis2dec() & toimpa() as sub-functions ## Rev. 1.3 - 3/8/2025 ##******************************************************************************* =LAMBDA(fis, LET(\0,"Note: Since function toimpa() written when error occur always return string [n/a] for Excel SUM() to work", \1,"now in this function explicitly force it return error #VALUE! for FFIISS format checking and validation", IF(fis="n/a","n/a",IF(ISERROR(fis2dec(fis)),1/"",toimpa(fis2dec(fis)))) ) ) ##******************************************************************************* ## 23 UDF/ Excel name: fis2impe() - Convert fis to imperial architectural format. ## Note: This function uses fis2dec() & toimpe() as sub-functions ## Rev. 1.3 - 3/8/2025 ##******************************************************************************* =LAMBDA(fis, LET(\0,"Note: Since function toimpe() written when error occur always return string [n/a] for Excel SUM() to work", \1,"now in this function explicitly force it return error #VALUE! for FFIISS format checking and validation", IF(fis="n/a","n/a",IF(ISERROR(fis2dec(fis)),1/"",toimpe(fis2dec(fis)))) ) ) ##************************************************************************************* ## 24 UDF/ Excel name: sumfis2dec() - Similar to Excel SUM(), decimal inches (default), ## with optional arguments convert to-factor [to_cFactor]. ## Note: This function uses fis2dec() as sub-function. ## Rev. 1.3 - 3/8/2025 ##************************************************************************************* =LAMBDA(varFis,[to_cFactor], LET(cf,IF(ISOMITTED(to_cFactor),1,to_cFactor), de,IF(ISBLANK(varFis),"n/a",IF(varFis="n/a","n/a",IFERROR(fis2dec(varFis),1/""))), SUM(de)*cf ) ) ##******************************************************************************************* ## 25 UDF/ Excel name: sumfis2impa() - Similar to Excel SUM(), imperial architectural format. ## Note: This function uses fis2dec() & toimpa() as sub-functions. ## Rev. 1.3 - 3/8/2025 ##******************************************************************************************* =LAMBDA(varFis, LET(de,IF(ISBLANK(varFis),"n/a",IF(varFis="n/a","n/a",IFERROR(fis2dec(varFis),1/""))), IF(ISERROR(SUM(de)),1/"",toimpa(SUM(de))) ) ) ##***************************************************************************************** ## 26 UDF/ Excel name: sumfis2impe() - Similar to Excel SUM(), imperial engineering format. ## Note: This function uses fis2dec() & toimpe() as sub-functions. ## Rev. 1.3 - 3/8/2025 ##***************************************************************************************** =LAMBDA(varFis, LET(de,IF(ISBLANK(varFis),"n/a",IF(varFis="n/a","n/a",IFERROR(fis2dec(varFis),1/""))), IF(ISERROR(SUM(de)),1/"",toimpe(SUM(de))) ) ) ##*************************************************************************************** ## 27 UDF/ Excel name: averagefis() - Similar to Excel AVERAGE(), imperial FFIISS format. ## Note: This function uses fis2dec() & dec2fis() as sub-functions. ## Rev. 1.3 - 3/8/2025 ##*************************************************************************************** =LAMBDA(fiss, LET(de,MAP(fiss,LAMBDA(i,IF(ISNUMBER(i),fis2dec(i),"n/a"))), IF(ISERROR(AVERAGE(de)),1/"",dec2fis(IFERROR(AVERAGE(de),"n/a"))) ) ) ##******************************************************************************* ## 28 UDF/ Excel name: minfis() - Similar to Excel MIN(), imperial FFIISS format. ## Note: This function uses dec2fis() as sub-function. ## Rev. 1.3 - 3/8/2025 ##******************************************************************************* =LAMBDA(fiss, LET(de,MAP(fiss,LAMBDA(i,IF(ISNUMBER(i),fis2dec(i),"n/a"))), \0,"Note use AVERAGE before MIN intentionally to throw error if fis format is incorrect!", IF(ISERROR(AVERAGE(de)),1/"",dec2fis(IFERROR(MIN(de),"n/a"))) ) ) ##******************************************************************************* ## 29 UDF/ Excel name: maxfis() - Similar to Excel MAX(), imperial FFIISS format. ## Note: This function uses dec2fis() as sub-function. ## Rev. 1.3 - 3/8/2025 ##******************************************************************************* =LAMBDA(fiss, LET(de,MAP(fiss,LAMBDA(i,IF(ISNUMBER(i),fis2dec(i),"n/a"))), \0,"Note use AVERAGE before MAX intentionally to throw error if fis format is incorrect!", IF(ISERROR(AVERAGE(de)),1/"",dec2fis(IFERROR(MAX(de),"n/a"))) ) ) ##********************************************************************************** ## 30 UDF/ Excel name: rangefis() - Excel MAX() minus MIN(), imperial FFIISS format. ## Note: This function uses dec2fis() as sub-function. ## Rev. 1.3 - 3/8/2025 ##********************************************************************************** =LAMBDA(fiss, LET(de,MAP(fiss,LAMBDA(i,IF(ISNUMBER(i),fis2dec(i),"n/a"))), \0,"Note use AVERAGE before (MAX - MIN) intentionally to throw error if fis format is incorrect!", IF(ISERROR(AVERAGE(de)),1/"",dec2fis(IFERROR(MAX(de)-MIN(de),"n/a"))) ) ) ##*********************************************************************************** ## 31 UDF/ Excel name: smallfis() - Similar to Excel SMALL(), imperial FFIISS format. ## Note: This function uses dec2fis() as sub-function. ## Rev. 1.3 - 3/8/2025 ##*********************************************************************************** =LAMBDA(fiss,ranK, LET(de,MAP(fiss,LAMBDA(i,IF(ISNUMBER(i),fis2dec(i),"n/a"))), IF(ISERROR(SMALL(de,ranK)),1/"",dec2fis(IFERROR(SMALL(de,ranK),"n/a"))) ) ) ##*********************************************************************************** ## 32 UDF/ Excel name: largefis() - Similar to Excel LARGE(), imperial FFIISS format. ## Note: This function uses dec2fis() as sub-function. ## Rev. 1.3 - 3/8/2025 ##*********************************************************************************** =LAMBDA(fiss,ranK, LET(de,MAP(fiss,LAMBDA(i,IF(ISNUMBER(i),fis2dec(i),"n/a"))), IF(ISERROR(LARGE(de,ranK)),1/"",dec2fis(IFERROR(LARGE(de,ranK),"n/a"))) ) ) ##******************************************************************************* ## 33 UDF/ Excel name: sumfis() - Similar to Excel SUM(), imperial FFIISS format. ## Note: This function uses fis2dec() & dec2fis() as sub-functions. ## Rev. 1.3 - 3/8/2025 ##******************************************************************************* =LAMBDA(varFis, LET(de,IF(ISBLANK(varFis),"n/a",IF(varFis="n/a","n/a",IFERROR(fis2dec(varFis),1/""))), IF(ISERROR(SUM(de)),1/"",dec2fis(SUM(de)))) ) ##************************************************************************************************** ## 34 UDF/ Excel name: sumimp2fis() - Similar to Excel SUM(), imperial FFIISS format. with optional ## argument convert from-factor [from_cFactor], see note convert from-factor toimpa() for details ## Note: This function uses todec() and dec2fis() as sub-functions. ## Rev. 1.3 - 3/15/2025 ##************************************************************************************************** =LAMBDA(imperials,[from_cFactor], LET(cf,IF(ISOMITTED(from_cFactor),1,from_cFactor), de,MAP(imperials,LAMBDA(i,IF(ISNUMBER(i),i/cf,todec(i)))), dec2fis(SUM(de)) ) ) ## End of Excel UDF # Screenshot of Excel 365 xlsx Test Debug UDF 20250315.xlsxFetching info... Excel UDFs Rev1.3-20250315.txtFetching info... Edited Monday at 02:50 AM by phuynh Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.