Well-Behaved Color Data Calculations

Color tolerancing is critical for quality control on colored products and for applications like assessing efficacy of cleaning products. We use a standard Excel data entry template (subject of an upcoming post) that can calculate the CMC Delta-E statistic to quantify the color difference between two samples in a way that correlates to human perceptibility (see the formula here under the “CMC l:c (1984)” heading or see ASTM D2244-16). This blog covers details of calculating needed hue angles from CIELAB (aka L,a,b color) data as commonly measured by lab instruments. It is an illustration of how good models go to lengths to get calculation details right even when that is not easy. The graph’s second set of datapoint labels shows the needed 0 to 360 degree hue angles for measurements mocked up to all have a Chroma (e.g. radius) equal to 10. The data file is ATAN_Check.xlsx in this Github repository. These calculations serve as our validation of this portion of the CMC ΔE template calculations –validation being a key characteristic of best-in-class models

Starting with CIELAB data necessitates converting between rectangular x, y (a-color and b-color) and polar r, Θ (e.g. Chroma, C, and Hue angle, h) coordinates. No problem, right? An initial, (and stay tuned, incorrect), thought is that a data entry template could utilize Excel’s built-in ATAN function to compute h as arctangent of b/a –with the color science reminder that a-color is red-to-green and b-color is blue-to-yellow and, when converted to polar coordinates, the radius or Chroma, C, is the intensity of the color and h is the hue which describes whether the color is red, green, yellow and so on.

Per the ATAN column (Column C) below, that function only returns results as first and fourth quadrant angles from -90 to +90 degrees and with #DIV/0! unpleasantness at the points where a-color is zero. We don’t get the required 0 to 360 degree hue angles. (If you are unfamiliar with Excel’s “@” notation in these formulas, see the note at the end).

We get past this situation by using Excel’s ATAN2 function. Historically, ATAN2 probably represents Microsoft recognizing ATAN’s limitations and (almost…keep reading) correcting them. The ATAN2 column (Column D )and the first labels on the graph’s datapoints shows ATAN2 calculations for our test dataset.

ATAN2 results range from -180 to +180 degrees with no errors when a-color (aka x axis value) is zero. That’s better, but still not 0 to 360 degrees. For that, we add a conditional correction in the ATAN2_mod (Column E) values that converts negative angles to positive. These are the second set of labels on the graph datapoints. This modified ATAN2 formula solves the problem of getting 0 to 360 degree hue angles for color tolerancing.

Note on Excel “@” Notation for Referring to Named Ranges

Our mockup file’s formulas use the modeling best practice of naming columns A and B “a_meas” and “b_meas”, respectively and using what Microsoft calls “implicit intersection” with the “@” symbol preceding the names in the formula. The “@” says to return the row’s value from the named column, and this is a best practice for creating easy-to-understand formulas (e.g. compare symbolic “@b_meas/@a_meas” here to classic cell reference syntax like “B4/A4”).  The first is easy to understand as the ratio of b:a colors. The second is more cryptic. We teach these best practices and more in our Digital Transformation with Excel online course.