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!

i tried to get the result from http://environmentalchemistry.com/yogi/reference/molar.html

your formula gives slighlty different result. so, not sure which one is the correct. the formula or the calculator in the referred website.

@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

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!

Thanks, David!

How are you seeing 4 atoms of sulfur instead of 3 in that chemical formula?

@David NApologies and many thanks for pointing out the oversight. Have amended in the text.

Regards

Hello XOR LX,

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

Hahaha! You have a wonderful way with words, James! 🙂

Cheers!

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

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

elSym

symLen

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).