Problems with decimal places using JCChemicalTerms

Hello,

how is the right use of the ‘JCChemicalTerms’ in ‘JChem for Excel’?

I’ve tried to calculate the solvent accessible surface area with at pH 7.4 with solventAccessibleSurfaceArea('7.4'), but the output was a number with a lots of decimal places.
The numeric’s were the same as in Marvin Sketch, but they cannot be rounded in Excel  (the Excel cell was formatted as number with 2 decimals).

I use JChem for Excel 5.8.2 and MS Office Excel 2007 (German Version).

Thanks,
Armin

Hi Armin,

usage of chemical terms is defined here:

https://www.chemaxon.com/jchem4excel/userguide/chemical_terms.html

 

Viktor

Hi Armin,

please try to wrap the function with a conversion function to convert the string to number.

= VALUE(JCChemicalterms( .....))

it will work.

If you have issues on your local settings with decimal separator please replace it to your convenient separator like:

=VALUE(SUBSTITUTE(JCChemicalterms( ;".";",";1) ))

with this embedding of multiple functions. 

Viktor

hamoriviktor wrote:

Hi Armin, please try to wrap the function with a conversion function to convert the string to number. = VALUE(JCChemicalterms( .....)) it will work. If you have issues on your local settings with decimal separator please replace it to your convenient separator like: =VALUE(SUBSTITUTE(JCChemicalterms( ;".";",";1) )) with this embedding of multiple functions.  Viktor

 

Hi Viktor,

thank you for your quick answer.

 

Both of
your approaches caused an error in the Excel cell (“error: invalid name”).

Is the
following syntax correct (I’m not an Excel-pro; “B2” is the cell with my input
molecule):

 

(fx) =
VALUE(JCChemicalTerms(B2;"solventAccessibleSurfaceArea('7.4')"))

 

(fx) = VALUE(SUBSTITUTE(JCChemicalTerms(
;".";",";1)
B2;"solventAccessibleSurfaceArea('7.4')"))

 

I have also tried to change the decimal
separator from “,” to “.”  in the Excel options - same effect!

Armin

It worked for us here at ChemAxon.

 

The issue you face is that this Chemical terms returns with a string not a numbr so you need to convert it to number, for this the VALUE( is the proper function in Excel.

Why Chemical terms returns with a string: it can return not only numbers but logical values, images so string is a "platform" the core java api can covert the molecules into.

So it would be nice if you could send us the document with your (non-confidential) molecule and we try to fix it.

 

 

hamoriviktor wrote:

It worked for us here at ChemAxon.   The issue you face is that this Chemical terms returns with a string not a numbr so you need to convert it to number, for this the VALUE( is the proper function in Excel. Why Chemical terms returns with a string: it can return not only numbers but logical values, images so string is a "platform" the core java api can covert the molecules into. So it would be nice if you could send us the document with your (non-confidential) molecule and we try to fix it.

Thank you
in advance.

Enclosed
you will  find the Excel sheet; the
calculation is in the register “ADME” (column “T”).

Maybe you
can solve also the problem with the logP / logD calculation of substance ‘WJS944’
 (‘Inconsistent molecule structure’ but
no error in the structure check).

Good luck,

Armin

Hi Armin,

 In U column I tried to replace . to , because in Hungarian env the , is the decimal separator.

In V column I tried a VALUE conversion  based on the replaced ","

In W column I tried a VALUE directly on the stirng what your T2 JCChemicalTerms returns with.

 

Viktor

Hi Viktor!

Thank you very much, now it works!
My ‘german’ Excel has not comprehended your proposed (english) terms ‘VALUE’and ‘SUBSTITUTE’.

Not really clear is the matter with the decimal separator:
In German the “,” is also the decimal separator. With the JChemExcel.Funktions everything is running well (output is a number with correct decimals).

The Chemical term calculations run only over the detour you found: conversion of the separator and then VALUE conversion (U and V column in the example). The VALUE conversion without separator exchange (W column) failed (enormous numerical value). Maybe this will run in a future version without detour.
Interestingly the ChemicalTerm “averagePolarizability” works with correct decimals?!

Have a nice weekend,
Armin