I wouldn’t normally publish a post on such an esoteric topic as this. However, since the idea for it came as a result of a challenge posed by the venerable David Hager, I felt that I could not resist.

And that challenge was as follows: given a list of chemical elements and their respective atomic weights, a formula to determine the weight for a given molecule.

It goes without saying that there are numerous quick and easy online applications which will perform such a calculation. Nevertheless, and however unlikely it may seem, there is still a small probability that this post will reach one or more of the tiny minority who have a practical need for such calculations to be performed within Excel (and, in addition, perhaps without recourse to VBA).

As an example of what is required, given aluminium sulphate (or sulfate if you prefer), Al2(SO4)3, we would expect a result of 342.15 (to 2 d.p.), since one molecule of this compound comprises, in total: 2 atoms of aluminium, 3 of sulphur and 12 of oxygen, and, using the table provided, we can sum up the relevant amounts to arrive at that value.

Assuming that the single-column ranges containing the elements and weights are defined as Sym and Wgt respectively (you can download the workbook here), and that we further define, relative to row 2, Arr as:

=ROW(INDEX($A:$A,1):INDEX($A:$A,LEN($A2)))

the required array formula** in B2 is then:

=SUM(SUMIF(Sym,MID(A2,Arr,MMULT(0+(ABS({77.5,107.5}-CODE(MID(MID(A2&"ζ",Arr,2),{1,2},1)))<13),{1;1})),Wgt)*IFERROR(0+MID(A2,MMULT(0+(ABS({77.5,107.5}-CODE(MID(MID(A2&"ζ",Arr,2),{1,2},1)))<13),{1;1})+Arr,MMULT(1-ISERR(0+MID(A2,Arr+MMULT(0+(ABS({77.5,107.5}-CODE(MID(MID(A2&"ζ",Arr,2),{1,2},1)))<13),{1;1}),{1,2})),{1;1})),1)*IFERROR(0+MID(A2,IF(ISNUMBER(MATCH(Arr,IF(ISODD(MATCH(Arr,MODE.MULT(IF(MID(A2,Arr,1)={"(",")"},Arr+{1,0}),Arr))),Arr),0)),FIND(")",A2,Arr))+1,MMULT(1-ISERR(0+MID(A2,IF(ISNUMBER(MATCH(Arr,IF(ISODD(MATCH(Arr,MODE.MULT(IF(MID(A2,Arr,1)={"(",")"},Arr+{1,0}),Arr))),Arr),0)),FIND(")",A2,Arr))+1,{1,2})),{1;1})),1))

Note that this set-up is limited to compounds whose largest single numeric does not exceed 99. So, for example, it will fail on something like:

C60H120

for example, (though:

(C30H60)2

would be perfectly fine).

Due to the length and complexity of the above, I hope readers will not mind too much if I pass on an explanation! However, I would be more than happy to respond to any queries as to how it works and/or suggestions for improvement.

Thanks again to David for being the inspiration for this one. Another post to follow shortly. Watch this space!