Color Space Mathematical Modeling Using Microsoft Excel

Abstract

Colorimetry allows every color to be defined as a combination of three values, known as color coordinates. Color coordinates allow researchers to universalize and standardize their color measurements, and nowadays, determining the color coordinates of samples is a routine method applied by organic and analytical chemists, students, and specialists like enologists. Although the colorimetry method requires a simple experimental procedure, many students, researchers and teachers might find the mathematical treatment of the data too difficult. In this paper, we introduce two workbooks using Microsoft Excel with Microsoft Visual Basic that simplify the use of colorimetry: one of these workbooks is specifically oriented to students and teachers, and it includes explanations and information about each color model. The second workbook is aimed at researchers, and it allows them to introduce and analyze numerous samples at the same time. Finally, another workbook has been included that contains various sample spectra that teachers can use to improve student comprehension of color coordinates.

Full text

Color Space Mathematical Modeling Using Microsoft Excel
M. J. Delgado-Gonzá
lez, Y. Carmona-Jimé
nez, M. C. Rodríguez-Dodero, and M. V. García-Moreno*
Department of Analytical Chemistry, University of Cadiz, Puerto Real, Cá
diz 11510, Spain
*
SSupporting Information
ABSTRACT: Colorimetry allows every color to be defined as a combination of
three values, known as color coordinates. Color coordinates allow researchers to
universalize and standardize their color measurements, and nowadays, determining
the color coordinates of samples is a routine method applied by organic and
analytical chemists, students, and specialists like enologists. Although the colorimetry
method requires a simple experimental procedure, many students, researchers and
teachers might find the mathematical treatment of the data too difficult. In this paper,
we introduce two workbooks using Microsoft Excel with Microsoft Visual Basic that
simplify the use of colorimetry: one of these workbooks is specifically oriented to
students and teachers, and it includes explanations and information about each color
model. The second workbook is aimed at researchers, and it allows them to
introduce and analyze numerous samples at the same time. Finally, another
workbook has been included that contains various sample spectra that teachers can
use to improve student comprehension of color coordinates.
KEYWORDS: First-Year Undergraduate/General, Graduate Education/Research, Analytical Chemistry, Organic Chemistry,
Computer-Based Learning, Acids/Bases, Agricultural Chemistry, Dyes/Pigments
■INTRODUCTION
In the fields of chemistry and enology the determination of the
color spaces of liquid samples is widely applied. For example,
chemists evaluate color using the CIE L*a*b*model of
organic dyes and colorants
1−5
and pH indicators,
6−8
and
enologists analyze the color of wine and spirit samples.
9−11
This procedure is officially described by the International
Organization of Vine and Wine (OIV) as “one of the most
important visual features that provide a large amount of
information”.
12
Numerous papers have been published on the importance of
teaching students color evaluation processes.
13,14
Although
these methods are easily understandable for students, the most
general method of determining the color of a sample requires
the quantification of three values known as color coordinates.
Although the experimental procedure is quite simple, the
determination of color spaces of samples involves long and
difficult mathematical equations that have been developed by
the International Commission on Illumination (CIE) and
these are explained in their technical reports.
15
For this reason,
in this paper we have collected the expressions for some color
spaces in Microsoft Excel with the aim of simplifying the
mathematical treatment for students, researchers, and teachers,
in addition to explaining and facilitating the understanding of
color coordinates for students of chemistry and enology.
What Is a Color Space Model?
Human cone cells are photoreceptors that are responsible for
the perception of each color that we observe. There are only
three types of human cone cells, namely, long wavelength
cones, medium wavelength cones, and short wavelength cones,
which are excited by light at different wavelengths of the visible
spectrum, i.e, red, green, and blue light, respectively. Therefore,
as explained by the trichromatic theory of color vision, human
perception of color depends on the wavelengths of the light
that interacts with our photoreceptors, and it can be defined as
a mixture of three primary light colors: red, green, and blue.
CIE defines 3D graphs that represent all colors that humans
can see, with each one defined as a combination of three values
known as color coordinates, which are determined by
mathematical formulas that are expressed considering the
absorbance spectra of human cone cells. In this way, each color
coordinate is usually related with one of its color properties:
for example, in the CIE L*a*b*space model the L*parameter
is related to the luminosity of the color, the a*parameter
defines the red or green component of the color, and the b*
parameter defines the yellow or blue component.
Importance of Colorimetry
The fastest and cheapest color-determination method is the
simple observation of the sample. This method has two
drawbacks: first, two different analysts can observe the same
color and obtain different results, and second, the color
depends on the illumination of the sample, so if the analysis is
carried out under different lighting conditions, e.g., daylight at
different hours, the results may change. This effect is
represented in Figure 1: under cold lighting conditions, colors
are sharper and more brilliant than colors that are observed
Received: September 4, 2017
Revised: July 9, 2018
Published: August 3, 2018
Technology Report
pubs.acs.org/jchemeduc
Cite This: J. Chem. Educ. 2018, 95, 1885−1889
© 2018 American Chemical Society and
Division of Chemical Education, Inc. 1885 DOI: 10.1021/acs.jchemed.7b00681
J. Chem. Educ. 2018, 95, 1885−1889
Downloaded via 173.211.86.104 on November 30, 2020 at 10:59:47 (UTC).
See https://pubs.acs.org/sharingguidelines for options on how to legitimately share published articles.

