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

Discuss the spreadsheet application
Post Reply
appoffice
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

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

Post by appoffice »

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.
Last edited by Hagar Delest on Tue Jan 15, 2019 11:23 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 3.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to understand how can i do that

Post by Villeroy »

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
appoffice
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Post by appoffice »

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
appoffice
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Post by appoffice »

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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: I want to understand how can i do that

Post by Lupp »

Code: Select all

{=IF((A1:A100>=20)*(A1:A100<=50); B1:B100)}
or

Code: Select all

{=IF(ABS(A1:A100-35)<=15; B1:B100)}
where
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
appoffice
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Post by appoffice »

Thank you so much ! The first example work fine !
OpenOffice 3.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to understand how can i do that

Post by Villeroy »

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.
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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: I want to understand how can i do that

Post by Lupp »

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.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
appoffice
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Post by appoffice »

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
appoffice
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Post by appoffice »

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..
OpenOffice 3.1
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to understand how can i do that

Post by Villeroy »

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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: I want to understand how can i do that

Post by Lupp »

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

=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.)
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
appoffice
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Post by appoffice »

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
OpenOffice 3.1
Post Reply