Leaderboard

@jim78b Then you should've just said that. Anyway - I think the problem is that you can't have a multi-line string like in your action tile statement, when there are parenthesis and other embedded code. Do this instead: ;; Action for the "Highlight Selection" button (action_tile "highlight" (strcat "(progn" " (setq selected_index (atoi (get_tile \"block_tree\")))" " (setq selected_block (nth selected_index *block_name_list*))" " (princ (strcat \"\nHighlighting all instances of: \" selected_block))" " (sssetfirst nil nil) ; Clear any previous selection grips" " (setq ss (ssget \"_X\" (list (cons 0 \"INSERT\") (cons 2 selected_block) (cons 410 (getvar 'ctab)))))" " (if ss (sssetfirst nil ss)) ; Highlight all found instances in current space" ")" ) )

I wasn't talking to you. I was asking the OP, if not mentioned by name or quoted it is presumed all over the internet the question is for the OP. But, I mentioned, no LISP needed if you are manually making a polyline, -Hatch with "draW boundary" option. I see no need for a LISP and you can also leave a polyline if needed.

...Continue from previous post, experiment with imperial multiplication. Phh ##******************************************************************************************** ## 41 UDF/ Excel name: impMul() - Imperial multiply (imperial * imperial), with optional ## arguments round off [rd_Off] and suppress trailing [suppress_trailing_label]. ## ## Note: This function uses todec() and toimpa() as sub-functions. ## ## Notes with 2 input parameters - scenarios: ## ## [decimal] x [decimal] = return decimal value (just like normal multiplication) ## [decimal] x [imperial feet-inches (or vice versa)] = return (linear) feet-inches ## [imperial feet-inches] x [imperial feet-inches] = return decimal ft² (if less than 1 ft² ## return decimal in²) ## ## Optional round off digits, applied for decimal ft² or decimal in² only. ## Optional suppress trailing (any Alphanumeric) = suppress label ft² or in² ## ## Rev. 1.0 - 7/23/2025 ##******************************************************************************************** =LAMBDA(varImp1,varImp2,[rd_Off],[suppress_trailing_label], LET(rd,IF(ISOMITTED(rd_Off),4,INT(rd_Off)), tr,IF(ISOMITTED(suppress_trailing_label),1,0), IF(AND(ISNUMBER(varImp1),ISNUMBER(varImp2)), varImp1*varImp2, IF(AND(ISTEXT(varImp1),ISTEXT(varImp2)), IF(ABS(todec(varImp1)*todec(varImp2))>=144, IF(tr=0,ROUND((todec(varImp1)*todec(varImp2))/144,rd),ROUND((todec(varImp1)*todec(varImp2))/144,rd)&" ft²"), IF(tr=0,ROUND((todec(varImp1)*todec(varImp2)),rd),ROUND((todec(varImp1)*todec(varImp2)),rd)&" in²")), toimpa(todec(varImp1)*todec(varImp2)) ) ) ) )

How are you creating the polyline? You can use -Hatch with the "draW boundary" option, just a few clicks for a solid hatch on current layer.

...Continue from previous post, with few useful functions when I do compute from field measurements. Phh ##*********************************************************************************************** ## 35 UDF/ Excel name: diagLen() - Find diagonal (hypotenus) length of right angel, with known ## run (base) and rise (height), optional arguments; 1 return angle (at start of run) and 2 ## (the other angle). ## Note: This function uses todec() & toimpa() as sub-functions. ## Rev. 1.4 - 6/20/2025 ##*********************************************************************************************** =LAMBDA(run,rise,[angle1_or_angle2], LET(flag,IF(AND(ISNUMBER(run),ISNUMBER(rise)),TRUE,FALSE), angt,IF(ISOMITTED(angle1_or_angle2),0,IF(OR(angle1_or_angle2<1,angle1_or_angle2>2),0,INT(angle1_or_angle2))), diag,SQRT(todec(run)^2+todec(rise)^2), an_1,DEGREES(ASIN(todec(rise)/diag)), an_2,DEGREES(ASIN(todec(run)/diag)), SWITCH(TRUE,angt=0,IF(flag=FALSE,toimpa(diag),diag),angt=1,an_1,angt=2,an_2,diag) ) ) 6/20/2025, correction argument label "angl1_or_angle2" on 3rd line, (sorry, my bad!) ##******************************************************************************************* ## 36 UDF/ Excel name: arclen() - Calculate arc length with known chord length & chord height ## optional arguments; 1 return radius, 2 the angle. ## Note: This function uses todec() & toimpa() as sub-functions. ## Rev. 1.3 - 5/5/2025 ##******************************************************************************************* =LAMBDA(chordL,chordH,[Optional_return_1Radius_or_2Angle], LET(flag,IF(AND(ISNUMBER(chordL),ISNUMBER(chordH)),TRUE,FALSE), cL,todec(chordL), cH,todec(chordH), arg,IF(ISOMITTED(Optional_return_1Radius_or_2Angle),0,INT(Optional_return_1Radius_or_2Angle)), rad,(cH/2)+(cL^2/(8*cH)), ang,IF(cH<(cL/2),2*(ASIN(cL/(2*rad))),2*PI()-(2*(ASIN(cL/(2*rad))))), arc,rad*ang, SWITCH(TRUE,arg=0,IF(flag=FALSE,toimpa(arc),arc),arg=1,IF(flag=FALSE,toimpa(rad),rad),arg=2,DEGREES(ang),IF(flag=FALSE,toimpa(arc),arc)) ) ) ##************************************************************************************************** ## 37 UDF/ Excel name: ang3len() - Find angle with know 3 segment lengths, return angle in degrees ## between 1st & 2nd segments as default, the sequence input order is importance, optional others ## angles argument 2, 3, 4, 5, 6, 7. Value of one segment must be less than the sum of others two. ## Note: This function uses todec() as sub-function. ## Rev. 1.4 - 6/20/2025 ##************************************************************************************************** =LAMBDA(lengthA,lengthB,lengthC,[arg_Num], LET(aNo,IF(ISOMITTED(arg_Num),1,IF(AND(arg_Num<4,arg_Num<1),1,INT(arg_Num))), lnA,todec(lengthA), lnB,todec(lengthB), lnC,todec(lengthC), Sper,(lnA+lnB+lnC)/2, Area,ROUND(SQRT(Sper*(Sper-lnA)*(Sper-lnB)*(Sper-lnC))/144,4), IF(AND(SUM(lnA,lnB)>lnC,SUM(lnB,lnC)>lnA,SUM(lnC,lnA)>lnB), SWITCH(aNo,1,DEGREES(ACOS((lnA^2+lnB^2-lnC^2)/(2*lnA*lnB))), 2,DEGREES(ACOS((lnB^2+lnC^2-lnA^2)/(2*lnB*lnC))), 3,DEGREES(ACOS((lnC^2+lnA^2-lnB^2)/(2*lnC*lnA))), 4,180-(DEGREES(ACOS((lnA^2+lnB^2-lnC^2)/(2*lnA*lnB)))), 5,180-(DEGREES(ACOS((lnB^2+lnC^2-lnA^2)/(2*lnB*lnC)))), 6,180-(DEGREES(ACOS((lnC^2+lnA^2-lnB^2)/(2*lnC*lnA)))), 7,Area&" ft²"), "Input error!, value of one segment must be less than the sum of others two!" ) ) ) 6/20/2025, correction, added argument 7 for compute area sq. ft. (sorry my bad!)

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.xlsx Excel UDFs Rev1.3-20250315.txt

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))) )