Forum Discussion
End of the Year Formula Challenge - Create the Periodic Table
Patrick2788 here is my entry:
=LET(pp, SCAN(0,group,LAMBDA(p,q, IF(q=1,p+1,p))),
p, IF(group>20,group-93,pp),
g, SCAN(0,group,LAMBDA(p,q, IF(q>20,p+1,q))),
MAKEARRAY(9,18,LAMBDA(r,c, XLOOKUP(r&";"&c, p&";"&g, atomic&CHAR(10)&symbol,""))))
the only caveat is that it make a chart more akin to this:
if the table had the groups from elements 57 and 89 be 101 and 102 respectively then my formula would make a table more like this:
but either way I like a solution with less customization since if you are applying that much customization why not just edit the table accordingly ... (i.e. make elements 57 & 89 in groups 101 & 102 and then elements 71 & 103 in group 3)
Thanks for sharing, Matt! Out solutions are similar in the use of MAKEARRAY to produce the array. We may differ in the logic used to 'fix' group/period, but ultimately this exercise seems to be about clearing the way for MAKEARRAY to deliver the solution (I left some of the non-essential information in the provided reference because I was half-expecting someone to know of a way to use that information to cut some corners.).
When I came up with this puzzle, I was intent on solving it with REDUCE without fixing group/period. The problems I ran into were the inconsistencies in the data, 'filling out' the group stacks, and sorting the atomic numbers before the symbol was added. Elegance goes out the window when one must sort the group 3 stack when the sequence of the last 3 elements is 103, 57, 89, for example.
Re: changing the groups in the data directly
Agreed that an easier way is to fix the data directly and then the solution becomes much cleaner. Many of the formula challenges I've posted don't appear to have practical applications on the surface. I like creating these challenges because it gives me an opportunity to sharpen the spear. I've used the REDUCE stacking technique a few times in the past month to deliver solutions for clients (One of which was told what she wanted could not be done with a formula nor vba).
