• Welcome to Overclockers Forums! Join us to reply in threads, receive reduced ads, and to customize your site experience!

Need an Excel Master here!

Overclockers is supported by our readers. When you click a link to make a purchase, we may earn a commission. Learn More.
King107s

King107s

Member
​
Joined
Oct 27, 2008
Hey guys,

I have a long list of chemicals in excel that I would like to include molecular weights for based on entered molecular formulas. I would like to know how I could enter the molecular formula as an argument and return the weight as a value.

Example:

Profenofos molecular formula (MF) is C11H15BrClO3PS and its molecular weight (MW) is ~373.65g/mol

This is calculated by multiplying the molar weight of each of the elements by their count and summing the total.
C = 12.01 * 11 = 132.11
H = 1.01 * 15 = 15.15
Br = 79.90 * 1 = 79.90
Cl = 35.45 * 1 = 35.45
O = 16.00 * 3 = 48.00
P = 30.97 * 1 = 30.97
S = 32.07 * 1 = 32.07
Then add up all the totals...

The math is easy, getting exact masses for the elements is also easy but making excel do it all as a function is something I havent been able to figure out.
 
If you know VB it would probably be easier to write your own custom function to do it rather than try to piece together the existing ones.

You would use your function something like: =mycustomfunction([cell location of the formula I want to translate], [cell location list of elements],[cell location list of coresponding element weights])

Your function would just parse the string left to right chopping off each element and accumulating the weight in a total as you go. You know a new element is starting when you see a capitol letter, you would then have to check if it is followed by a lower case letter, then check if the element is followed by a number.
 
Nevermind I was wrong....

WAIT i was right...

Enter element names (or symbols) in Column 1 (H, Br, etc.), enter the molecular weight corresponding to the element in Column 2. Now create a "Range" for each molecular weight, and name it the element (ie. the cell to the right of Hydrogen would be named range 'H'). This will allow you to use the math (ie. water would be =2+H+O or =H+H+O)
Range naming
 
Last edited:
jmh547 said:
Nevermind I was wrong....

WAIT i was right...

Enter element names (or symbols) in Column 1 (H, Br, etc.), enter the molecular weight corresponding to the element in Column 2. Now create a "Range" for each molecular weight, and name it the element (ie. the cell to the right of Hydrogen would be named range 'H'). This will allow you to use the math (ie. water would be =2+H+O or =H+H+O)
Range naming
Click to expand...

Thats not how the molecular formula is written though and I dont think this approach will achive what I want here. Try it with the example above and see if it works.

DaveHCYJ said:
If you know VB it would probably be easier to write your own custom function to do it rather than try to piece together the existing ones.

You would use your function something like: =mycustomfunction([cell location of the formula I want to translate], [cell location list of elements],[cell location list of coresponding element weights])

Your function would just parse the string left to right chopping off each element and accumulating the weight in a total as you go. You know a new element is starting when you see a capitol letter, you would then have to check if it is followed by a lower case letter, then check if the element is followed by a number.
Click to expand...

I think this would be work if I knew visual basic :)
 
My friend... there is a way to find the molecular weight using excel. but first u have to bring in all the database collected into two columns in ur excel sheet. and then post it back as an attachment and then, i'll let u know how to work it out....
 
Ok
I am confused a little

do you want excel to Auto Convert
C11H15BrClO3PS to 373.65g/mol
That I am unsure how to do with out building a sheet

if you are going to break it down
columb A = Element
Columb B= Mol Mass
Columb C = Quantity
Columb D = =B1*C1

at the Bottom of your element list in Columb D you would put =SUM(D1:D7) this is an exact example of the current element you describe.

I am not sure how to Pull out that data exactly from the full elemental formula
 
ratbuddy said:
Google says http://www.chemicalogic.com/mwcalc/
Click to expand...
+1000 (Provifding that it works, I don't use MS Office)
Saves the insanity of setting it up yourself.

If you really wanted to set it up with Excel yourself then you'd have to (as previously mentioned) have the Element names nad molecular weight in two columns. Then you have several options, creating an extensive if-then function that would seperate the characters following each capitol letter until the next one, then seperating the number assiciated, then finding the molecular weight, and then multiplying by the number and then adding it to the total weight already determined.
It's be REALLY long, and easily messed up, but I think it can be done. I think I could do it (80% chance) if I had 3-4 hours to spend fiddling with it.However, I'm honestly not up to that right now.
Any programming language would make it easier, writing a basic script in linux would make this easier. If I were doing it that way I know I could od it in less than an hour. Expecially if I already had a CSV file of the raw data.
IMO use the right tools for the job, get the extension.
 
curtis1552 said:
+1000 (Provifding that it works, I don't use MS Office)
Saves the insanity of setting it up yourself.

If you really wanted to set it up with Excel yourself then you'd have to (as previously mentioned) have the Element names nad molecular weight in two columns. Then you have several options, creating an extensive if-then function that would seperate the characters following each capitol letter until the next one, then seperating the number assiciated, then finding the molecular weight, and then multiplying by the number and then adding it to the total weight already determined.
It's be REALLY long, and easily messed up, but I think it can be done. I think I could do it (80% chance) if I had 3-4 hours to spend fiddling with it.However, I'm honestly not up to that right now.
Any programming language would make it easier, writing a basic script in linux would make this easier. If I were doing it that way I know I could od it in less than an hour. Expecially if I already had a CSV file of the raw data.
IMO use the right tools for the job, get the extension.
Click to expand...
Custom function in VBScript to parse. But yeah, someone's already done that work for you. :D
 
You must log in or register to reply here.
Back