We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

What's the easiest way to find the range a number lies within [closed]

asked 2015-03-22 18:57:31 +0200

MeMoses gravatar image

updated 2020-08-17 22:59:35 +0200

Alex Kemp gravatar image

I'm using Version 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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-17 23:01:34.321169

3 Answers

Sort by » oldest newest most voted

answered 2015-03-24 13:46:22 +0200

Lupp gravatar image

updated 2015-03-24 13:47:27 +0200

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 )

edit flag offensive delete link more

answered 2015-03-22 20:03:02 +0200

karolus gravatar image

updated 2015-03-22 20:06:56 +0200


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 )

edit flag offensive delete link more


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.

Lupp gravatar imageLupp ( 2015-03-22 23:35:03 +0200 )edit

answered 2015-03-24 04:42:50 +0200

doug gravatar image

updated 2015-03-24 05:32:53 +0200

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
        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)
        factor = 0.0

    REM Don't want 0^x = 1 for x <> 0
    If r = 0.0 Then 
      rgb1 = 0 
      rgb1 = intensityMax * ((r * factor) ^ gamma)
    End If

    If g ...
edit flag offensive delete link more


there is no question about changing Cellbackcolors...( btw your Code ← -1 )

karolus gravatar imagekarolus ( 2015-03-24 07:46:42 +0200 )edit

@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..

doug gravatar imagedoug ( 2015-03-24 14:09:12 +0200 )edit

@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.

doug gravatar imagedoug ( 2015-03-24 14:50:02 +0200 )edit

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 gravatar imagekarolus ( 2015-03-24 15:10:49 +0200 )edit

@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.

doug gravatar imagedoug ( 2015-03-24 16:02:07 +0200 )edit

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 gravatar imagekarolus ( 2015-03-24 17:16:18 +0200 )edit

@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.

doug gravatar imagedoug ( 2015-03-24 17:30:57 +0200 )edit

@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"

doug gravatar imagedoug ( 2015-03-24 18:43:55 +0200 )edit

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.


karolus gravatar imagekarolus ( 2015-03-25 01:29:22 +0200 )edit

@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.

doug gravatar imagedoug ( 2015-03-25 02:14:07 +0200 )edit

Question Tools

1 follower


Asked: 2015-03-22 18:57:31 +0200

Seen: 465 times

Last updated: Mar 24 '15