Delta E(cmc) formula

[Michelle7890]

Does anyone out there have the Delta E cmc formula for excel? I am trying to
set up a spreadsheet that compares colors of test samples to a reference
sample in a spreadsheet. But my excel skills are not this good.
Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|

 

[Shane Devenshire]

Hi,

are DL, ISL, DC, ab, cSC, DH, SH and so on variables? Or is ab a*b for
example? The reason I ask is you have "unless h is between 164 and 345
degrees" but the only h you have is part of DH, SH, or hab.

How are all the variables laid out in the spreadsheet. Show us something,

A
1 SL
2 L
3 hab
4 ab
5 cSC
....

 

[Michelle7890]

Hi Shane,
The variables are based on the reference sample so I was hoping to determine
the formula for the entire sheet based on the reference. The spread sheet so
far has the reference and then the samples tested L, a and b. Here are the
fist few lines.
L a b
Master LT 28.63 2.04 4.08 28.99
Color 303 DARK BRONZE
Check L a b Delta E
1 1st 26.09 2.6 5.83 26.86
2nd 25.94 2.52 5.61 26.66
3rd 26.16 2.38 5.33 26.80
I was thinking it might be best to write a formula first for the h based on
the master data to determine which formula to use below. The formula for h is
the tangent to the -1 of (b/a) (this b and a are of the master reading). I
have no idea how to write this formula in excel.
Then from this I was hoping to be able to determine what T formula to use.
S sub L is also based on the L value of the master sample.
This website breaks down the formula really well but I just do not know how
to do these functions in excel (for example tan and cos and to the -1 power)
www.brucelindbloom.com/Eqn_DeltaE_CMC.html
Thank you so much,
Michelle

Hi,

are DL, ISL, DC, ab, cSC, DH, SH and so on variables? Or is ab a*b for
example? The reason I ask is you have "unless h is between 164 and 345
degrees" but the only h you have is part of DH, SH, or hab.

How are all the variables laid out in the spreadsheet. Show us something,

A
1 SL
2 L
3 hab
4 ab
5 cSC
...


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Does anyone out there have the Delta E cmc formula for excel? I am trying to
set up a spreadsheet that compares colors of test samples to a reference
sample in a spreadsheet. But my excel skills are not this good.
Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|

 

[Dana DeLouis]

Hi. Just some thoughts...

CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

According to your link, the above equation is a little off.
You are missing the variable "l" and "c", but you merged them into the
variable name. I believe it should be...

CMC = [(DL* / (l*SL) )^2 + (DC / (c*SC) )^2 + (DH / SH)^2]^0.5

Note that there is no "ab" variable.

the tangent to the -1 of (b/a)

This is a math notation for the Arc Tangent, or inverse.
In Excel, it is the function ATAN()
Your link recommends using the function ATAN2() instead.

What are the values of l & c in your equations?

= = =
Dana DeLouis

Hi Shane,
The variables are based on the reference sample so I was hoping to determine
the formula for the entire sheet based on the reference. The spread sheet so
far has the reference and then the samples tested L, a and b. Here are the
fist few lines.
L a b
Master LT 28.63 2.04 4.08 28.99
Color 303 DARK BRONZE
Check L a b Delta E
1 1st 26.09 2.6 5.83 26.86
2nd 25.94 2.52 5.61 26.66
3rd 26.16 2.38 5.33 26.80
I was thinking it might be best to write a formula first for the h based on
the master data to determine which formula to use below. The formula for h is
the tangent to the -1 of (b/a) (this b and a are of the master reading). I
have no idea how to write this formula in excel.
Then from this I was hoping to be able to determine what T formula to use.
S sub L is also based on the L value of the master sample.
This website breaks down the formula really well but I just do not know how
to do these functions in excel (for example tan and cos and to the -1 power)
www.brucelindbloom.com/Eqn_DeltaE_CMC.html
Thank you so much,
Michelle

Hi,

are DL, ISL, DC, ab, cSC, DH, SH and so on variables? Or is ab a*b for
example? The reason I ask is you have "unless h is between 164 and 345
degrees" but the only h you have is part of DH, SH, or hab.

How are all the variables laid out in the spreadsheet. Show us something,

