Molecular Weights 10

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!

10 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” … !!!

  4. Pingback: #Excel Super Links #1 – shared by David Hager | Excel For You

  5. An amazingly ingenious solution. I never can make my mind up whether to be in awe of such ‘too clever by half’ approaches or horrified by them. One idea that you might like to comment on is that of representing reusable fragments of formulas as Names. For example

    elWgt

    =SUMIF(Sym,elSym,Wgt)

    elSym

    =MID(molecule,Arr,symLen)

    symLen

    =MMULT(0+(ABS({77.5,107.5}-CODE(MID(MID(molecule&"ζ",Arr,2),{1,2},1)))<13),{1;1})

    The formula symLen to determine whether the Symbol name has one or two characters is still alarmingly devious but the use of a name encapsulates the complexity and leaves a meaningful expression that could be documented (under Name.Comments).

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