Paint Color Batch Correction with Excel – CTC

Explaining the Digital Mechanism behind Color Batch Control Using Excel

Software packages from different suppliers like Datacolor and XRite have rather sophisticated features to calculate the optimal amount of additions to a batch of paint to get the color right. They take variables into consideration like the number of pigments to add, the size of the batch vessels to prevent overflow after addition, color difference, and metameric differences.

Many professionals in the industry work with these packages, however, we have noticed that not all of them completely understand the mechanisms behind the batch control software. After reading this article, you will have a better insight into the way color batch control digitally works, and it will help you to make better decisions related to color batch control in the future.

In the article, we restrict ourselves to discussing the color aspects of the paint, not aspects like the required volumes or issues related to vessel overflow.

The Batch of Paint

Let’s start with a batch of paint having a beige color and the following Lab values:
L=67, a=3 and b=9

The color formula of the batch color is:

Remark: We use the term ‘colorant’ to indicate the coloring substance. Sometimes other terms are used like mixing color, pigment, pastes, etc.

Suppose the color difference of the batch under 3 important light sources is:

To correct for these color differences, the most common practice is to add one or more of the colorants already present in the mixture. So, a first step could be to see what the influence of each individual colorant in the mixture is. You can, of course, do that by adding a small amount of each colorant and prepare an actual paint sample. The sample can be measured, and the differences compared to the standard color can be calculated. This is a practical method used by companies with relatively small numbers of colors to be delivered.

The Digital 1% Tinting Table

Another possibility is to use a color matching program and calculate the additions digitally. How this is done is less important for the crux of this article, but the results will be something like the following table:

In the table, the influence of 1% colorant addition on the color formula is given for all colorants in the mixture. From this tinting table we see, for instance, that adding 1% of Blue colorant results in a darker (dL), greener (da) and, unsurprisingly, bluer color (db). The influence of White is very modest as compared to the influence of Red. So, you need to add a lot of white to notice any color difference.

The da/db and da/dL Color Plots

Let’s look at the graphs depicting the influence of the colorants on the color of the batch under D65:

In the first graph, the influence of 1% colorant is given in the ab diagram under daylight. In the second graph, the influence on da and dL is given. Combining these graphs gives us a way to picture 3D space as two projections of the 3D color space. The influence of the Red colorant is significantly larger than the influence of Blue. Sometimes this influence is referred to as the ‘color sensitivity’ of a colorant in a mixture. It is also clear that the influences in the dab diagram are very different from those in the da/dL diagram. Which is to be expected, of course.

The Initial Batch Differences under Three Light Sources

Now, lets look at the da/db and da/dL graphs of standard color compared to batch color:

The differences under three light sources are connected with each other through straight lines. Ideally, all points would be at the origin, meaning that the color differences are zero. Looking at the da/db diagram, adding Blue would reduce the difference between standard color and batch color. However, this would not help the color in the da/dL graph. We also see that the Yellow and Red colorants have an opposite effect in the da/db diagram, but not so in the da/dL diagram. So, finding the optimal additions and making sure that the number of colorants is minimized is not so easy to see, especially if more than one light source needs to be considered, as well as other aspects like minimizing the metameric effect between light sources.

The Excel Solver in Action

Now how can we use Excel to calculate the best additions and taking this intricate play of color influences into account?

Let’s start by using the Solver in Excel. We need the 1% tinting table and the dL, da, and db differences under three light sources of the batch compared with the standard. Because the correction additions of this second table are zero when we start, their influences are also zero. And, therefore, the differences before and after the optimization are the same.

With the Data/Solver function of Excel, the following screen pops up. In the ‘Set Objective’ field, the cell that needs to be minimized is indicated, and in the field ‘By Changing Variable Cells’ the concentration of the colorants to add are indicated by Correction. We check the box labelled ’Make Unconstrained Variables Non-Negative’ to make sure colorants can only be added. (Note that all cells refer to each other with underlying function, like dE = =(J23^2+J24^2+J25^2)^0.5), etc.)

Then we press the Solve button…

The combined dE value decreased from 1.25 to 0.46, a substantial reduction.

The Solver Results Analyzed

Looking at the shift in de da/db and da/dL diagram the Solver was successful i.e the green lines are much closer to the origin that the initial red lines.

Also, the combined color difference under three light sources is reduced from 1.25 to 0.46 by adding 1.2 Blue, no Yellow1, 6.2 White, 0,5 Yellow2, and 0.39 Red. Well, this seems a rather big beneficial step. However, the additions are substantial, i.e. 8.3. Can we achieve similar success with less addition? Yes, we can!

Let’s first look at the colorants. Perhaps they are working against each other. From Graph 1, it can be concluded that some colorants partly work against each other. There is, however, an alternative way to detect this. Using the Data/Data Analysis/Correlation option on the tinter table, we obtain the following table:

It is easy to see that Blue and Yellow 2 are negatively correlated, which means that if one of them are set to zero, the other will be less also. We run the Solver again and set the value of Yellow 2 as constraint to 0. Hitting Solve produces the following table:

And indeed, the additions go from 8.3 to a total of 2.2 and the combined dE hardly increased. Using these options, you can play around with different constraints to see what happens with the color of your batch.

One of the aspects that is important for some businesses is the metameric index (MI) and the combination of MI with color difference. For instance, a specification on dE might require it to be smaller than 0.5 AND the metameric index to be smaller than 0.5 as well. In our example, this effect would however be rather minimal.

Closing remarks

1. 1. As remarked earlier, dedicated software programs are more sophisticated and take more aspects of batch control into consideration than this example Excel sheet. These programs make sure the vessel will not overflow, and can add the correction colorants not present in the mixture. However, they are sometimes not flexible enough regarding the parameters that need to be optimized. Excel enables us to involve various criteria in the optimization not supported by regular software, such as the combination of color differences and metameric indexes. Also, weighing factors could easily be introduced in Excel on different levels.
  2. Consider Excel as an intelligent calculator to support your problem in cases where a regular software doesn’t give the right answers.
  3. Dedicated software programs do not calculate with a 1% tinting table. They work with the derivative of the optical model which is better and can cause substantial differences. This especially happens in case of colorants with a very high influence on the color of the batch i.e. a high color sensitivity. However, if there is no sound optical model available, the 1% tinter table method still works!

Roel Gottenbos is an experienced color research manager and developer of game changing color tools. He helps companies to improve their color processes from raw material to end product. Roel has the privilege to work together with Erik van Biemen within Color Technology Consultancy.

Please contact us if you need more information or would like to comment on the content of this publication: info@coltechcon.com.