A
1 SL
2 L
3 hab
4 ab
5 cSC
...


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Does anyone out there have the Delta E cmc formula for excel? I am trying to
set up a spreadsheet that compares colors of test samples to a reference
sample in a spreadsheet. But my excel skills are not this good.
Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|

 

[Michelle7890]

The l and c are both equal to one- it is a 1:1 ratio. The a and the b are the
measurements taken with the equipment in my spreadsheet below and are used to
calculate the delta C by taking the square root of the sample a squared plus
the sample b squared and the square root of reference a squared plus
reference b squared.

Hi. Just some thoughts...

CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

According to your link, the above equation is a little off.
You are missing the variable "l" and "c", but you merged them into the
variable name. I believe it should be...

CMC = [(DL* / (l*SL) )^2 + (DC / (c*SC) )^2 + (DH / SH)^2]^0.5

Note that there is no "ab" variable.

the tangent to the -1 of (b/a)

This is a math notation for the Arc Tangent, or inverse.
In Excel, it is the function ATAN()
Your link recommends using the function ATAN2() instead.

What are the values of l & c in your equations?

= = =
Dana DeLouis

Hi Shane,
The variables are based on the reference sample so I was hoping to determine
the formula for the entire sheet based on the reference. The spread sheet so
far has the reference and then the samples tested L, a and b. Here are the
fist few lines.
L a b
Master LT 28.63 2.04 4.08 28.99
Color 303 DARK BRONZE
Check L a b Delta E
1 1st 26.09 2.6 5.83 26.86
2nd 25.94 2.52 5.61 26.66
3rd 26.16 2.38 5.33 26.80
I was thinking it might be best to write a formula first for the h based on
the master data to determine which formula to use below. The formula for h is
the tangent to the -1 of (b/a) (this b and a are of the master reading). I
have no idea how to write this formula in excel.
Then from this I was hoping to be able to determine what T formula to use.
S sub L is also based on the L value of the master sample.
This website breaks down the formula really well but I just do not know how
to do these functions in excel (for example tan and cos and to the -1 power)
www.brucelindbloom.com/Eqn_DeltaE_CMC.html
Thank you so much,
Michelle

Hi,

are DL, ISL, DC, ab, cSC, DH, SH and so on variables? Or is ab a*b for
example? The reason I ask is you have "unless h is between 164 and 345
degrees" but the only h you have is part of DH, SH, or hab.

How are all the variables laid out in the spreadsheet. Show us something,

A
1 SL
2 L
3 hab
4 ab
5 cSC
...


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

Does anyone out there have the Delta E cmc formula for excel? I am trying to
set up a spreadsheet that compares colors of test samples to a reference
sample in a spreadsheet. But my excel skills are not this good.
Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|

 

[Dana DeLouis]

L a b Delta E

Master 28.63 2.04 4.08 28.99

1st 26.09 2.6 5.83 26.86

