2

How to recognize (and SUM) also chemical formulas which have more occurrences of elements such as AsH2(C4H9) = where you have H on two places or in CH2COOH where actually only one of the "same" elements will be counted??

It is good explained on these threads below, but it isn't included when you have compounds with repetitive elements, where is necessary to SUM it properly, otherwise, one element is always omitted.

So those threads are only for counting elements and simply calculate of compounds, but not for compounds with brackets, and repetitive elements.

How to count up elements in excel

Calculating Molecular Weight Using Excel

And is it possible to do it without VBA coding?

This is coding that I have been using. 

 =E$2*MAX(IFERROR(IF(FIND(E$1&ROW($1:$99);$A3);ROW($1:$99);0);0);IFERROR(IF(FIND(E$1&CHAR(ROW($65:$90));$A3&"Z");1;0);0))

Example of Data

I included the data set example, in red you can see problematic actually. It is "matching" all the elements without brackets and this is not what I want. In column B is only sum of all the components from right side.

JvdV
  • 70,606
  • 8
  • 39
  • 70
MmVv
  • 553
  • 6
  • 22
  • 1
    Please include sample data with expected output – JvdV Jul 07 '19 at 09:18
  • If you use countif() to count the number of “O” (for Oxygen) then multiply by 16, but you need to trap “OO” as well... a good exercise... – Solar Mike Jul 07 '19 at 09:23
  • @JvdV i did include the picture of short example... – MmVv Jul 07 '19 at 09:42
  • @SolarMike yeah..sounds easy but aint :D – MmVv Jul 07 '19 at 11:20
  • this would be a lot more easier in google sheets – player0 Jul 07 '19 at 11:37
  • @player0 ...unfortunately I dont use google sheets at all... :/ – MmVv Jul 07 '19 at 11:44
  • I think I figured out another approach. So, for those who are familiar with chemistry those chemical formulas are written as a Chemical formulas (1/3th of them), and exactly those are with brackets. But, if I simply write them in Molecular formulas then my Excel formulas will work completely. Only thing you have to do it manually but better than nothing. So you will have instead of AsH2(C4H9) >>> C4H11As but that doesnt change molecular weight and thats actually what I need. But if someone still got know-how idea, write it!!! – MmVv Jul 07 '19 at 17:29

1 Answers1

0

Chemistry Mol Calculator

I used this function:

=E$2*MAX(IFERROR(IF(FIND(E$1&ROW($1:$99);$A3);ROW($1:$99);0);0);IFERROR(IF(FIND(E$1&CHAR(ROW($65:$90));$A3&"Z");1;0);0))
double-beep
  • 5,031
  • 17
  • 33
  • 41
MmVv
  • 553
  • 6
  • 22