none
Tint application algorithm not consistent with MS Excel RRS feed

  • Question

  • Dear MS team,

    I have an excel file that contains some background colors in themes. I can calculate every color properly, except for one.

    The color is defined as FFED7D31 and a tint is applied to it with value 0.79998168889431442.

    I am using the reference implementation for conversion between RGB and HLS colors given here:

    https://support.microsoft.com/en-us/help/29240/how-to-converting-colors-between-rgb-and-hls-hbs

    The problem is that due to rounding errors this conversion is not color preserving. Converting FFED7D31 to HLS and back to RGB gives FFED7D32. Further more, it seems that this is not the code that runs under Excel. This results in slight differences in colors when the calculation is carried out with this algorithm, compared to what Excel gets.

    The end result is that applying the given tint on the given color gives me the RGB color FFFBE6D6. While if I open the document in excel and use the color picker in paint on a screen shot (or query the color of a cell using interop) I get FFFCE4D6. Note that there is a slight difference between these colors. The R value differs with 1, and the G value with 2.

    Rounding errors come from specific implementation details in the algorithms (variable types, or something similar) and it would be important to know how these colors are calculated precisely.

    Is it possible to get an exact description of the algorithm that calculates the tinted colors in Office?

    If this is not possible, we can 'assume' that every two color with total distance at most 4 in RGB coordinates are equal, but that would not be a nice and reliable solution.

    Thanks in advance,

    Sándor Kolumbán

    31200

    Saturday, September 23, 2017 1:34 PM

Answers

  • We worked with Sandor through dochelp and addressed the color differencing issue. The HLSMAX has to be 240 when tint is used and 255 when tint is not used to do HLS to RGB transform.

    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Wednesday, October 18, 2017 3:41 PM

All replies

  • Hi Sandor, 

    Thanks for your question about the background color calculations for SpreadsheetML. One of the Open Specications team will respond to assist you with this.

    Best regards,
    Tom Jebo 
    Sr Escalation Engineer
    Microsoft Open Specifications Support

    Saturday, September 23, 2017 2:20 PM
    Moderator
  • Hello Sandor -

    I'm researching this for you and will get back.

    Thanks


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Monday, September 25, 2017 7:30 PM
  • Hello Sandor - 

    Can you please drop me an email to dochelp at Microsoft dot com since I need to verify certain things on algo. with you? 

    PS: Link to similar discussion in past - https://social.msdn.microsoft.com/Forums/en-US/e9d8c136-6d62-4098-9b1b-dac786149f43/excel-color-tint-algorithm-incorrect?forum=os_binaryfile 

    Thanks


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Monday, September 25, 2017 7:56 PM
  • Hello Sandor -

    Let's us know if you still need our help as we didn't hear back from you.

    Following update on this thread (https://social.msdn.microsoft.com/Forums/en-US/e9d8c136-6d62-4098-9b1b-dac786149f43/excel-color-tint-algorithm-incorrect?forum=os_binaryfile ) may resolve your query. If so; kindly let us know.

    '

    Thanks, this seems to work. I did have this algorithm being used, but my HLSMAX value was 255, not 240. Also, the documentation in the original link I posted says HLSMAX is 255, but when using the tinting algorithm, you must also use 240. In addition, something that is hinted at by the example, but not said explicitly in that documentation is that after multiplying by (1.0+tint) or (1.0-tint), the result must be truncated (not rounded) before continuing on  with the rest of the equation.

    '

    Thanks


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Thursday, September 28, 2017 5:09 PM
  • Hi Tarun,

    I did change bot the 255-240 issue and I incorporated the truncation as well, but the colors are still off.

    I will compile a small piece of code, so you can check why things are going wrong.  I will send it to you in a mail soon.

    Cheers,

    Sándor Kolumbán

    Monday, October 2, 2017 12:07 PM
  • Sure; Thanks.

    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Tuesday, October 3, 2017 3:39 PM
  • Hi Tarun,

    I have sent you the sample code that has the full algorithm that I harvested from the various msdn sites.

    It contains a small unit test that tries to match the calculated colors with what is coming out from excel.

                // color of row 6, color displayed in excel: R-208, G-206, B-206 = D0CECE
                var tintedColor = applyTint(-0.0999786370433668, "FFE7E6E6");
                Assert.AreEqual("D0CECE", tintedColor);
    
                // color of row 4, color displayed in excel: R-226, G-239, B-218 = E2EFDA
                // this color is calculated correctly if the truncateAfterMultiplication function is not used to truncate the L value
                tintedColor = applyTint(0.79998168889431442, "FF70AD47");
                Assert.AreEqual("FFE2EFDA", tintedColor);
    
                // color of row 2, color displayed in excel: R-252, G-228, B-214 = FCE4D6
                tintedColor = applyTint(0.79998168889431442, "FFED7D31");
                Assert.AreEqual("FFFCE4D6", tintedColor);

    I sent the code and the sample file to you to dochelp. Let me know if you can adwise what is going wrong when I transfer the c++ code to C#.

    Best regards,

      Sándor Kolumbán

    Sunday, October 8, 2017 12:50 PM
  • We are working with Sandor offline and will update the thread once we are done with our analysis.

    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Monday, October 16, 2017 10:53 PM
  • Stay tuned, we are closing in on the remaining bits of the issue.
    Tuesday, October 17, 2017 4:30 PM
  • We worked with Sandor through dochelp and addressed the color differencing issue. The HLSMAX has to be 240 when tint is used and 255 when tint is not used to do HLS to RGB transform.

    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Wednesday, October 18, 2017 3:41 PM