Molecular formulae and weeding out zeros and ones

[ltyar]

Hello all-

Although this problem seems simple on the surface, i am not finding the
answers i need in the help section.

When using the spreadsheet to enter a molecular formula with the intent
of generating a molecular weight, it is also desirable to create a cell
which will display the entire formula in addition. ***

In order to display the entire formula in one cell I have partially
achieved, using the "C"&A17&"H"&B17&"N"&C17&"Cl"&D17etc method (if
anybody knows of a more elegant way to achieve that result, i'm all
ears).

However, this method requires that compound that have zero chlorines or
sulfurs or whatever to show up as (for example) C4H7N5S0Cl0. The
software we use accepts formulae copied from the Excel speadsheet and
pasted into the appropriate boxes, but it cannot handle these zeros.

How do i set a conditional either in each cell or on the entire
spreadsheet that if D17's value is zero, the "Cl" is not displayed in
the formula? Same with each column, to be specific, be it C, N, H, S,
Cl, etc.

Thanks a ton

***Background on how this is set up: If you picture columns whose
headers are C,H,N,Cl,O, S etc, the values underneath are the number of
each of those atoms types in the molecule. I have a formula cell that
calculates the MW (12.01*A17+1.008*B17+14.01*C17 etc) using the
molecule weight of each atom times the number of those atoms in the
molecule.

 

[JE McGimpsey]

In order to display the entire formula in one cell I have partially
achieved, using the "C"&A17&"H"&B17&"N"&C17&"Cl"&D17etc method (if
anybody knows of a more elegant way to achieve that result, i'm all
ears).

However, this method requires that compound that have zero chlorines or
sulfurs or whatever to show up as (for example) C4H7N5S0Cl0. The
software we use accepts formulae copied from the Excel speadsheet and
pasted into the appropriate boxes, but it cannot handle these zeros.

How do i set a conditional either in each cell or on the entire
spreadsheet that if D17's value is zero, the "Cl" is not displayed in
the formula? Same with each column, to be specific, be it C, N, H, S,
Cl, etc.

One way:

=IF(A17>0,"C" & A17,"") & IF(B17>0,"H" & B17,"") & IF(C17>0,"N" &
C17,"") & IF(D17>0,"Cl" & D17,"") & IF(E17>0,"S" & E17,"")

 

[ltyar]

Cool. I knew there had to be a way. I'll give it a shot.

I suppose this cannot be done globally for the entire spreadsheet, but
must be cell specific?

(Didn't think so )

 

[JE McGimpsey]

Cool. I knew there had to be a way. I'll give it a shot.

I suppose this cannot be done globally for the entire spreadsheet, but
must be cell specific?

Not really sure what you mean - you can change the references at will.

If you have a table of data, and, say, column A is always carbon, column
B is always Hydrogen, etc., then you can copy the formula down...

A B C D E F
17 4 7 5 0 0 C4H7N5
18 6 5 0 1 0 C6H5Cl

One could also write a User Defined Function that returned a string:

=CHEMFORM(A18,B18,C18,D18,E18)

to return

C6H5Cl

Perhaps something like:

Public Function ChemForm( _
Optional ByVal C As Long, _
Optional ByVal H As Long, _
Optional ByVal N As Long, _
Optional ByVal Cl As Long, _
Optional ByVal S As Long) As String
ChemForm = IIf(C > 0, "C" & C, "") & _
IIf(H > 0, "H" & H, "") & _
IIf(N > 0, "N" & N, "") & _
IIf(Cl > 0, "Cl" & Cl, "") & _
IIf(S > 0, "S" & S, "")
End Function