Convert a list of RGB values to CMYK

[jdonnelly]

I have a list of RGB values that I would like to convert to CMYK values. I've located some help that will requires the values be entered manually. I would like to read the list and convert.

Thanks!

 

[Rick Rothstein]

Can you tell us where your RGB values are located and tell us where you want the CYMK values to be placed and whether you want a formula solution or VBA solution?

 

[Rick Rothstein]

Where did you go? You were online when I asked my question. Okay, I'll assume your RGB values are in separate columns (Columns A, B, C for R, G, B respectively) and that your output is to go in Columns D, E, F, G (for C, M, Y, K respectively). Also, I am assuming Row 1 is used for headers. With that said, put these formulas in the indicated cells and copy them down...

D2: =(1-$G2-A2/255)/(1-$G2)

E2: =(1-$G2-B2/255)/(1-$G2)

F2: =(1-$G2-C2/255)/(1-$G2)

G2: =1-MAX(A2/255,B2/255,C2/255)

Make sure to format the cells in Columns D, E, F, G as Percentage with zero decimal points.

 

[Jon Peltier]

Rick, we need to prevent to prevent #DIV/0 errors for black, that is, RGB(0, 0, 0). It might be as simple as

D2: =(1-$G2-A2/255)/(1-IF($G2=1,0,$G2))
E2: =(1-$G2-B2/255)/(1-IF($G2=1,0,$G2))
F2: =(1-$G2-C2/255)/(1-IF($G2=1,0,$G2))

 

[Rick Rothstein]

Good catch Jon. If I had noticed that problem, I probably would have opted to use IFERROR...

D2: =IFERROR((1-$G2-A2/255)/(1-$G2),)
E2: =IFERROR((1-$G2-B2/255)/(1-$G2),)
F2: =IFERROR((1-$G2-C2/255)/(1-$G2),)

 

[Jon Peltier]

Yep, that works, too.

 

[jdonnelly]

Rick and Jon, Thank you both for your assistance with this issue. Rick, you assumptions regarding how the columns are setup are correct. We now have a solution in place for this challenge.

Thanks!

 

[TallDude]

Convert RGB to CMYK in single line of code.

I hate looking at sloppy code, and writing it makes me giggle, but I must provide this, because I used your code to make mine. Stood on the shoulders of giants so to speak.

This first code will convert RGB in (R,G,B) format into C #% M#% Y#% K#%
Example:
In cell A1 enter (100,28,22)

In cell B1 enter

="C "&ROUND((1-(1-MAX(MID(A1|2|FIND(","|A1)-2)/255|MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255|MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255))-MID(A1|2|FIND(","|A1)-2)/255)/(1-(1-MAX(MID(A1|2|FIND(","|A1)-2)/255|MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255|MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255)))*100|0)&"% M "&ROUND((1-(1-MAX(MID(A1|2|FIND(","|A1)-2)/255|MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255|MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255))-MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255)/(1-(1-MAX(MID(A1|2|FIND(","|A1)-2)/255|MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255|MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255)))*100|0)&"% Y "&ROUND(((1-(1-MAX(MID(A1|2|FIND(","|A1)-2)/255|MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255|MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255))-MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255)/(1-(1-MAX(MID(A1|2|FIND(","|A1)-2)/255|MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255|MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255))))*100|0)&"% K "&ROUND((1-MAX(MID(A1|2|FIND(","|A1)-2)/255|MID(A1|FIND(","|A1)+1|FIND(","|A1|FIND(","|A1)+1)-FIND(","|A1)-1)/255|MID(A1|FIND(","|A1|FIND(","|A1)+1)+1|FIND(")"|A1)-FIND(","|A1|FIND(","|A1)+1)-1)/255))*100|0)

Cell B2 shows C 0% M 72% Y 78% K 61

Or, alternately, if you were looking for a CMYK color simply enclosed in parentheses (C,M,Y,K), like the RGB color
Use this code in B1:

="("&ROUND((1-(1-MAX(MID(A2|2|FIND(","|A2)-2)/255|MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255|MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255))-MID(A2|2|FIND(","|A2)-2)/255)/(1-(1-MAX(MID(A2|2|FIND(","|A2)-2)/255|MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255|MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255)))*100|0)&","&ROUND((1-(1-MAX(MID(A2|2|FIND(","|A2)-2)/255|MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255|MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255))-MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255)/(1-(1-MAX(MID(A2|2|FIND(","|A2)-2)/255|MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255|MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255)))*100|0)&","&ROUND(((1-(1-MAX(MID(A2|2|FIND(","|A2)-2)/255|MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255|MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255))-MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255)/(1-(1-MAX(MID(A2|2|FIND(","|A2)-2)/255|MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255|MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255))))*100|0)&","&ROUND((1-MAX(MID(A2|2|FIND(","|A2)-2)/255|MID(A2|FIND(","|A2)+1|FIND(","|A2|FIND(","|A2)+1)-FIND(","|A2)-1)/255|MID(A2|FIND(","|A2|FIND(","|A2)+1)+1|FIND(")"|A2)-FIND(","|A2|FIND(","|A2)+1)-1)/255))*100|0)&")"

Cell B2 shows (0,72,78,61)

BULK

Put a list of RGB colors in the A column in (R,G,B) format.
Select either of the formulas for B1.
Select cell B1
Use the fill handle to fill the formula from B1 down parallel with the last RGB value.



Conversion complete.