To blend hex color, take the average (round to 0) of respective Rs, Gs and Bs of two colors and generate the Hex color for the result. Color1 = 236, 121, 253 Color2 = 140, 21, 171 Average of respective Rs, Gs and Bs = 188, 71, 212
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 425
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Blend RGB Colors into Hex with Power Query
Power Query solution 1 for Blend RGB Colors into Hex, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
C1 = List.Transform(Text.Split([Color1], ", "), Number.From),
C2 = List.Transform(Text.Split([Color2], ", "), Number.From),
Z = List.Zip({C1, C2}),
O = List.Transform(Z, (f) => Number.ToText(Number.RoundUp(List.Average(f), 0), "X2")),
R = "#" & Text.Combine(O)
][R]
)
in
ReturnPower Query solution 2 for Blend RGB Colors into Hex, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x, y) =>
let
C1 = x,
C2 = y,
L = List.Transform,
a = L(Text.Split(C1, ", "), Number.From),
b = L(Text.Split(C2, ", "), Number.From),
c = L({0 .. List.Count(b) - 1}, each {a{_}} & {b{_}}),
d = L(c, each Number.RoundUp(List.Average(_))),
e = L(d, each Number.ToText(_, "X")),
f = L(e, each if Text.Length(_) = 1 then "0" & _ else _),
g = "#" & Text.Combine(f)
in
g,
Sol = Table.AddColumn(S, "Answer Expected", each Fx([Color1], [Color2]))
in
SolPower Query solution 3 for Blend RGB Colors into Hex, proposed by Arnaud Duvernois:
let
Source = Excel.CurrentWorkbook(){[Name = "tSourceXl425"]}[Content],
List1 = List.TransformMany(Source[Color1], (s) => Text.Split(s, ", "), (s, y) => Number.From(y)),
List2 = List.TransformMany(Source[Color2], (s) => Text.Split(s, ", "), (s, y) => Number.From(y)),
ListAverage = List.Transform(List.Zip({List1, List2}), List.Average),
Split = List.Split(List.Transform(ListAverage, each Number.ToText(Number.RoundUp(_, 0), "X2")), 3),
Combine = Table.FromColumns(
{List.Transform(Split, each "#" & Text.Combine(_))},
{"Answer expected"}
)
in
CombineSolving the challenge of Blend RGB Colors into Hex with Excel
Excel solution 1 for Blend RGB Colors into Hex, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10&"/"&B2:B10,
LAMBDA(a,
CONCAT("#",
DEC2HEX((MMULT(
{1,
1},
--TEXTSPLIT(
a,
", ",
"/"
)
)+1)/2,
2))))Excel solution 2 for Blend RGB Colors into Hex, proposed by Rick Rothstein:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
"#"&CONCAT(DEC2HEX((1+TEXTSPLIT(
a,
","
)+TEXTSPLIT(
b,
","
))/2,
2))))Excel solution 3 for Blend RGB Colors into Hex, proposed by John V.:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
CONCAT(
"#",
DEC2HEX(
-INT(
BYCOL(
-TEXTSPLIT(
a&"-"&b,
", ",
"-"
),
SUM
)/2
),
2
)
)
)
)Excel solution 4 for Blend RGB Colors into Hex, proposed by محمد حلمي:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
CONCAT(
"#",
RIGHT(
0&DEC2HEX(
-BYCOL(
VSTACK(
-TEXTSPLIT(
a,
","
),
-TEXTSPLIT(
b,
","
)
),
LAMBDA(
a,
ROUNDUP(
AVERAGE(
a
),
)
)
)
),
2
)
)
)
)Excel solution 5 for Blend RGB Colors into Hex, proposed by Kris Jaganah:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
v,
w,
"#"&CONCAT(
MAP(
ROUND(
MMULT(
{1,
1},
--TEXTSPLIT(
v&"."&w,
",",
"."
)
)/2,
0
),
LAMBDA(
x,
DEC2HEX(
x,
2
)
)
)
)
)
)Excel solution 6 for Blend RGB Colors into Hex, proposed by Julian Poeltl:
=BYROW(A2:B10,
LAMBDA(c,
LET(O,
TEXTSPLIT(
TAKE(
c,
,
1
),
", "
),
T,
TEXTSPLIT(
TAKE(
c,
,
-1
),
", "
),
CONCAT("#",
DEC2HEX(ROUNDUP((O+T)/2,
0),
2)))))
Shorter and unstructured:
=BYROW(A2:B10,
LAMBDA(A,
CONCAT("#",
DEC2HEX(ROUNDUP((TEXTSPLIT(
TAKE(
A,
,
1
);", "
)+TEXTSPLIT(
TAKE(
A,
,
-1
),
", "
))/2,
0),
2))))Excel solution 7 for Blend RGB Colors into Hex, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
CONCAT("#",
DEC2HEX(ROUNDUP((TEXTSPLIT(
a,
", "
) + TEXTSPLIT(
b,
", "
)) / 2,
0),
2))))Excel solution 8 for Blend RGB Colors into Hex, proposed by Timothée BLIOT:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
"#"&CONCAT(DEC2HEX(ROUND((--TEXTSPLIT(
a,
", "
)+--TEXTSPLIT(
b,
", "
))/2,
0),
2))))Excel solution 9 for Blend RGB Colors into Hex, proposed by Hussein SATOUR:
=MAP(A2:A10,
B2:B10,
LAMBDA(x,
y,
CONCAT("#",
DEC2HEX(ROUND((TEXTSPLIT(
x,
","
)+TEXTSPLIT(
y,
","
))/2,
0),
2))))Excel solution 10 for Blend RGB Colors into Hex, proposed by Sunny Baggu:
=MAP(
A2:A10 & ", " & B2:B10,
LAMBDA(
x,
LET(
_a,
BYROW(
WRAPCOLS(
TEXTSPLIT(
x,
", "
),
3
) + 0,
LAMBDA(
a,
ROUND(
AVERAGE(
a
),
0
)
)
),
_b,
DEC2HEX(
"" & _a
),
"#" & CONCAT(
IF(
LEN(
_b
) < 2,
0 & _b,
_b
)
)
)
)
)Excel solution 11 for Blend RGB Colors into Hex, proposed by Anshu Bantra:
=MAP(
Color1,
Color2,
LAMBDA(C_1,
C_2,
LET(
nums_1,
TEXTSPLIT(
C_1,
", "
),
nums_2,
TEXTSPLIT(
C_2,
", "
),
avg_,
ROUND((nums_1 + nums_2) / 2,
0),
hex_,
DEC2HEX(
avg_
),
"#" & TEXTJOIN(
,
,
IF(
LEN(
hex_
) = 1,
"0" & hex_,
hex_
)
)
)
)
)Excel solution 12 for Blend RGB Colors into Hex, proposed by Charles Roldan:
=BYROW(
A2:B10,
LAMBDA(
x,
"#" & CONCAT(
DEC2HEX(
ROUND(
MMULT(
{1,
1},
--TEXTSPLIT(
TEXTJOIN(
"; ",
,
x
),
", ",
"; "
)
) / 2,
),
2
)
)
)
)Excel solution 13 for Blend RGB Colors into Hex, proposed by Andy Heybruch:
=MAP(A2:A10,
B2:B10,
LAMBDA(_a,
_b,
LET(
_c1,
--TEXTSPLIT(
_a,
","
),
_c2,
--TEXTSPLIT(
_b,
","
),
_rgb,
ROUND((_c1+_c2)/2,
0),
_hex,
DEC2HEX(
_rgb
),
"#"&CONCAT(
IF(
LEN(
_hex
)=1,
"0"&_hex,
_hex
)
))))Excel solution 14 for Blend RGB Colors into Hex, proposed by Pieter de Bruijn:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
CONCAT("#",
DEC2HEX(ROUND((TEXTSPLIT(
a,
","
)+TEXTSPLIT(
b,
","
))/2,
),
2))))Excel solution 15 for Blend RGB Colors into Hex, proposed by Ziad A.:
=BYROW(
A2:B10,
LAMBDA(
r,
"#"&SORT(
JOIN(
,
DEC2HEX(
ROUND(
REDUCE(
,
r,
LAMBDA(
a,
c,
a+SPLIT(
c,
","
)
)
)/2
),
2
)
)
)
)
)Excel solution 16 for Blend RGB Colors into Hex, proposed by Edwin Tisnado:
=MAP(A2:A10,
B2:B10,
LAMBDA(x,
y,
"#"&CONCAT(DEC2HEX(ROUND((TEXTSPLIT(
x,
,
", "
)+TEXTSPLIT(
y,
,
", "
))/2,
),
2))))Excel solution 17 for Blend RGB Colors into Hex, proposed by Songglod P.:
=CONCAT(
"#",
DEC2HEX(
LET(
col_deli,
", ",
color1,
--TEXTSPLIT(
A2,
col_deli
),
color2,
--TEXTSPLIT(
B2,
col_deli
),
nums,
color1+color2,
MAP(
nums,
LAMBDA(
num,
ROUNDUP(
num/2,
0
)
)
)
),
2
)
)Excel solution 18 for Blend RGB Colors into Hex, proposed by Ernesto Vega Castillo:
=MAP(A2:A10,
B2:B10,
LAMBDA(_c1,
_c2,
TEXTJOIN("",
1,
,
CHAR(
35
),
REDUCE("",
LET(x,
TEXTSPLIT(
_c1,
,
","
),
y,
TEXTSPLIT(
_c2,
,
","
),
DEC2HEX(ROUNDUP((x+y)/2,
0),
2)),
LAMBDA(
g,
c,
g&c
)))))Excel solution 19 for Blend RGB Colors into Hex, proposed by Gabriel Raigosa:
="#"&MAP(A2:A10,B2:B10,LAMBDA(a,b,CONCAT(DEC2HEX(ROUND((TEXTSPLIT(a,",")+TEXTSPLIT(b,","))/2,0),2))))
=MAP(A2:A10,B2:B10,LAMBDA(a,b,CONCAT("#",DEC2HEX(ROUND((TEXTSPLIT(a,",")+TEXTSPLIT(b,","))/2,0),2))))
🔹ES:
="#"&MAP(A2:A10,B2:B10,LAMBDA(a,b,CONCAT(DEC.A.HEX(REDONDEAR((DIVIDIRTEXTO(a,",")+DIVIDIRTEXTO(b,","))/2,0),2))))
=MAP(A2:A10,B2:B10,LAMBDA(a,b,CONCAT("#",DEC.A.HEX(REDONDEAR((DIVIDIRTEXTO(a,",")+DIVIDIRTEXTO(b,","))/2,0),2))))Excel solution 20 for Blend RGB Colors into Hex, proposed by Hussain Ali Nasser:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,"#"&CONCAT(BYCOL(--TEXTSPLIT(a&"|"&b,",","|"),LAMBDA(x,DEC2HEX(ROUND(SUM(x)/2,),2))))))Excel solution 21 for Blend RGB Colors into Hex, proposed by Hussain Ali Nasser:
="#"&MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
TEXTJOIN(,
,
DEC2HEX(ROUND((TEXTSPLIT(
a,
", "
) + TEXTSPLIT(
b,
", "
)) / 2,
),
2))))Excel solution 22 for Blend RGB Colors into Hex, proposed by Tyler Cameron:
=LET(
a,
DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
", "
)
& )
)
),
1
),
b,
DROP(
REDUCE(
"",
B2:B10,
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
", "
)
)
)
),
1
),
f,
DEC2HEX(
MAKEARRAY(
ROWS(
a
),
COLUMNS(
a
),
LAMBDA(
r,
c,
ROUND(
AVERAGE(
INDEX(
a,
r,
c
),
INDEX(
b,
r,
c
)
),
0
)
)
),
2
),
BYROW(
f,
LAMBDA(
z,
CONCAT(
"#",
z
)
)
)
)Excel solution 23 for Blend RGB Colors into Hex, proposed by Alexandra Popoff:
= LAMBDA(Color_1,
[Color_2],
MAP(Color_1,
if(
ISOMITTED(
Color_2
),
Color_1,
Color_2
),
LAMBDA(z_C1,
z_C2,
LET(
z_Col_Arr,
ROUND((VALUE(
TRIM(
TEXTSPLIT(
INDEX(
z_C1,
1,
1
),
,
","
)
)
) + VALUE(
TRIM(
TEXTSPLIT(
INDEX(
z_C2,
1,
1
),
,
","
)
)
)) / 2,
0),
z_Hex_Arr,
DEC2HEX(
z_Col_Arr
),
z_Hex_Arr_Cor,
IF(
LEN(
z_Hex_Arr
) = 1,
"0" & z_Hex_Arr,
z_Hex_Arr
),
"#" & TEXTJOIN(
"",
TRUE,
z_Hex_Arr_Cor
)
)))
)Solving the challenge of Blend RGB Colors into Hex with Python
Python solution 1 for Blend RGB Colors into Hex, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("425 Hex Color Blending.xlsx",usecols="A:B", nrows = 10)
test = pd.read_excel("425 Hex Color Blending.xlsx", usecols="C", nrows = 10)
def split_and_convert_colors(color):
return [int(c) for c in color.split(", ")]
input['Color1'] = input['Color1'].apply(split_and_convert_colors)
input['Color2'] = input['Color2'].apply(split_and_convert_colors)
input['FinalColor'] = input.apply(lambda row: [(c1 + c2 + 1) // 2 for c1, c2 in zip(row['Color1'], row['Color2'])], axis=1)
input['Answer Expected'] = input['FinalColor'].apply(lambda color: '#' + ''.join([hex(c)[2:].zfill(2).upper() for c in color]))
input = input.drop(columns=['Color1', 'Color2', 'FinalColor'])
print(input.equals(test)) # True
Solving the challenge of Blend RGB Colors into Hex with Python in Excel
Python in Excel solution 1 for Blend RGB Colors into Hex, proposed by ferhat CK:
import math
al=xl("A1:B10", headers=True)
hex = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, "A", "B", "C", "D", "E", "F"]
j = []
for i in range(len(al)):
x1 = al.Color1[i].split(",")
x2 = al.Color2[i].split(",")
x3 = ""
for n in range(len(x1)):
ort = math.ceil((int(x1[n]) + int(x2[n])) / 2)
x3 += str(hex[ort // 16]) + str(hex[ort % 16])
j.append("#"+x3)
pd.DataFrame({"Answer Expected":j})
Solving the challenge of Blend RGB Colors into Hex with R
R solution 1 for Blend RGB Colors into Hex, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/425 Hex Color Blending.xlsx", range = "A1:B10")
test = read_excel("Excel/425 Hex Color Blending.xlsx", range = "C1:C10")
result = input %>%
mutate(Color1 = strsplit(as.character(input$Color1), ", ") %>%
map(., ~as.numeric(.x)),
Color2 = strsplit(as.character(input$Color2), ", ") %>%
map(., ~as.numeric(.x))) %>%
mutate(FinalColor = map2(Color1, Color2, ~ceiling((.x + .y) / 2))) %>%
mutate(`Answer Expected` = map_chr(FinalColor, ~rgb(.x[1], .x[2], .x[3], maxColorValue = 255))) %>%
select(-Color1, -Color2, -FinalColor)
&&