under a warmer light source. In fact, in this example, yellowish
and reddish lights produce colors that cannot be easily
distinguished.
Given the drawbacks outlined above, CIE colorimetry
methods were developed in order to universalize and
standardize color determinations and results independently
of the light conditions and/or the analyst. This is possible
because colorimetry mathematical color spaces are defined
using two standard parameters: a standardized lighting source,
which fixes the effect of different lighting conditions over the
results, and the observer degree, which removes the human
factor from the analysis. In this paper, all equations are
expressed for a D65 CIE standardized lighting source,
proposed by the CIE to represent mean daylight, with a
color temperature of 6500 K
16
and an observer degree of 10°.
Therefore, the use of these standard parameters in addition to
all universal colorimetry formulas was an important improve-
ment to achieve reproducibility of color results.
■DETERMINING THE COLOR SPACES OF THE
SAMPLE WITH MICROSOFT EXCEL
Several spreadsheet files were designed to carry out the
mathematical treatment. These spreadsheets within workbooks
allow one to obtain the color coordinates from the trans-
mittance spectra. Specifically, two Excel workbooks were
developed: Template-5 nm-for-Students.xlsm and Template-5
nm-for-Researchers.xlsm.
The file Template-5 nm-for-Students.xlsm was created for use
by teachers and students in their laboratory color practical
classes or to facilitate classroom explanation. The spreadsheets
in this workbook are protected so that formulas, graphs, or
images cannot be accidentally changed or moved. This file
contains five sheets that include explanations and recom-
mendations that may be helpful for students.
The file Template-5 nm-for-Researchers.xlsm was created for
use by researchers in their laboratory color experiments, and
10 sample columns were added. This Workbook is not
protected, so, if desired, all columns can be copied and pasted
so that the sample list can be as large as the researcher requires.
This file, which only has one sheet, does not include as much
information and explanation as the Workbook for students, but
all color parameters of different samples are automatically
determined at the same time when the spectra are pasted in the
white columns.
It is important to remember to accept the use of macros
when using these sheets by clicking the enable content button in
the message that appears at the top of the sheet.
Obtaining the Spectrum of a Sample
As described by ISO 11664-4,
17
all color space determinations
must begin by obtaining the transmittance or reflectance
spectrum of the sample in the range 380−780 nm. Trans-
mittance spectra must be obtained for liquid samples, and
these samples must be transparent and must not contain
suspended particles. Reflectance spectra must be obtained for
solid samples, and these must have a totally flat surface.
Liquid samples must be introduced into a 10 mm cuvette,
which is the reference path length, in order to obtain universal
color coordinates. If possible, the spectrum should be recorded
with a wavelength resolution of 5 nm as the factors and
formulas provided are expressed for this resolution. The blank
is measured with distilled water if the sample is liquid or with a
blank solid standard if the sample is solid (for example, for
reflectance analyses commercial standards of CaCO3are often
used).
Once the transmittance or reflectance spectra of all the
samples have been measured, a mathematical treatment is
required to obtain the color coordinates.
18
The mathematical
expressions employed in the most commonly used color spaces
(CIE XYZ, CIE L*a*b*, CIE LCH, and RGB) are defined in
the file named Color-spaces-formulas.pdf, which can be found in
the Supporting Information.
■USING TEMPLATE-5NM-FOR-STUDENTS
WORKBOOK
First Sheet: Template 5 nm
In the first sheet, called Template 5 nm, the sample spectrum
must be introduced so that all formulas can automatically
return the color space coordinates. In order to understand this
sheet better, some instructions have been introduced in the
student’sfile.
Second Sheet: CIE L*a*b*
The second sheet, called CIE L*a*b*, automatically returns
text with the L*a*b*results and their meaning, and it
automatically express the resulting L*,a*, and b*values in two
graphs: the a*−b*graph and the L*graph (Figure 2). In
addition, some information about this color space and the
meaning of its parameters are given.
Figure 1. Fruit, wine, and olive oil, photographed under eight different light sources. In the first row, from left to right: candle light, warm light,
solar light, and cold light. In the second row, from left to right: red light, orange light, green light, and blue light. Photographs were taken with a
Nikon D300 digital camera in manual mode with white balance in the sunlight position, with a focal length of 18 mm, 4.5 diaphragm aperture, and
shutter speeds of 1/30 sec for photos in the first row, and 1/15 sec for photos in the second row. A JEDI lighting LED E27 (LTL International,
Belgium) color-changing bulb was used as the light source.
Journal of Chemical Education Technology Report
DOI: 10.1021/acs.jchemed.7b00681
J. Chem. Educ. 2018, 95, 1885−1889
1886

