What logic function to use?

Discuss the spreadsheet application
Post Reply
DennyNet
Posts: 2
Joined: Sun Jan 20, 2019 11:58 pm

What logic function to use?

Post by DennyNet »

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

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

Re: calc: what logic function to use?

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: calc: what logic function to use?

Post by John_Ha »

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
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: calc: what logic function to use?

Post by RusselB »

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.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
Post Reply