Stairway to ... LAMBDA?
I use Excel for everything.
I need some stairs from my yard to the forest floor below. It doesn't need to be fancy because almost no one will see it.
Before building anything, we need to take measurements. It is just 5 vertical feet (And there is the LAMBDA opportunity - I'm using feet and inches.). Stairs should have treads between 10 and 11 inches and risers that are about 7 1/2 inches. I will be constructing this from pressure treated lumber with premade concrete footers.
The next task is to visualize it. I used the free Sketchup program to model it.
Excel time!
Raw Materials
The dimension of the risers require 2x8s, which are actually 1.5 inches x 7.25 inches. The treads require 2x12s (1.5" x 11.25"). The sides require 2x8s and the posts require 4x4s. I don't want the wood in direct contact with the ground so I will use 11"x11" premade concrete footers. My car requires none of these lumber pieces exceed 8 feet.
Assembly List
With my Raw Materials List, I can then create my Assembly List. The inputs are the assembly name, how long that assembly is, how many I need of that assembly, and which raw material I will use. From those inputs, the table calculates how many of each raw material I must buy and what the total damage (cost) will be (There is a another table, not shown, which uses dynamic array formulas to combine materials into a shopping list).
The formulas are shown below the Assembly List. Notice that Act.Qty and Mat.Qty reference an odd function: ToInches(). ToInches is a 5g function I created for this project because Excel just doesn't like working with feet and inches. I intend to create a library for builders so they can estimate projects easily in Excel.
Before I created this function, I looked for formulas others had already made. Why "reinvent the wheel?" I found one that looked promising. Here is that formula:
Looks pretty good!
The importance of testing
I converted their formula to a LAMBDA and tested it. For their test cases, it worked fine. But for our edge tests, it failed.
领英推è
Excel will very often convert fractions that are by themselves to dates. The formula I found did not test for that. When entering dimensions in feet and inches, we sometimes only enter inches. The formula I found did not handle that scenario either.
A new formula, a far more complex formula is needed (I'm hopeful Bo Rydobon ???? , Diarmuid Early , Peter Bartholomew , Owen Price , Erik Oehm , Sergei Baklan , ???? Taeyong Shin , or someone else I should have mentioned will show us a much simpler way) . Here is my solution:
This LAMBDA has some advanced (to me) features. The first is it contains what I would call, two sub functions. I believe the correct term is a "thunk". I like sub functions better. It just makes more sense to me.
NOTE! The order in which we define things is critical in LAMBDA. In most programing languages, we would define sub routines below our main routine. We cannot do that with LAMBDA. We MUST define what will be referenced before we reference it.
The two sub functions are fnFraction and fnOneNum. I created these as sub functions because I could code them once and use them twice.
In fnFraction I incorporated one of Excel's new REGEX functions: REGEXTEST(). I use REGEXTEST() to determine if the text string contains a / which would indicate the string contains a fraction (as opposed to a decimal). We could use FIND() to do the same thing, but REGEXTEST() is simpler.
Hard core coders will notice that I sprinkled in a bit of 'hungarian notation.' Some coders love it. Some hate it. For this situation, it made sense to me to use sFeet to indicate the formula step produces a text string, and dFeet for decimal numbers.
In FnOneNum (combine whole number and fraction into one decimal number) I call fnFraction twice. This is because it is possible for someone to enter a feet as 1 1/2' as well as inches as 3 7/8". As you can see the feet portion of the measure can have a fraction and so can the inches. So we must split measures into feet and inches and assume we might have to convert either side, or both sides from fractions to decimals.
Moving to the main routine, we see a note about REGEXREPLACE(). When I first started this project I thought it would make a good opportunity to explain a bit about the REGEX functions. I really like REGEX but there is no sense in making things more complicated than they are. I tried using REGEXREPLACE() but when that function doesn't find anything to replace, it throws an #N/A! error. SUBSTITUTE does not. SUBSTITUTE is simpler so we will have to wait for another day to discuss REGEXREPLACE().
The rest of the routine is unremarkable so, let's not add any more remarks about it except to say, I now have an easy way to work with feet and inches in Excel that doesn't require VBA.
After creating this LAMBDA I ran it through my BXL Test add-in. My first half dozen attempts failed before I finally got it right (cross fingers) and it passed a thousand tests. If you create LAMBDAs, I highly encourage you to use this add-in. It's free. Here is more information on it:
Users Guide (with links to add-in): https://www.dropbox.com/scl/fi/azs8cdpdoskwli61s69no/BXL-Tests.pdf?rlkey=ruxpsm4fxlx6fmg5kilg6swyf&dl=0
Website discussing testing: https://sites.google.com/site/beyondexcel/home/5g-modeling/5g-course/5g-testing-components
Excel and VBA Specialist @ jkp-ads.com, Microsoft MVP
8 个月Hah, feet, inches. Archimedes units, we used to call those over here :-P Rather than using thunks, doesn't it make more sense to have fnFraction and fnOneNum as separate lambdas? And create a name space for the threesome? My first thought was Zeppelin. That way we get Zeppelin.ToInches, Zeppelin.fnFraction, and Zeppelin.fnOneNum. But perhaps a more serious UnitConv is better :-)
Excel Wizard
8 个月This seems to work with these samples =LAMBDA(fi,LET(i,TRIM(SUBSTITUTE(TEXTAFTER(fi,{"'","-"},-1,,,fi),"""",)), IF(COUNT(FIND({" ","/"},i))=1,"0 "&i,IFERROR(--i,))+TEXTBEFORE(fi,"'",,,,0)*12))(A2) or =LAMBDA(i,LET(j,BYROW(EXPAND(TEXTSPLIT(i,"/",{"'",""""," ","-"},1,,1),,2,1)^{1,-1},PRODUCT), SUM(j*EXPAND(IF(COUNT(FIND("'",i)),12,1),ROWS(j),,1))))(A2)
Founder at Early Days Consulting, Partner at the Golden Company
8 个月I like your LAMBDA, although I think you're opening a can of worms by trying to cater for all the bad ways people might enter data (e.g. '1 inch' or 'four ft' are both easily human-readable but would break the function). If I were building something like this for others to use, I think I'd have some validation on the data entry and a simpler conversion function. That might actually be a good use case for REGEX - in a data validation formula to check the cell only contains input that the function will be able to understand (preferably accompanied by a note / warning / pop-up if it's not). Good luck with the project! : )
Excel Robot
8 个月I was going to suggest upgrading to an elevator to Lambda, or at least an escalator, but read the article and you are actually calculating the project cost of an ACTUAL stairway! Very cool real-life example of using lambdas! I like how you solicited our feedback on simplifying your Lambda logic... because you KNEW we would, haha! Here's the little nugget of obscure Excel nerd trivia that I'll contribute: Excel actually has a CONVERT function that converts between a ton of different unit types, including Feet and Inches, so I think you COULD have used CONVERT(fnOneNum(Measure),"ft","in") to simplify things a bit. Not that you even need to bother, more just sharing the observation!