Third Sheet: CIE LCH
The third sheet, called CIE LCH, automatically returns text
with the results and a brief explanation of their meaning, and it
also automatically expresses the obtained L*,C*, and H*
values in two graphs: the C*−H*graph and the L*graph
(Figure 3).
Fourth Sheet: RGB
As in the previous sheets, the fourth sheet, called RGB,
automatically expresses the obtained R,G, and Bvalues in
three graphs: the Rgraph, the Ggraph, and the Bgraph
(Figure 4), and the values obtained for each parameter are
summarized.
Fifth Sheet: Formulas
Finally, the sheet called Formulas contains a summary of all
equations proposed by the CIE and all formulas used in
Microsoft Excel. This sheet was included because teachers may
want to introduce some equations to their students, or they
may want the students to understand that mathematical
treatment of the sample spectrum is not as easy as this
spreadsheet file makes it.
■EXAMPLES
The file Examples.xlsx can be found in the Supporting
Information. This file includes four spectra: one spectrum of
an orange dye, one of a violet dye, one of a blue dye, and
another of a green dye. These four dyes were chosen because
Figure 2. a*−b*graph and L*graph for (a) an orange dye and (b) a
violet dye. In the a*−b*graph, the black dot represents the point (a*,
b*) of the sample. In the L*graph, the red line represents the L*of
the sample.
Figure 3. C*−H*graph and L*graph for (a) an orange dye and (b) a
violet dye. In the C*−H*graph, C*is graphically the length of the
dotted line, and H*is represented as the angle of the dark sector. In
the L*graph, the red line represents the L*value for the sample.
Figure 4. RGB graphs for (a) an orange dye and (b) a violet dye. The
white line represents the R,G, and Bvalues of the sample.
Journal of Chemical Education Technology Report
DOI: 10.1021/acs.jchemed.7b00681
J. Chem. Educ. 2018, 95, 1885−1889
1887

