Showing results for 
Search instead for 
Did you mean: 

A Template for Efficiency, Simplicity and Consistency

Team TFS
Team TFS

What is your favourite thing about Chromeleon CDS? If you have read any of my previous blog posts, I’d guess you would think mine is audit trails or queries.

cdsGood guess but you’d be wrong; I love Chromeleon CDS report templates. There is nothing better than taking a newly supplied analytical method, with some slightly ‘odd’ calculations or complex reporting criteria and creating a report template that does the job.


Reducing Complexity

In much the same way eWorkflow™ procedures can simplify the complexities of sequence creation, a good Chromeleon CDS report template can streamline the intricacies of result calculations and reporting.

Here at Sterling we have multiple methods all with individual reporting criteria. This complexity can lead to inefficiency and errors, especially if calculations are performed manually or in an uncontrolled spreadsheet. In the years BC (Before Chromeleon!) we actually transcribed results into workbooks or sheets and manually made this comparison! Of course this manual process of recording results and comparing them to the criteria was prone to error. But with the implementation of our lab-wide CDS, we thought, “Why not incorporate the criteria into a report template and let Chromeleon CDS automate the reporting process?”

I’d like to show you how Sterling has used Chromeleon CDS report template functionality to help simplify reporting and the interpretation of reporting criteria. In the following example we have used Chromeleon CDS report template functionality to automatically calculate the results and compare them to the reporting criteria.


Reporting Criteria: Limits of Quantification (LOQ)

It is quite common for residual solvent methods to have reporting instructions as follows:

    • Report any component not detected as <LOQ (ND)
    • Report any component detected but less than LOQ as <LOQ
    • Report any detected components >LOQ to 0 decimal places

In theory this is quite a simple set of reporting criteria but in practice how many components do you have? Do they have different LOQ values? Have you rounded the result correctly? What other tests are you running today, what are their criteria?

Example Report Table

Consider the example table in Figure 1

Figure 1: Chromeleon report table showing reportable amountFigure 1: Chromeleon report table showing reportable amount


LOQ:                                    Displaying component specific LOQ values
Amount:                              Undetected components are displayed as (ND) instead of ‘n.a.’ with
Reportable Amount:         Rounded reportable results with peaks <LOQ displayed as “<##ppm” with undetected peaks displayed as “<##ppm (ND)”

Hopefully you can see and agree that the last column simplifies the interpretation of the reporting criteria for the analyst. The final reportable amount is clearly shown and meets the reporting criteria.



How Was This Done? Formulas to the Rescue!

Most spreadsheet users will be familiar with formula functions. Did you know they can be used in Chromeleon CDS report templates and tables? Let’s take a stepwise look at the formulas behind the important columns…

Step 1: Create a Custom Component Variable and Column for the LOQ Levels

A Custom Component Variable can be created directly in the Processing Method or in the Console, Tools menu

    • Create a new custom variable and set the type to ‘Numeric’ in the drop down and complete the fields as required (Figure 2).

Figure 2: Custom Component Variable creationFigure 2: Custom Component Variable creation


The component specific limits of quantification can then be added for each component to a new column (LOQ) in the component table in the Processing Method as shown in Figure 3. Note the * simply highlights that this is a custom column.

Figure 3: Add component LOQ limitsFigure 3: Add component LOQ limits

 The LOQs can then be added as a column in the report table (by setting the column formula to component.customVar(“LOQ”)) and we can use this to compare our results to the LOQs.

Step 2: Amount Column

We are going to use two functions.


    • IF statements are logical arguments, we can use these to test a condition (a calculated result or any other parameter) and return a corresponding TRUE or FALSE value


    • ISERROR checks for any error, such as n.a., #N/A, #VALUE!, #REF!, #DIV/0!, or #NAME?, and gives a TRUE or FALSE output

In the ‘Amount’ column we want to check whether there is a calculated peak amount and report “(ND)” if the peak is not found in the chromatogram. To start, open the table properties for the required report column and type ‘if’ into the formula bar then click on parameters, as shown in Figure 4.

Figure 4: Editing the IF formulaFigure 4: Editing the IF formula


The ‘IF’ parameters window will open.

Figure 5: IF formula parametersFigure 5: IF formula parameters

    • Set the conditions as shown in Figure 5 (Note: the “” are important to show this is text)
    • Click OK to see the full formula: if(iserror(peak.amount),"(ND)",peak.amount)

So what does this all mean? In this example Chromeleon CDS will check if the peak amount is an error (i.e. ‘n.a.’) and show (ND), otherwise the peak amount is shown.

Step 3: Reportable Amount Column

We will use two more functions, ROUND and TEXT, plus a nested IF formula:

    • ROUND rounds the selected variable or number to the specified number of decimal places
    • TEXT simply converts a value to text

