Page 1 of 1

### [Solved] I want to understand how can i do that

Posted: Mon Jan 14, 2019 7:45 pm
Hello

I have a problem to understand how i need to do/create a formula.

So, for example I have two column, with ranges

A1:A100 - contain only numbers from 1 to 100
B1:B100 - contain any text data

Now i need to get all data from B1:B100 to list, where A1:A100>=20 AND A1:A100<=50

How I can do that ? whic what functions ?

I have i little example what work fine but.. it have only one condition : IF(A1:A100>=20; B1:B100) (i put this formula in Data>Validity>Range Cell) and it's work fine ! but i need TWO conditions.. so my idea it's like a BETWEEN searching..

I hope you understand what i say and my logic.

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 7:55 pm

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 8:13 pm
Yes, it's works, but I need to have this data in (select) List (like it do this formula : IF(A1:A100>=20; B1:B100)

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 8:27 pm
In simply words, i want to make a query like : select all data from B1:B100 where A1:A100 contain numbers >=20 AND <=50 (and show me it in List)

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 8:51 pm
Code: Select all   Expand viewCollapse view
`{=IF((A1:A100>=20)*(A1:A100<=50); B1:B100)}`
or
Code: Select all   Expand viewCollapse view
`{=IF(ABS(A1:A100-35)<=15; B1:B100)}`
where
35 is short for (20+50)/2 and 15 is short for (50-20)/2.

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 9:06 pm
Thank you so much ! The first example work fine !

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 9:49 pm
File>New>Database...
[X] Connect to existing
[X] Register the database
Save the database.

SELECT * FROM "Sheet1" WHERE "Number Field" BETWEEN 20 AND 50
and add a param query like this:
SELECT * FROM "Sheet1" WHERE "Number Field" BETWEEN :From AND :Until

Save and close the database with the working queries.
-------------------
In Calc or Writer hit F4 for the data source window, browse to your query and drag the one you like better into a document. When you drag it into a spreadsheet (could be the same spreadsheet as the source) you always get a refreshable import range. Click any cell in the import range and call menu:Data>Refresh
A database connection to a spreadsheet is very limited but the filters and sorting capabilities are far better than Calc alone.

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 10:23 pm
appoffice wrote:Thank you so much ! The first example work fine !

Aha. But the second formula should return exactly the same result.
(All the mentioned formula, including the one posted in the question, suffer from lacking the alternative excpression, and thus return FALSE for the else-case.)

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 10:25 pm
Thanks, but my level is basic now, I just statin to learn office/calc and see it's great tool for solve many problems with any data.

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 10:28 pm
Lupp wrote:
appoffice wrote:Thank you so much ! The first example work fine !

Aha. But the second formula should return exactly the same result.
(All the mentioned formula, including the one posted in the question, suffer from lacking the alternative excpression, and thus return FALSE for the else-case.)

Thanks, in any case I will need to learn more to understand more tricks and how it works..

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 11:30 pm
Do you understand how the array function works and what to do when the input area A1:B100 expands?

### Re: I want to understand how can i do that

Posted: Mon Jan 14, 2019 11:33 pm
I should probably add that the "trick" to use (A1:A100>=20)*(A1:A100<=50) here instead of AND((A1:A100>=20;A1:A100<=50) isn't just introduced for fun. The AND-variant wouldn't work inside an array-formula because AND doesn't treat its arguments as arrays. It is thus "not eligible fort array-evaluation". Calulating the result row by row I would surely use
Code: Select all   Expand viewCollapse view
`=IF(AND(A1>=20;A1<=50);B1;"")`
in C1 (e.g.) and fill this formula down till C100.
In fact I would resort to array-evaluation only if the result needs to be passed to a surrounding expression.

===Edit 2019-0115 134:45 CET===
Formula rectified. (The formula for use per row wrongly still contained range addresses.)

### Re: I want to understand how can i do that

Posted: Tue Jan 15, 2019 1:47 pm
Villeroy wrote:Do you understand how the array function works and what to do when the input area A1:B100 expands?

Yes understand but a little bit, I need more practice and reading about arrays and how functions works but in detail, i know +- how it works in abstract..
because it's my first experience with calc/excel tools.
After a fix my problem i will start to learn from begin, step by step for better understanding in details all works.

If I put A1:B100 - in theory i will select all cells in this ranges