Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 18 Feb 2018 15:47:20 +0100How to find where in the interval a number ishttps://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/Imagine we have 3 columns, the first two define a range (e.g. min and max weight) and the third the value (e.g. price that item costs if it has that weight).
![Example](https://ibin.co/3rvyu5bRGXvs.png)
What formula should be used to achieve this?
[From this site](https://chandoo.org/wp/2010/06/30/range-lookup-excel/) I was able to build something that returns the number of the column, but I would now need to get column C.
To get the column number I use =IF(SUMPRODUCT(--(A1:A39<=F2)*(B1:B39>=F2))=1,SUMPRODUCT(--(A1:A39<=F2)*(B1:B39>=F2),ROW(A1:A39))-1,"Not Found")
here is an example file: https://drive.google.com/file/d/1yJGcRI71R5zR_OOpCYiPuWI1g3vdLxNr/view?usp=sharing
**Questions:**
1. How would I retrieve the corresponding value in C?
2. Is there an easier way to do this?Fri, 16 Feb 2018 19:58:39 +0100https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/Comment by Lupp for <p>Imagine we have 3 columns, the first two define a range (e.g. min and max weight) and the third the value (e.g. price that item costs if it has that weight).
<img alt="Example" src="https://ibin.co/3rvyu5bRGXvs.png"></p>
<p>What formula should be used to achieve this?</p>
<p><a href="https://chandoo.org/wp/2010/06/30/range-lookup-excel/">From this site</a> I was able to build something that returns the number of the column, but I would now need to get column C.
To get the column number I use =IF(SUMPRODUCT(--(A1:A39<=F2)<em>(B1:B39>=F2))=1,SUMPRODUCT(--(A1:A39<=F2)</em>(B1:B39>=F2),ROW(A1:A39))-1,"Not Found")</p>
<p>here is an example file: <a href="https://drive.google.com/file/d/1yJGcRI71R5zR_OOpCYiPuWI1g3vdLxNr/view?usp=sharing">https://drive.google.com/file/d/1yJGc...</a></p>
<p><strong>Questions:</strong></p>
<ol>
<li>How would I retrieve the corresponding value in C?</li>
<li>Is there an easier way to do this?</li>
</ol>
https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146575#post-id-146575The world isn't organized in steps of one hundredth.
Assume you intervals left side closed, right side open. The first row e.g. covering
`1.10 <= value < 1.50`, the next interval then being `1.50 <= value < 2.00`.
If actually only occur values gtting integer when multiplied with 100 this won't spoil anything.
Anyway the column of upper limits is superfluous.Fri, 16 Feb 2018 20:32:53 +0100https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146575#post-id-146575Comment by migueldealmeida for <p>Imagine we have 3 columns, the first two define a range (e.g. min and max weight) and the third the value (e.g. price that item costs if it has that weight).
<img alt="Example" src="https://ibin.co/3rvyu5bRGXvs.png"></p>
<p>What formula should be used to achieve this?</p>
<p><a href="https://chandoo.org/wp/2010/06/30/range-lookup-excel/">From this site</a> I was able to build something that returns the number of the column, but I would now need to get column C.
To get the column number I use =IF(SUMPRODUCT(--(A1:A39<=F2)<em>(B1:B39>=F2))=1,SUMPRODUCT(--(A1:A39<=F2)</em>(B1:B39>=F2),ROW(A1:A39))-1,"Not Found")</p>
<p>here is an example file: <a href="https://drive.google.com/file/d/1yJGcRI71R5zR_OOpCYiPuWI1g3vdLxNr/view?usp=sharing">https://drive.google.com/file/d/1yJGc...</a></p>
<p><strong>Questions:</strong></p>
<ol>
<li>How would I retrieve the corresponding value in C?</li>
<li>Is there an easier way to do this?</li>
</ol>
https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146729#post-id-146729I agree. The real world example actually **is** organised in steps of one hundredth (it is a cost and they don't define less than one cent of euro), but I agree that from the calculation's point of view the upper limit is not needed.Sun, 18 Feb 2018 15:39:30 +0100https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146729#post-id-146729Answer by Lupp for <p>Imagine we have 3 columns, the first two define a range (e.g. min and max weight) and the third the value (e.g. price that item costs if it has that weight).
<img alt="Example" src="https://ibin.co/3rvyu5bRGXvs.png"></p>
<p>What formula should be used to achieve this?</p>
<p><a href="https://chandoo.org/wp/2010/06/30/range-lookup-excel/">From this site</a> I was able to build something that returns the number of the column, but I would now need to get column C.
To get the column number I use =IF(SUMPRODUCT(--(A1:A39<=F2)<em>(B1:B39>=F2))=1,SUMPRODUCT(--(A1:A39<=F2)</em>(B1:B39>=F2),ROW(A1:A39))-1,"Not Found")</p>
<p>here is an example file: <a href="https://drive.google.com/file/d/1yJGcRI71R5zR_OOpCYiPuWI1g3vdLxNr/view?usp=sharing">https://drive.google.com/file/d/1yJGc...</a></p>
<p><strong>Questions:</strong></p>
<ol>
<li>How would I retrieve the corresponding value in C?</li>
<li>Is there an easier way to do this?</li>
</ol>
https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?answer=146577#post-id-146577This is a cvase for one of the lookup variants. Since your lower limits are included with the respective intervals (left closed), you can use VLOOKUP() or a combination of MATCH() and INDEX() [or OFFSET()], both with the sort parameter 1 giving the assurance that the lower limits are strictly ascending top down.
(Edit1 regarding the comment below:)
Using VLOOKUP no additional checks are needed.
If the value to select the interval does not produce a match an eror will be returned. There are sufficient means to handle the situation without a need to check specifically for that value being a number.
I made **[this example](/upfiles/15188636581408818.ods)** preferring the flexible combination of MATCH() with INDEX(). (Even more flexibility is achieved using OFFSET() in some places.) The example values (now in column E instead of F) are mostly generated randomly.
How to alert users to enter valid entities was not my concern. If addressing this, I would prefer to use a conditional format.Fri, 16 Feb 2018 20:36:18 +0100https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?answer=146577#post-id-146577Comment by Paul451 for <p>This is a cvase for one of the lookup variants. Since your lower limits are included with the respective intervals (left closed), you can use VLOOKUP() or a combination of MATCH() and INDEX() [or OFFSET()], both with the sort parameter 1 giving the assurance that the lower limits are strictly ascending top down. </p>
<p>(Edit1 regarding the comment below:) <br>
Using VLOOKUP no additional checks are needed. </p>
<p>If the value to select the interval does not produce a match an eror will be returned. There are sufficient means to handle the situation without a need to check specifically for that value being a number. </p>
<p>I made <strong><a href="/upfiles/15188636581408818.ods">this example</a></strong> preferring the flexible combination of MATCH() with INDEX(). (Even more flexibility is achieved using OFFSET() in some places.) The example values (now in column E instead of F) are mostly generated randomly. </p>
<p>How to alert users to enter valid entities was not my concern. If addressing this, I would prefer to use a conditional format.</p>
https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146617#post-id-146617For eg.
=VLOOKUP($F$2,$A$2:$C$10,3)
To check the range, perhaps use MIN() and MAX()
Ie,
=IF(MAX($A$2:$A$10)<$F$2,"Too Large", IF(MIN($A$2:$A$10)>$F$2,"Too Small",VLOOKUP($F$2,$A$2:$C$10,3)))
And to check that the F2 search value is a number, use ISNUM().
Ie,
=IF(ISNUM($F$2),IF(MAX($A$2:$A$10)<$F$2,"Too Large", IF(MIN($A$2:$A$10)>$F$2,"Too Small",VLOOKUP($F$2,$A$2:$C$10,3))),"Enter Number")Sat, 17 Feb 2018 07:06:39 +0100https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146617#post-id-146617Comment by migueldealmeida for <p>This is a cvase for one of the lookup variants. Since your lower limits are included with the respective intervals (left closed), you can use VLOOKUP() or a combination of MATCH() and INDEX() [or OFFSET()], both with the sort parameter 1 giving the assurance that the lower limits are strictly ascending top down. </p>
<p>(Edit1 regarding the comment below:) <br>
Using VLOOKUP no additional checks are needed. </p>
<p>If the value to select the interval does not produce a match an eror will be returned. There are sufficient means to handle the situation without a need to check specifically for that value being a number. </p>
<p>I made <strong><a href="/upfiles/15188636581408818.ods">this example</a></strong> preferring the flexible combination of MATCH() with INDEX(). (Even more flexibility is achieved using OFFSET() in some places.) The example values (now in column E instead of F) are mostly generated randomly. </p>
<p>How to alert users to enter valid entities was not my concern. If addressing this, I would prefer to use a conditional format.</p>
https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146732#post-id-146732This works really well, thanks!Sun, 18 Feb 2018 15:47:20 +0100https://ask.libreoffice.org/en/question/146572/how-to-find-where-in-the-interval-a-number-is/?comment=146732#post-id-146732