A nested IF is simply one or more IF functions within an IF function. The general formula for a nested IF is: IF(Condition1, Result1,IF(Condition2, Result2, Result3)). Of course you can ‘nest’ even more IF functions to cover all your options!

To hopefully make this a bit clearer, we are asking Chromeleon CDS to test Condition1, if it’s TRUE return Result1, if it’s FALSE test Condition2. If that’s TRUE return Result2, if it’s FALSE return Result3.

The actual formula in Column D is:

IF(ISERROR(peak.amount),"<"+TEXT(component.customVar("loq"))+"ppm (ND)", IF(ROUND(peak.amount,0)<component.customVar("loq"),"<"+TEXT(component.customVar("loq"))+"ppm", peak.amount))

Let’s break this down and convert to plain English!

Blue: Check if the peak amount variable contains an error (e.g. ‘n.a.’), if TRUE return the text ‘<##ppm (ND)’, otherwise perform the next (purple) IF formula.

Purple: If the peak amount rounded to zero decimal places is less than the LOQ return ‘<##ppm’, otherwise display the calculated peak amount (red).

So this would result in a value of ‘<10ppm’ being returned for a component that is detected but at less than its LOQ of 10ppm but ‘<10ppm (ND)’ if the same component is not detected.

By using nested IF formulas in one column of the table we end up with an elegant solution to our result reporting.


Top Tips


    • This example uses an integration table but these formulas also work in summary and consolidated tables.
    • The same principles can be used for custom calculations in the report template cells.
    • The formulas can look complicated as there are brackets everywhere! The easiest way to ensure you get the syntax right is to create each part in an individual column then, once you get the individual columns working, build the final formula.
    • To help build the final formula you can copy and paste the component parts from the IF formula in the column properties into cells of the report template. This will give you a text copy that you can build upon and can be pasted back into the combined formula. I prefer to write the whole formula out in a cell as text which can then be copied into the column properties formula.
    • Finally did you know you can save your formula as a Custom Formula that can then be reused more simply by calling the Custom Formula instead of having to type out the whole formula each time? This can help eliminate errors in formulas and speed up report creation.

Simple, Efficient, Consistent?

So let’s go back to our reporting criteria:

    • Report any component not detected as <LOQ (ND)
    • Report any component detected but less than LOQ as <LOQ
    • Report any detected components >LOQ to 0 decimal places

Which do you feel is the most useful table in Figure 6 for helping the analyst report the results correctly and eliminate errors?

Figure 6: Report tables with and without the custom formulasFigure 6: Report tables with and without the custom formulas


For me, the first table requires manual result rounding and comparison to the LOQ values which could lead to human errors. The second table automatically gives the analyst the results in the format required and the final reportable results are displayed. ‘Raw’ results and LOQ values are also shown so that both analyst and reviewer can clearly see the reported results are correct.



Time Saved

This example is a simple one, but we have many other analyses, such as impurity analysis that require much more intricate reporting criteria. The time and effort invested in creating and verifying Chromeleon Report Templates to automatically report the results to these specific criteria and to the correct number of decimal places has been returned significantly. It is hard to get an exact figure on the time saved but I would estimate the time taken to evaluate and report these more complex results has been reduced by 25-50%. This is not taking into account the reduction in transcription errors. But more importantly for me the templates ensure data is interpreted and reported in a consistent manner.


Remember To Check It!

Of course the software can help immensely but it is still the responsibility of the analyst and reviewer to check the correct results are reported. Plus the report templates must be verified to ensure correct operation.

During template verification it may be necessary to produce some ‘creative integration’ to check your LOQ values, rounding, greater than and less than arguments are working correctly. This is why we always work with a copy of a sequence that contains data for report template development and verification leaving the original data untouched (and only our administrators can copy sequences with data).  And remember to always add detailed explanations as to why a copy has been made in the audit trail comments!


Want To Go One Step Further?

How about adding a specification comparison with conditional formatting to clearly highlight failures?

Figure 7: Report Table with pass/fail comparison to specification and conditional formattingFigure 7: Report Table with pass/fail comparison to specification and conditional formatting


First create another custom component variable for the specification (Column E in Figure 7). Then append another column (Column I) to the table for the Pass / Fail comparison – this where the magic happens! What we want Chromeleon CDS to do is to check the reportable result (Column H) and find out if it’s text (e.g. <10ppm (ND)) or is less than or equal to the specification (Column E) and report a Pass or Fail.

To add a custom ‘Excel-type’ formula to a table column, there are 2 options: Edit in the report table properties, or type directly into the cell and then copy it down the entire column. In this example we can edit cell I32 with the formula below. This formula will produce a column containing either ‘1’ for in spec results or ‘0’ for those out of spec.

Formula: =VALUE(IF(OR(ISTEXT(H32),H32<=E32),“1”,“0”))

