I’m using Version 4.2.7.2 on Linux Mint 17.
So I have a numbers representing specific wavelengths. I want to take that wavelength and get the name of the color. What is the easiest or cleanest way to go about this. I could have a bunch of if statements but it would be a mess and I’m sure there is an easier way. Right now I just a have a spearate sheet with the color names as columns and a value of TRUE or FALSE if the wavelength matches. Using that, is there a way to select the TRUE one and ouput the color? Hopefully this was somewhat clear. I’m not too familiar with LibreOffice or spreadsheet operations in general. Thanks for any help
Hallo
Setup a two-Column-range with sorted wavelenght on the left and color-name on the right.
then use Formula: =VLOOKUP( wavelenght ; yourRange ; 2 )
For Example with fictive Celladresses:
=VLOOKUP( A1 ; J2:K99 ; 2 )
As VLOOKUP can only look for exact matches (SortOrder=0) or go through ranges sorted ascending (SortOrder<>0 or omitted) it cannot be used with a lookup table with wavelengths in descending order. The tabel will need processing first. Another limitation of VLOOKUP is that the values to go through have to be in the first column of the table. There are more flexible solutions - and even a quick&dirty solution using CHOOSE() may be chosen if only a few limits are to check.
After looking at this further, I’ll comment that the conversion from wavelengths to colors is not going to change dynamically, and since you are hard-coding these values into a sheet anyway, I’m skeptical of what is gained by avoiding IF statements … By all means, implement as CHOOSE or SQL or VLOOKUP or whatever you want instead of IF statements. But … six of one half dozen of the other.
Here is an implementation of the last idea from my previous answer that was downvoted (with exclamation points!!), converting the wavelength to RGB. It also hard-codes text ranges, and in fact like the actual conversion table. This implementation borrows heavily from, and translates Tarc’s code on StackOverflow, and SabreWolfy’s code also on StackOverflow. It will dynamically change the background colors of cells to match the wavelength, more-or-less, and will write out the generic name of the color, although I would suggest more granular naming of colors than provided here.
The code is written for a two-column spreadsheet, with wavelength values in column A and the automated formatting and text in column B. This is a better answer because it actually uses the colors in a dynamic manner, which is not possible other than by macro. It is cleanest because the desired hard-coding is in a centralized location and has the virtue of accomplishing the intended purpose. Most of the mess here is the conversion from wavelength to RGB and thus is unavoidable. Would need to come up with a way to reliably run macro whenever needed.
Sub Main
Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
Doc = ThisComponent
Sheet = Doc.Sheets(0)
i = 0
Cell = Sheet.getCellByPosition(0, i)
gamma = 0.80
intensityMax = 255
Do While Cell.getValue():
WaveLength = Cell.getValue()
If((Wavelength >= 380) And (Wavelength<440)) Then
r = -(Wavelength - 440) / (440 - 380)
g = 0.0
b = 1.0
ElseIf ((Wavelength >= 440) And (Wavelength<490)) Then
r = 0.0
g = (Wavelength - 440) / (490 - 440)
b = 1.0
ElseIf ((Wavelength >= 490) And (Wavelength<510)) Then
r = 0.0
g = 1.0
b = -(Wavelength - 510) / (510 - 490)
ElseIf ((Wavelength >= 510) And (Wavelength<580)) Then
r = (Wavelength - 510) / (580 - 510)
g = 1.0
b = 0.0
ElseIf ((Wavelength >= 580) And (Wavelength<645)) Then
r = 1.0
g = -(Wavelength - 645) / (645 - 580)
b = 0.0
ElseIf ((Wavelength >= 645) And (Wavelength<781)) Then
r = 1.0
g = 0.0
b = 0.0
Else
r = 0.0
g = 0.0
b = 0.0
End If
REM Let the intensity fall off near the vision limits
If((Wavelength >= 380) And (Wavelength<420)) Then
factor = 0.3 + 0.7*(Wavelength - 380) / (420 - 380)
ElseIf ((Wavelength >= 420) And (Wavelength<701)) Then
factor = 1.0
ElseIf ((Wavelength >= 701) And (Wavelength<781)) Then
factor = 0.3 + 0.7*(780 - Wavelength) / (780 - 700)
Else
factor = 0.0
EndIf
REM Don't want 0^x = 1 for x <> 0
If r = 0.0 Then
rgb1 = 0
Else
rgb1 = intensityMax * ((r * factor) ^ gamma)
End If
If g = 0.0 Then
rgb2 = 0
Else
rgb2 = intensityMax * ((g * factor) ^ gamma)
End If
If b =0.0 Then
rgb3 = 0
Else
rgb3 = intensityMax * ((b * factor) ^ gamma)
End If
FCell = Sheet.getCellByPosition(1, i)
FCell.CellBackColor = RGB(rgb1,rgb2,rgb3)
If Wavelength <= 422.5 Then
text_str = "Violet"
ElseIf Wavelength > 422.5 And WaveLength <= 460 Then
text_str = "Indigo"
ElseIf Wavelength > 422.5 And WaveLength <= 460 Then
text_str = "Indigo"
ElseIf Wavelength > 460 And WaveLength <= 492.5 Then
text_str = "Blue"
ElseIf Wavelength > 492.5 And WaveLength <= 540 Then
text_str = "Green"
ElseIf Wavelength > 540 And WaveLength <= 580 Then
text_str = "Yellow"
ElseIf Wavelength > 580 And WaveLength <= 610 Then
text_str = "Orange"
Else
text_str = "Red"
End If
FCell.setString(text_str)
i = i + 1
Cell = Sheet.getCellByPosition(0, i)
Loop
End Subthere is no question about changing Cellbackcolors…( btw your Code ← -1 )
@karolus: The question was to get the name of the color. The only clean way to deal with the many variations of colors is to represent the color visually, which you would realize if you engaged the problem with any depth. The RGB representation of a color is the name. As Lupp accurately points out, the alternative would require either something that looks a lot like a mess or a lookup table. Your thoughless critiques are out of step with the culture of this forum, and are wrong…
@karolus: The only other user I see who has issued anywhere near the same number of downvotes as you has issued 24x more up-votes. Your deviation from the supportive culture of the forum would be easier to overlook if your own answers were uniformly superior.
The original subject is What's the easiest way to find the range a number lies within?
also in the body of the question is no hint about calculating RGB-values from given wavelenght
- but maybe I’m wrong because I’m not native english - in any case your first answer (removed by yourself) isnt helpfull
@karolus: I think we can both agree that the cleanest way to describe the colors from the near infinite variety of wavelenths is not to make a list of them by name in a spreadsheet. You do realize, don’t you, that you downvoted both of my working answers while providing a non-working answer yourself? I don’t think it is a language problem. While you are at it, why not also go over to stackoverflow and downvote those authors too.
I’ve provided a working answer - in opposite to your removed if-cascade the VLOOKUP- formula doesnt grow to unmantainable size if there are more than 5 different cases to handle with, it works in exactly the same way for 5, 10 or 50000 entries.
( and in case @MeMoses confirmes the question was: how to calculate rgb-representation from wave-lenght? – i will be able to give an answer without copy&paste from stackoverflow - be sure!)
@karolus: so you are saying the ‘clean’ solution is to have a 50,000 -entry list, rather than displaying the actual RGB value of a color? And you are so confident of that that you will downvote my answer? Wow. On the one hand you downvoted saying the code is -1, but now you claim copy-paste from a highly-rated source? You are just making things up now.
@karolus: Wait a minute, did you just say that I should be sure that you know without looking what are the algorithms for converting wavelength to RGB? Amazing once again. Are you also saying that the java code that was my (cited) source will execute in the LO Basic interpreter? I’ll disagree there. Please be more cognizent of the TDF member code to “treat fellow members and end users with politeness, indulgence, objectivity, liberality, friendliness, understanding, and goodwill”
Dont twist my words - i’ve compared the Limitations of your if ssss to the properties of one VLOOKUP - nothing about Clean solution- (-for what?-) - should have 50000 entry-list.
I say nothing about java code will run directly from Basic - i say: your Basic-code is almost c&p with some adaption into Basicsyntax.
EOT
@karolus: you rationalized your downvote as bad code, and then for cut and paste, when I said it was a translation to Basic of a java algorithm that itself was translated from Pascal. The answer was within the reasonable range of responses for an informal Q&A forum, and the code works . As for your answer, if you aren’t going to use 50,000 rows of color names, then I guess your VLOOKUP solution isn’t a great improvement after all. The question was for the easiest or cleanest way.
Sometimes we may look for a “quick and dirty” solution, avoiding the creation of a lookup table, e.g. To also avoid the many nested IF() calls we may use the often underestimated CHOOSE(). With the Lambda/nm in A20 this example may show what I mean. (Colour names in German, wavelength limits from de.wikipedia.org)
=CHOOSE(1+($A20>=390)+($A20>=420)+($A20>=480)+($A20>=560)+($A20>=580)+($A20>=630);"ultraviolett";"violett";"blau";"grün";"gelb";"orange";"rot";"infrarot")