Forum Discussion
End of the Year Formula Challenge - Create the Periodic Table
The challenge is to create an array of the Period Table:
You're provided with a reference of elements (The snip below is a slice of the provided reference):
The rules: formulas only, no fill handle, and you can't change the provided reference in the sheet (Must be done through formulas).
You may skip to the attachment to dig in or read my solution notes below.
Here's my solution:
The way I approached this challenge was trying to make use of half the 'coordinates' provided with the Group column. I first used REDUCE to stack elements but due to the anomalies in the listing and group numbers (The three-digit group numbers), I could only get so far (about 75% of elements stacked and some elements in the wrong slots). I think the best route to a solution is to 'fix' the group column and to assign a 'period' to each element. MAKEARRAY could then deliver the solution.
Named ranges for Atomic, Group, and Symbol
Lambda 'Obtain Period'
=LAMBDA(a,g,LET(
counter, COUNTIF(TAKE(group, a), 18),
IF(
AND(a >= 57, a <= 70),
8,
IF(
AND(a >= 89, a <= 102),
9,
IF(counter = 0, 1, IF(AND(counter > 0, g = 18), counter, counter + 1))
)
)
))Period - obtains the 'correct' period number for each element.
=MAP(atomic, group, ObtainPeriod)MGroup - reassigns 'group' number where needed.
=MAP(
group,
atomic,
LAMBDA(g, a,
IF(XOR(a = {71, 103}), 3, IF(XOR(g = {101, 102}), COUNTIF(TAKE(group, a), g) + 3, g))
)
)
Coordinates Lambda - fetch atomic number and symbol
=LAMBDA(r,c,XLOOKUP(r & c, Period & mGroup, atomic & CHAR(10) & symbol, ""))
Sheet level formula:
=MAKEARRAY(9,18,Coordinates)
Open to suggestions, improvements, and/or shortcuts. Enjoy!
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).
