Molecular Weights 8

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!

8 comments

  1. @Flora

    Can you give me an example of a compound for which that site gives a different result? For the example I gave, Al2(SO4)3, that site also gives a result of 342.15 (to 2 d.p.).

    Regards

  2. To be clear, my intention was less of a challenge and more of a realization that the formula techniques you have developed would make you capable of creating this formula. Congratulations!

  3. Hello XOR LX,

    My molecules have gathered together to catalyze an atomic “Congratulations” … !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s