What logic function to use?

Discuss the spreadsheet application

What logic function to use?

Postby DennyNet » Mon Jan 21, 2019 12:31 am

Hello

I need to advise on how to use the logical function in the table where I have to break down employees by age category:

first category - up to 30 years old
second category from 31 to 60 years old
third category over 60 years

so far I have only used the "IF"

Code: Select all   Expand viewCollapse view
=IF(F4>30;0;1),
=IF(AND(F4>=31;F4<=60;0;1))
=IF(F4<61;0;1)

I need to merge it when one cell is "true" to convert it to a logical function to display the age range according to the above three categories.

Thank you
OpenOffice 3.1, Windows 7
DennyNet
 
Posts: 2
Joined: Sun Jan 20, 2019 11:58 pm

Re: calc: what logic function to use?

Postby Villeroy » Mon Jan 21, 2019 12:43 am

Create a list of age thresholds and categories in different columns, say X1:Y6
=LOOKUP(A1 ; $X$1:$X$6 ; $Y$1:$Y$6) returns the category from Y1:Y6 where the age in X1:X6 is equal to or smaller than the search value in A1. Works with thousands of values as long as the lookup vector (X1:X6 in the example) is sorted in ascending order.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: calc: what logic function to use?

Postby John_Ha » Mon Jan 21, 2019 1:28 am

You will find much useful information in the User Guides, the Writer, Base and Calc Tutorials and the AOO Frequently Asked Questions. May I suggest you bookmark the pages.

Click the Function Wizard icon to get help with functions.

Never forget the value of "helper columns" where you do part of the work and place the result in a "helper column". You then complete the work by examining what you placed in the "helper columns". See Re: Calculation based on text
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6579
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: calc: what logic function to use?

Postby RusselB » Mon Jan 21, 2019 2:11 am

While this can be done as a single IF, I can see it getting complicated quickly.
I'd recommend that you forget about using the IF, in this case, and use a lookup table, as suggested by Villeroy
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4930
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: AlexandraNyx, Google [Bot] and 29 guests