Hi. ok. "lSL" is really "l*SL"
If I understand the equation, Your "Master" E is 28.99.
The "Delta E" for another reference (#1) should be a negative number
that is added to 28.99 to arrive at 26.86?
(E + Delta E = New E)

The way I understand it, your Delta E equation is a Square Root, and is
always positive. Hence, there is no solution.
Perhaps you can give some example on what is expected. Did you
calculate 26.86, or is that the expected answer?

= = =
Dana DeLouis

The l and c are both equal to one- it is a 1:1 ratio. The a and the b are the
measurements taken with the equipment in my spreadsheet below and are used to
calculate the delta C by taking the square root of the sample a squared plus
the sample b squared and the square root of reference a squared plus
reference b squared.

Hi. Just some thoughts...

CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

According to your link, the above equation is a little off.
You are missing the variable "l" and "c", but you merged them into the
variable name. I believe it should be...

CMC = [(DL* / (l*SL) )^2 + (DC / (c*SC) )^2 + (DH / SH)^2]^0.5

Note that there is no "ab" variable.

the tangent to the -1 of (b/a)

This is a math notation for the Arc Tangent, or inverse.
In Excel, it is the function ATAN()
Your link recommends using the function ATAN2() instead.

What are the values of l & c in your equations?

= = =
Dana DeLouis

Hi Shane,
The variables are based on the reference sample so I was hoping to determine
the formula for the entire sheet based on the reference. The spread sheet so
far has the reference and then the samples tested L, a and b. Here are the
fist few lines.
L a b
Master LT 28.63 2.04 4.08 28.99
Color 303 DARK BRONZE
Check L a b Delta E
1 1st 26.09 2.6 5.83 26.86
2nd 25.94 2.52 5.61 26.66
3rd 26.16 2.38 5.33 26.80
I was thinking it might be best to write a formula first for the h based on
the master data to determine which formula to use below. The formula for h is
the tangent to the -1 of (b/a) (this b and a are of the master reading). I
have no idea how to write this formula in excel.
Then from this I was hoping to be able to determine what T formula to use.
S sub L is also based on the L value of the master sample.
This website breaks down the formula really well but I just do not know how
to do these functions in excel (for example tan and cos and to the -1 power)
www.brucelindbloom.com/Eqn_DeltaE_CMC.html
Thank you so much,
Michelle


:

Hi,

are DL, ISL, DC, ab, cSC, DH, SH and so on variables? Or is ab a*b for
example? The reason I ask is you have "unless h is between 164 and 345
degrees" but the only h you have is part of DH, SH, or hab.

How are all the variables laid out in the spreadsheet. Show us something,

A
1 SL
2 L
3 hab
4 ab
5 cSC
...


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

Does anyone out there have the Delta E cmc formula for excel? I am trying to
set up a spreadsheet that compares colors of test samples to a reference
sample in a spreadsheet. But my excel skills are not this good.
Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|

 

[Dana DeLouis]

Hi. At this web site...

http://www.brucelindbloom.com/index.html?Eqn_DeltaE_CIE94.html

If I fill in the first row with the following data.
28.63 2.04 4.08

and the second row with this data...
26.09 2.6 5.83

both that web site, and my excel calculate CMC(1:1) as 3.8379.
(based on the equations in the link)

Since you haven't received any equations, perhaps you can rephrase your
question / equation. I am not sure what 28.99 and 26.86 are, or how
they are calculated.

= = =
HTH
Dana DeLouis

L a b Delta E
Master 28.63 2.04 4.08 28.99

1st 26.09 2.6 5.83 26.86

Hi. ok. "lSL" is really "l*SL"
If I understand the equation, Your "Master" E is 28.99.
The "Delta E" for another reference (#1) should be a negative number
that is added to 28.99 to arrive at 26.86?
(E + Delta E = New E)

The way I understand it, your Delta E equation is a Square Root, and is
always positive. Hence, there is no solution.
Perhaps you can give some example on what is expected. Did you
calculate 26.86, or is that the expected answer?

= = =
Dana DeLouis

The l and c are both equal to one- it is a 1:1 ratio. The a and the b
are the measurements taken with the equipment in my spreadsheet below
and are used to calculate the delta C by taking the square root of the
sample a squared plus the sample b squared and the square root of
reference a squared plus reference b squared.

Hi. Just some thoughts...

CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

According to your link, the above equation is a little off.
You are missing the variable "l" and "c", but you merged them into
the variable name. I believe it should be...

CMC = [(DL* / (l*SL) )^2 + (DC / (c*SC) )^2 + (DH / SH)^2]^0.5

Note that there is no "ab" variable.

the tangent to the -1 of (b/a)

This is a math notation for the Arc Tangent, or inverse.
In Excel, it is the function ATAN()
Your link recommends using the function ATAN2() instead.

What are the values of l & c in your equations?

= = =
Dana DeLouis



Michelle7890 wrote:
Hi Shane,
The variables are based on the reference sample so I was hoping to
determine the formula for the entire sheet based on the reference.
The spread sheet so far has the reference and then the samples
tested L, a and b. Here are the fist few lines.
L a b
Master LT 28.63 2.04 4.08 28.99
Color 303 DARK BRONZE
Check L a b Delta E
1 1st 26.09 2.6 5.83 26.86
2nd 25.94 2.52 5.61 26.66
3rd 26.16 2.38 5.33 26.80
I was thinking it might be best to write a formula first for the h
based on the master data to determine which formula to use below.
The formula for h is the tangent to the -1 of (b/a) (this b and a
are of the master reading). I have no idea how to write this
formula in excel. Then from this I was hoping to be able to
determine what T formula to use. S sub L is also based on the L
value of the master sample. This website breaks down the formula
really well but I just do not know how to do these functions in
excel (for example tan and cos and to the -1 power)
www.brucelindbloom.com/Eqn_DeltaE_CMC.html
Thank you so much,
Michelle

:

Hi,
are DL, ISL, DC, ab, cSC, DH, SH and so on variables? Or is ab a*b
for example? The reason I ask is you have "unless h is between 164
and 345 degrees" but the only h you have is part of DH, SH, or hab.

How are all the variables laid out in the spreadsheet. Show us
something,
A
1 SL
2 L
3 hab
4 ab
5 cSC
...


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

Does anyone out there have the Delta E cmc formula for excel? I
am trying to set up a spreadsheet that compares colors of test
samples to a reference sample in a spreadsheet. But my excel
skills are not this good. Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|

 

[Robert]

If you find out the answer to this please let me know I am working on the
same problem.

 

[Robert]

I finnally got it worked out. Say your standard is in cells A1 for L., A2 for
a., and A3 for your b. Your test sample is in cells B1 for L., B2 for a., and
B3 for the b.
Here is the formula I am useing note there may be other ways to write it but
I know this works.
=(((B1-A1)*(B1-A1))+((B2-A2)*(B2-A2))+((B3-A3)*(B3-A3)))^(1/2)
It could probably be written this way as
well=(((B1-A1)^2))+((B2-A2)^2))+((B3-A3)^2))^(1/2)

 

