Luke.Speam
New Member
- Joined
- Jun 6, 2012
- Messages
- 6
Hello,
I have a spreadsheet containing a large dataset of reflected light values for a number of different coloured squares. These squares each have a stimulus name (which is comprised of a code detailing the specific paints and proportions used in making the square), a colour category (simply the colour name of the square - e.g. blue, blue-green, etc.), a lightness (e.g. light, medium, or dark), and finally the amount of light reflected for each wavelength of light between 380nm and 780nm (in 4nm intervals). See the example below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Stimulus[/TD]
[TD]Colour[/TD]
[TD]Lightness[/TD]
[TD]380nm[/TD]
[TD]384nm[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]red[/TD]
[TD]light[/TD]
[TD]x1[/TD]
[TD]x2[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]blue[/TD]
[TD]light[/TD]
[TD]x3[/TD]
[TD]x4[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]blue[/TD]
[TD]dark[/TD]
[TD]x5[/TD]
[TD]x6[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]green[/TD]
[TD]medium[/TD]
[TD]x7[/TD]
[TD]x8[/TD]
[/TR]
</tbody>[/TABLE]
As I said, the data set is larger than this (comprising of around 400 stimuli), and there are far more than 3 colour names.
My question to you is this: Is there some way to create several line graphs (one for each colour category) with the wavelengths on the x axis and the reflected light values on the y axis? My aim is to have a line graph for each of the different colour categories with each stimulus within that category to be an individual series.
This would be an easy job if I could just sort the data by the colour category and then manually select the data for each colour category. However, any given stimulus' colour category is liable to change at any point in time. I am currently reviewing the colour categories of all stimuli to try to make sure they are consistent with each other - this is a long process and is extremely variable (e.g. one day I might call a particular stimulus blue, the next day purple). So it is crucial that each graph's data selection range is dynamic - so that if I reclassified, for example, stimulus b from blue to purple, the graph with all purple colours would automatically include this in its data selection.
One solution to this I have been thinking of is using VLOOKUP formulae in different sheets (e.g. one sheet for Blue, another for Red, etc.) to return all the stimuli, and their respective reflected wavelengths, that are classified as a given colour and then create a line graph within each of those sheets. But I don't know how to do this - is it even possible to VLOOKUP multiple values like that?
In my final words of this post, I'd like to say thank you (and sorry!) for reading this extremely long post and thank you for any suggestions you have. If you would like me to explain in further detail anything, please don't hesitate to ask.
Thanks again, Luke.
I have a spreadsheet containing a large dataset of reflected light values for a number of different coloured squares. These squares each have a stimulus name (which is comprised of a code detailing the specific paints and proportions used in making the square), a colour category (simply the colour name of the square - e.g. blue, blue-green, etc.), a lightness (e.g. light, medium, or dark), and finally the amount of light reflected for each wavelength of light between 380nm and 780nm (in 4nm intervals). See the example below:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Stimulus[/TD]
[TD]Colour[/TD]
[TD]Lightness[/TD]
[TD]380nm[/TD]
[TD]384nm[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]red[/TD]
[TD]light[/TD]
[TD]x1[/TD]
[TD]x2[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]blue[/TD]
[TD]light[/TD]
[TD]x3[/TD]
[TD]x4[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]blue[/TD]
[TD]dark[/TD]
[TD]x5[/TD]
[TD]x6[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]green[/TD]
[TD]medium[/TD]
[TD]x7[/TD]
[TD]x8[/TD]
[/TR]
</tbody>[/TABLE]
As I said, the data set is larger than this (comprising of around 400 stimuli), and there are far more than 3 colour names.
My question to you is this: Is there some way to create several line graphs (one for each colour category) with the wavelengths on the x axis and the reflected light values on the y axis? My aim is to have a line graph for each of the different colour categories with each stimulus within that category to be an individual series.
This would be an easy job if I could just sort the data by the colour category and then manually select the data for each colour category. However, any given stimulus' colour category is liable to change at any point in time. I am currently reviewing the colour categories of all stimuli to try to make sure they are consistent with each other - this is a long process and is extremely variable (e.g. one day I might call a particular stimulus blue, the next day purple). So it is crucial that each graph's data selection range is dynamic - so that if I reclassified, for example, stimulus b from blue to purple, the graph with all purple colours would automatically include this in its data selection.
One solution to this I have been thinking of is using VLOOKUP formulae in different sheets (e.g. one sheet for Blue, another for Red, etc.) to return all the stimuli, and their respective reflected wavelengths, that are classified as a given colour and then create a line graph within each of those sheets. But I don't know how to do this - is it even possible to VLOOKUP multiple values like that?
In my final words of this post, I'd like to say thank you (and sorry!) for reading this extremely long post and thank you for any suggestions you have. If you would like me to explain in further detail anything, please don't hesitate to ask.
Thanks again, Luke.
