Using excel for a colour calculation

[Q]

I am trying to use excel to perform some calculations with colour matching.

I have 20 rows of colour samples with three inter related columns of numbers
R-G-B for each sample.

I am dividing each sample row by 10 to produce a predetermined fraction of
each colour sample, example below with just two rows.

A B C
120 100 50
100 150 90

/10
A B C
12 10 5
10 15 9

Having reduced each of the 20 rows of three columns to a tenth as fraction
samples of their starting values I now want to calculate what 10 additive
combinations of the 20 rows of three columns will come closest to a target
value. eg. If I wish to achieve;
A B C
215 40 87

By adding 10 fraction samples from the rows together to achieve the closest
result. The closest result can have anything between 0-10 fraction samples
from any of the rows but it must have 10 samples in total (all ten could
even come from one row) and the result just has to be the closest mix within
those parameters.

e.g. The result might end up being
A B C
208 46 81

Any ideas on how I might achieve the formulas for this?

 

[Bernie Deitrick]

Q,

Can your solution include mulitples of one row (repeats allowed - ie, 3 of row 2, 4 of row 7, 1 of
row 10, and 2 of row 14) or is it limited to 10 separate samples added together?

In either case, the solution will require iterating by trial and error - allowing repeats will
increase the number or trials by a factor of 10 - but each is doable using a VBA solution.

HTH,
Bernie
MS Excel MVP

 

[Q]

Thanks for the reply

Yes repeats of rows can occur even to the extent of all 10 samples coming
from the one row.
So you think this is achievable?

I am searching for a solution for skin colour matching for clients with
depigmentation problems e.g. vitiligo who require repigmentation.

 

[Bernie Deitrick]

Q,

I sent a workbook to your GMail account.

HTH,
Bernie
MS Excel MVP

 

[Q]

Thanks incredibly to Bernie your solution works a treat and you are
obviously extremely intelligent and gifted.
May the sun always shine upon your face.