[Solved] Determine best match in RGB color matrix

Discuss the spreadsheet application
Post Reply
frankcohen
Posts: 3
Joined: Fri Apr 30, 2021 3:18 am

[Solved] Determine best match in RGB color matrix

Post by frankcohen »

Hi OpenOfficeistas! I am color blind and have a technique for selecting colors. I am seeking an approach in the Calc part of OpenOffice to find the nearest Red, Green, and Blue values from a prexisting table of paints. B7:B18 in the attached spreadsheet are the paint colors I have. I can use a smartphone camera to detect a color. For example, Red value of 10, green value of 191 and blue value of 163 (out of 255). I use the INDEX and MATCH functions to find the paint color nearest my chosen color. For example, in the attached spreadsheet the Red value of 4 is nearest the 206 value on row 10. The issue is how to detect the nearest value from all 3 colors? For example, the row that is nearest a Red value of 10, Green value of 191, and Blue value of 163. -Frank
Attachments
Colorman.ods
(14.2 KiB) Downloaded 110 times
Last edited by MrProgrammer on Fri May 07, 2021 9:30 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
Hi OpenOffice-istas, I'm on MacOS 12.3.1, OpenOffice 4.1.8
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Matrix find

Post by FJCC »

Do you want to find the row with the smallest total deviation from your Goal RGB values? In the attached file, I calculated the sum of the absolute deviations of each available color from the goal. I do not know if a linear sum is the right measure. In any case, I then used INDEX and MATCH in E4 to return the Color Choices value of the row with the smallest deviation. In the example given, the chosen row's Red value is so far from the target that the match is very poor, but I suppose you have a larger pallet to work with.
Attachments
ColorMatch.ods
(11.81 KiB) Downloaded 117 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
frankcohen
Posts: 3
Joined: Fri Apr 30, 2021 3:18 am

Re: Matrix find

Post by frankcohen »

Thank you FJCC. I applied your answer to the attached spreadsheet. You're correct that I have a larger palette of colors to work from. I am using the Koh-I-Noor Watercolor Wheel Stack Pack (8 Trays x 6 Colors) at https://amzn.to/33Ibyzy. I came up with a system to label each tray and easy color. I used a smartphone to identify the RGB colors for each color in the pack. This technique seems to work well. I may futz with it some more and add some weighting to the colors. Thank you for your help! -Frank
Attachments
Colorman Worksheet.ods
(17.83 KiB) Downloaded 115 times
Hi OpenOffice-istas, I'm on MacOS 12.3.1, OpenOffice 4.1.8
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Matrix find

Post by MrProgrammer »

frankcohen wrote:I can use a smartphone camera to detect a color.
If you are using MacOS, perhaps it would be easier for you to use the Digital Color Meter application which comes pre-installed. Here I have used it to determine the color of a pixel in the N in Colorman.ods. One can make the aperture larger to average the color over a set of pixels.
DCM.jpg
FJCC wrote:In the attached file, I calculated the sum of the absolute deviations of each available color from the goal. I do not know if a linear sum is the right measure.
Another measure is the sum of the squares of the deviations. There is a Calc function for that. Cell I8 of your attachment can use =SUMXMY2($D$2:$F$2;D8:F8). XMY2 means "(X Minus Y)^2.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Mac FAQ
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
frankcohen
Posts: 3
Joined: Fri Apr 30, 2021 3:18 am

Re: Matrix find

Post by frankcohen »

Thank you! I added the Sum of Squares calculation to the attached spreadsheet. If you want to see the color system I developed it is here https://drive.google.com/file/d/1t5Aj7- ... sp=sharing. I will try the Sum of Squares on some previous color selections. Thanks! -Frank
Attachments
Colorman Worksheet.ods
(21.14 KiB) Downloaded 121 times
Hi OpenOffice-istas, I'm on MacOS 12.3.1, OpenOffice 4.1.8
Post Reply