[Robert]

I finnally got it worked out. Say your standard is in cells A1 for L., A2 for
a., and A3 for your b. Your test sample is in cells B1 for L., B2 for a., and
B3 for the b.
Here is the formula I am useing note there may be other ways to write it but
I know this works.
=(((B1-A1)*(B1-A1))+((B2-A2)*(B2-A2))+((B3-A3)*(B3-A3)))^(1/2)
It could probably be written this way as
well=(((B1-A1)^(2))+((B2-A2)^(2))+((B3-A3)^(2)))^(1/2)

Does anyone out there have the Delta E cmc formula for excel? I am trying to
set up a spreadsheet that compares colors of test samples to a reference
sample in a spreadsheet. But my excel skills are not this good.
Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|

 

[Dana DeLouis]

=(((B1-A1)^(2))+((B2-A2)^(2))+((B3-A3)^(2)))^(1/2)

Just to mention another way as a worksheet function: (I find easier to read)

=SQRT(SUMSQ(B1-A1,B2-A2,B3-A3))


Just to mention...you are using the formula..
[(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

with a variable SL = 0.511

It was mentioned here a while ago that the equation does not appear
correct. There is no variable "lSL"
It appears to be something like:

(DL * / (L*SL))^2 ...etc
(C*SC)...etc

I may be wrong thou...

= = = =
Dana DeLouis

I finnally got it worked out. Say your standard is in cells A1 for L., A2 for
a., and A3 for your b. Your test sample is in cells B1 for L., B2 for a., and
B3 for the b.
Here is the formula I am useing note there may be other ways to write it but
I know this works.
=(((B1-A1)*(B1-A1))+((B2-A2)*(B2-A2))+((B3-A3)*(B3-A3)))^(1/2)
It could probably be written this way as
well=(((B1-A1)^(2))+((B2-A2)^(2))+((B3-A3)^(2)))^(1/2)

Does anyone out there have the Delta E cmc formula for excel? I am trying to
set up a spreadsheet that compares colors of test samples to a reference
sample in a spreadsheet. But my excel skills are not this good.
Thanks,
Michelle
CMC = [(DL* / lSL)^2 + (DC*ab / cSC)^2 + (DH*ab / SH)^2]^0.5

where:

SL = 0.040975L* / (1+0.01765L*) unless L < 16 when SL = 0.511
SC = 0.0638C*ab / (1+0.0131C*ab) + 0.638
SH = (fT + 1 - f)SC

where:

f = [(C*ab)^4 / {(C*ab)^4 + 1900)}]^0.5
T = 0.36 + |0.4 cos(hab + 35)|
unless h is between 164 and 345 degrees when
T = 0.56 + |0.2 cos(hab + 168)|