At 17:50 01/03/2019 +0530, Sudha Bharathan wrote:
I am unable to get the desired result ...
I don't read minds: it would help if you explained what is your desired result!
... after the third filter is added on open office calc.(open office 4.1.6)
1. filter in column F
2.filter in column N
3.When the third filter is applied in column N
The information showing is all wrong. Screenshot attached.
Actually, I think the information is correct (but there is no visible
example of one case for anyone to be sure).
You will know that there is an implied order of arithmetical
operations. For example, multiplication takes precedence over
addition. So A x B + C means (A x B) + C and not A x (B + C). The
physical order is irrelevant, so A + B x C still means A + (B x C),
not (A + B) x C. Parentheses are used in algebra to indicate when
operations are required to occur in a different order, so one writes
ab+c if the multiplication is to take place before the addition, and
a(b+c) if the addition is to be first.
The same applies to logical operators, and the AND you have here in
your filter has a higher precedence than your OR. So your filter
condition as specified is true if either (1) F is "Iritty" and N is
zero, or (2) N is 10. I think Calc is working correctly for you in
this way. This is as if writing your condition as A AND B OR C in
Boolean algebra - which means (A AND B) OR C.
But what you may want (I'm guessing) is for the condition to be true
if F is "Iritty" and either (1) N is zero or (2) N is 10. This
equates to A AND (B OR C) in Boolean algebra.
Now you cannot (as far as I can see) override the natural order of
logical operations in your Standard Filter: there is no equivalent to
the parentheses. So how to proceed? There are a number of choices:
o As someone has already suggested, you could repeat your first
condition, so that you have F is "Iritty" AND N is zero OR F is
"Iritty" AND N is 10. Both ANDs are now evaluated before the OR, and
the result is what you appear to want. A AND B OR A AND C means (A
AND B) OR (A AND C).
o You could evaluate the required condition by entering in (say) P2
=AND(F1="Iritty";OR(N1=0;N1=10))
and filling down column P. Here the function parentheses define the
order and your Standard Filter needs to say just
Column P = TRUE. (You can hide column P or put the values elsewhere
if preferred.)
o (Probably best) Use Advanced Filter instead. First give at least
columns F and N headings in the top row of your range to be filtered.
Now somewhere else and convenient - even on another sheet - duplicate
the row headings and place below these the criteria you want to
apply. Criteria in columns of a row are automatically combined with
AND and criteria in rows of a column are similarly combined using OR.
So in the first row under your duplicated column headings you will
have "Iritty" and zero, and underneath them in the next row "Iritty"
(again) and 10. Now select the range of rows to be filtered, and go
to Data | Filter > | Advanced Filter... . Drag across your newly
defined table of criteria. Click OK. Bingo!
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
