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

Discuss the spreadsheet application

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

Postby appoffice » 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.
Last edited by Hagar Delest on Tue Jan 15, 2019 11:23 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 3.1
appoffice
 
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Postby Villeroy » Mon Jan 14, 2019 7:55 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to understand how can i do that

Postby appoffice » 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)
OpenOffice 3.1
appoffice
 
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Postby appoffice » 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)
OpenOffice 3.1
appoffice
 
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Postby Lupp » 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.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: I want to understand how can i do that

Postby appoffice » Mon Jan 14, 2019 9:06 pm

Thank you so much ! The first example work fine !
OpenOffice 3.1
appoffice
 
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Postby Villeroy » Mon Jan 14, 2019 9:49 pm

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to understand how can i do that

Postby Lupp » 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.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: I want to understand how can i do that

Postby appoffice » 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.
OpenOffice 3.1
appoffice
 
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Postby appoffice » 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..
OpenOffice 3.1
appoffice
 
Posts: 7
Joined: Mon Jan 14, 2019 7:30 pm

Re: I want to understand how can i do that

Postby Villeroy » 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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27111
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: I want to understand how can i do that

Postby Lupp » 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.)
Last edited by Lupp on Tue Jan 15, 2019 2:45 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2528
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: I want to understand how can i do that

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


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 19 guests