they present different a*and b*signs, so teachers can use
these examples to improve their explanations. In addition, in
order to aid the explanation of the L*parameter, another
spectrum of a dark green dye has been included.
■OBTAINED RESULTS WITH STUDENTS
These spreadsheet files have been used by chemistry and
enology students at our university in order to discuss their
experimental results in a laboratory report. Specifically, for
chemistry students the color space parameters of different pH
indicators (phenolphthalein, bromothymol blue, and methyl
orange) were measured at different pH values. Furthermore,
enology students determined the color space coordinates of
different wine and alcoholic beverage samples.
The newly developed Microsoft Excel workbooks described
here, which not only offer more information on the results but
also allow a cell to be colored with an almost identical color to
the real sample, have allowed students to understand better
and improve their discussion of the results in their laboratory
reports.
Two fragments of students’laboratory reports can be
observed in Color-laboratory-reports.pdf, which is included in
the Supporting Information. In the first report, the workbook
was used in order to better understand the effect of different
quantities of polivinylpolipyrrolidone (PVPP) over the color of
red wines. In the second report, the workbook was used in
order to describe the color of a wood-aged spirit. Both reports
have been translated from Spanish to English for better
comprehension.
The files described here were also used in a classroom
explanation as a visual and interactive aid. In all cases, the
results were satisfactory, and students commented that the
sheets provided an easy way to understand color space.
■IN CONCLUSION
Two Microsoft Excel workbooks have been included and
described in this paper, These sheets, based on the
transmittance spectrum of a sample, enable the values of the
color coordinates to be obtained in four different color spaces
(CIE XYZ, CIE L*a*b*, CIE LCH, and RGB).
The first Excel sheet was developed so that students can
understand the most relevant color spaces, as well as to
facilitate the work of teachers when offering explanations for
this topic. This sheet has previously been used in enology and
chemistry lectures at the University of Cá
diz, and it has been
an improvement on the previous teaching methods.
The second Excel sheet was developed so that researchers
working in the laboratory can easily obtain the coordinates of
the color spaces most commonly applied in the scientific
community. For this reason, this sheet provides less
information that the first sheet, although it can be used to
introduce several spectra and thus obtain the color parameters
of several samples at the same time.
■ASSOCIATED CONTENT
*
SSupporting Information
The Supporting Information is available on the ACS
Publications website at DOI: 10.1021/acs.jchemed.7b00681.
Two extracts from students’laboratory reports (PDF)
Microsoft Excel workbook for colorimetric mathematical
treatment for researchers (Template-5 nm-for-Research-
ers.xlsm), Microsoft Excel workbook for colorimetric
mathematical treatment and explanations for students
and teachers (Template-5 nm-for-Students.xlsm), Mi-
crosoft Excel workbook with examples of sample spectra
(Examples.xls), and file summarizing the formulas and
explanations of all related color spaces (Color-spaces-
formulas.pdf) (ZIP)
■AUTHOR INFORMATION
Corresponding Author
*E-mail: valme.garcia@uca.es.
ORCID
M. J. Delgado-Gonzá
lez: 0000-0003-3464-2397
M. V. García-Moreno: 0000-0002-5718-3296
Notes
The authors declare no competing financial interest.
■ACKNOWLEDGMENTS
We would like to thank S. Delgado for use of her laboratory
reports.
■REFERENCES
(1) Crews, P. C. The fading rates of some natural dyes. Stud.
Conserv. 1987,32 (2), 65−72.
(2) Grabchev, I.; Meallier, P.; Konstantinova, T.; Popova, M.
Synthesis of some unsaturated 1,8-naphthalimide dyes. Dyes Pigm.
1995,28 (1), 41−46.
(3) Chen, C. C.; Wang, I. J. Synthesis of some pyridone azo dyes
from 1-substituted 2-hydroxy-6-pyridone derivatives and their colour
assessment. Dyes Pigm. 1991,15 (1), 69−82.
(4) Vankar, P. S.; Shanker, R. Ecofriendly ultrasonic natural dyeing
of cotton fabric with enzyme pretreatments. Desalination 2008,230
(1−3), 62−69.
(5) Senthilkumar, M. Modelling of CIELAB values in vinyl Sulphone
dye application using feed-forward neural networs. Dyes Pigm. 2007,
75 (2), 356−361.
(6) Barbosa, J.; Bosch, E.; Roses, M. Neutralisation indicators in 2-
methylpropan-2-ol: their pKa values and chromatic parameters of
transition ranges. Analyst 1987,112 (2), 179−184.
(7) Fernandez, A. M. C.; Chozas, M. G. Colour specification of
pyridine-2-aldehyde and 6-methylpyridine-2-aldehyde p-nitrophenyl-
hydrazones as indicators for pH determination. Talanta 1987,34 (7),
673−676.
(8) Kim, M. J.; Jung, S. W.; Park, H. R.; Lee, S. J. Selection of an
optimum pH-indicator for developing lactic acid bacteria-based time-
temperature integrators (TTI). J. Food Eng. 2012,113 (3), 471−478.
(9) Gil-Muñoz, R.; Gómez-Plaza, E.; Martínez, A.; López-Roca, J. M.
Evolution of the CIELAB and other spectrophotometric parameters
during wine fermentation. Influence of some pre and postfermentative
factors. Food Res. Int. 1997,30 (9), 699−705.
(10) Delgado-Gonzá
lez, M. J.; Sá
nchez-Guillé
n, M. M.; García-
Moreno, M. V.; Rodríguez-Dodero, M. C.; García-Barroso, C.;
Guillé
n- Sá
nchez, D. A. Study of a laboratory-scaled new method for
the accelerated continuous ageing of wine spirits by applying
ultrasound energy. Ultrason. Sonochem. 2017,36, 226−235.
(11) Pé
rez-Magariño, S.; Gonzá
lez-Sanjosé
, M. L. Application of
absorbance values used in wineries for estimating CIELAB parameters
in red wines. Food Chem. 2003,81 (2), 301−306.
(12) OIV. Determination of Chromatic Characteristics According to
CIELab (Resolution Oeno 1/2006), Method OIV-MA-AS2-11:2006.
In Compendium of International Methods of Wine and Must Analysis;
Paris, 2014; Vol. 1;pp1−16.
(13) Chaloupka, K.; Varanka-Martin, M.; Pringle, D. L. Crepe Paper
Colorimetry. J. Chem. Educ. 1995,72 (8), 722.
(14) Campos, A. R.; Knutson, C. M.; Knutson, T. R.; Mozzetti, A.
R.; Haynes, C. L.; Penn, R. L. Quantifying Gold Nanoparticle
Journal of Chemical Education Technology Report
DOI: 10.1021/acs.jchemed.7b00681
J. Chem. Educ. 2018, 95, 1885−1889
1888

Concentration in a Dietary Supplement Using Smartphone Color-
imetry and Google Applications. J. Chem. Educ. 2016,93 (2), 318−
321.
(15) CIE. Colorimetry CIE 15:2004, 3rd ed.; Vienna, Austria, 2004.
(16) ISO. ISO 11664-2:2007/CIE S 014-2/E:2006. Joint ISO/CIE
Standard: ColorimetryPart 2: CIE Standard Illuminants; Switzerland,
2007.
(17) ISO. ISO 11664-4:2008/CIE S 014-4/E:2007. Joint ISO/CIE
Standard: ColorimetryPart 4: CIE 1976 L*a*b*Colour Space;
Switzerland, 2008.
(18) ISO. ISO 11664-3:2012/CIE S 014-3/E:2011. Joint ISO/CIE
Standard: ColorimetryPart 3: CIE Tristimulus Values; Switzerland,
2012.
Journal of Chemical Education Technology Report
DOI: 10.1021/acs.jchemed.7b00681
J. Chem. Educ. 2018, 95, 1885−1889
1889