Page 1 of 1

What logic function to use?

PostPosted: Mon Jan 21, 2019 12:31 am
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   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

Re: calc: what logic function to use?

PostPosted: Mon Jan 21, 2019 12:43 am
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.

Re: calc: what logic function to use?

PostPosted: Mon Jan 21, 2019 1:28 am
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

Re: calc: what logic function to use?

PostPosted: Mon Jan 21, 2019 2:11 am
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