[Solved] I want to understand how can i do that
[Solved] I want to understand how can i do that
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.
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.
Last edited by Hagar Delest on Tue Jan 15, 2019 11:23 pm, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OpenOffice 3.1
Re: I want to understand how can i do that
menu:Data>Filter>Standard Filter...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: I want to understand how can i do that
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)
OpenOffice 3.1
Re: I want to understand how can i do that
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)
OpenOffice 3.1
Re: I want to understand how can i do that
Code: Select all
{=IF((A1:A100>=20)*(A1:A100<=50); B1:B100)}
Code: Select all
{=IF(ABS(A1:A100-35)<=15; B1:B100)}
35 is short for (20+50)/2 and 15 is short for (50-20)/2.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: I want to understand how can i do that
Thank you so much ! The first example work fine !
OpenOffice 3.1
Re: I want to understand how can i do that
File>New>Database...
[X] Connect to existing
Type: Spreadsheet
[X] Register the database
Save the database.
Add a query like:
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.
[X] Connect to existing
Type: Spreadsheet
[X] Register the database
Save the database.
Add a query like:
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: I want to understand how can i do that
Aha. But the second formula should return exactly the same result.appoffice wrote:Thank you so much ! The first example work fine !
(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.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: I want to understand how can i do that
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.
OpenOffice 3.1
Re: I want to understand how can i do that
Thanks, in any case I will need to learn more to understand more tricks and how it works..Lupp wrote:Aha. But the second formula should return exactly the same result.appoffice wrote:Thank you so much ! The first example work fine !
(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.)
OpenOffice 3.1
Re: I want to understand how can i do that
Do you understand how the array function works and what to do when the input area A1:B100 expands?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: I want to understand how can i do that
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
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.)
Code: Select all
=IF(AND(A1>=20;A1<=50);B1;"")
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.)
Last edited by Lupp on Tue Jan 15, 2019 2:45 pm, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: I want to understand how can i do that
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..Villeroy wrote:Do you understand how the array function works and what to do when the input area A1:B100 expands?
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
OpenOffice 3.1