This uses some new variables: VALUE, OR and ISTEXT:


    • VALUE: This will return a number from text (ensuring we have a number 1 or 0 at the end, not text).
    • OR: This will check the two conditions and return TRUE if either of them is true.
    • ISTEXT: Returns TRUE if cell H32 contains text.

On pressing enter, the CDS will ask you ‘Are you really sure?’ as this input will replace a dynamic Chromeleon Report Variable. Answer yes.

Now open the column properties where you can see the calculation in the Formula line as shown in Figure 8.

Figure 8: Report Column PropertiesFigure 8: Report Column Properties


To activate the conditional formatting and ensure the 1 is now converted into Pass and 0 into FAIL, enter the following in the Format box: [Color10][=1]“Pass”;[Color3][=0]“FAIL”



Do You Know Easier Ways To Do It?

I have been a Chromeleon 7 software user for over seven years and I’m still finding new and better ways of generating and presenting results. I would encourage any Chromeleon CDS users to share their template tips and tricks in the Chromeleon User Group on LinkedIn so we can learn together as a community.

Not applicable
Hi Brian,

I can't seem to get your equation to work for me:

IF(ISERROR(peak.amount),”<“+TEXT(component.customVar(“loq”))+”ppm (ND)”, IF(ROUND(peak.amount,0)<component.customVar(“loq”),”<“+TEXT(component.customVar(“loq”))+”ppm”, peak.amount))

Chromeleon is giving me an error at position 25 where the "<" is. Please help! This is a great idea I would like to implement in my report template that I am working on.

Thank you kindly!
Team TFS
Team TFS
It looks like there is a space making it two lines. Try removing the space.
Involved Contributor
Involved Contributor

Hello, i'm trying to use conditional formatting with chromeleon 7. I want to color text according to 2 conditions. My area must be between 2 values. I put the formula "IF(AND(...", but chromeleon doesn't know the formula "AND" and puts me an error. 


Do you know why ? How can i do ? 


Thank you ! 

Community Manager
Community Manager

Hello @jle, Welcome to the community!  

We will work to find you an answer quickly. 

@lfernandes Could you help with this question about Chromeleon? Thank you!

Community Manager
Community Manager

Hello @jle,

@peter-zipfell will be able to help provide you and answer Wednesday or Thursday. But he asks if you can let him know 

the exact version of Chromeleon 7 you are using, e.g.,  7.2.10 or SR5, etc.


Team TFS
Team TFS

Hello @jle 


Are you trying to use the Excel way of applying conditional formatting? The Chromeleon way requires a slightly different approach. It's probably easier if i refer to the initial article, where you will notice in the Column Properties that the Chromeleon formula is slightly different to Excel. It's made up of two components, the calculation formula and the format entry field that activates conditional formatting.


In your example you need to use the Chromeleon style as opposed to Excel. To consider the area between two values then you probably need to enter a formula like if(peak.area>0.3 AND peak.area<0.86,1,-1). I left my numbers in as they relate to the dataset i happen to have open in Chromeleon, replace as needed. This would work for a peak area column field, but you would need to adjust the formula if you need to refer to a partic cell or cell range.  For the conditional formatting i would use something like [Green][>0]"Pass";[Red][<0]"Fail";General.


You may or may not see a boolean error, depending on if you encounter n.a. If this can't be ignored then you will need to adjust the formula again to something like if(iserror(peak.area),0,if(original formula). In addition, you will need to amend the conditional formatting (format) to consider [Black] [0] "no peak". This part i haven't tested fully, but hopefully you get the idea?


If you need further help then please free to get back in touch. I would also be interested if you have success too.

Involved Contributor
Involved Contributor

Hello @peter-zipfell

Thank you very much for your reactivity and your help, its working ! 😀

I had a boolean error but with "if(iserror..." its ok. 


Team TFS
Team TFS


That's great news!

I hadn't checked the "if(iserror.......", but it's a common way to handle those n.a.'s when you don't want them. Did you have to tweak the formula for that one? I hadn't checked it out in detail.

Involved Contributor
Involved Contributor

Hello @peter-zipfell 

I answered without trying the formatting for "no peak"...


One of my current formula is : if(iserror(peak.area);0;if((peak.area>0) AND (peak.area<61,4);1;-1)) with format : [Color10][=1]"Pass";[Color3][=-1]"Fail";General > Its working

When i put : [Color10][=1]"Pass";[Color3][=-1]"Fail";[Color1][=0]"No peak";General i have an error message "Invalid number format"

I tried with : [Color10][=1]"Pass";[Color3][=-1]"Fail";[Color3][=0]"No peak";General i have the same error message "Invalid number format"

Do I really need to add a format for "no peak" ?

Team TFS
Team TFS

Hi @jle,


Not necessarily. The 'no peak' shows without the conditional format entry, right?

It will probably shows as black text anyway, so If you're happy then i would leave it for now.