• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Logical Function

N

niting

New Member
Chandoo,


Hie again!! I have seen many posts of urs using logical functions in CF, like Iserror, countif, Not, And. However, many a times i had just picked up formulas directly without understanding logic behind it. Would u be kind enough to tell me why do we use in certain formulas, >0 and why sometimes = 0 in logical formulas.


i know its amteurish, but bettr to ask and be seen as a fool rather than not ask and remain fool.
 
Niting


>0 means Greater than zero

=0 means Equal to zero

>=0 means Greater than or equal to zero


Similar to <0, <=0


Their use depends on the logic you are trying to explain

ie:

a Date less than today "<Today()" will not include records from today


a Date less than or equal to today "<=Today()" will include records from any date before or equal to today, but not records dated after today


a Date after today ">Today()" won't include dates before or equal to today.


You can combine logic :


Not equal to today "<>Today()" will give any records before or after today, but not including today. That could also be written as "and(<Today(),>Today())", but theres no need to.


This post could go on forever trying to explain all the nuances of the use of logic.

Keep an eye on what people do and post here and try and work out what are they trying to say in code in real terms.
 
Hui,


Thanks and apologies for the delayed response.


I would be more specific,. In one of posts on comparison Ninza, while comparing 2 lists and hiliting values in both lists, the formula used was-:

COUNTIF(lst2,B21)>0.

Now my question is, we are essentially trying to find same entries, so should not the operator be "=" rather than ">"


Would appreicate if you coudl explain this.


Thanks
 
Niting

lst2 is a named range

This means if the value in B21 is in the List lst2 then the countif() will be > 0
 
Hui,


Thats where I have confusion.


If the countif arguement is false, then it should be zero and thats y u had used in that post for some other purpose, countif(lst2,B21)=0


So when we want the function to higlit values when it is true, then y do we use the operator>0. cant we use COUNTIF(lst2,B21)=1


Pls ustand, i m nt tryin to be preachy. I am just not able to ustand the logic behind dis function.


Thanks

NITIN
 
pitching in my 2 cents...


You are correct niting, you could reverse the logic. You can either write something that says "If this condition is not met, do this..." or you can write "If this condition is met, do this..."


In your example, there is a subtle difference between asking

=If(COUNTIF(..)=0,"Don't act","Act") vs

=IF(COUNTIF(..)=1,"Act","Don't act")


Note how the true/false operations are switched. While you could use either way, the first is better than the latter. Here's why: What if the COUNTIF=2? The 2nd formula would fail to evauluate correctly. (NOTE: If you're 100% sure you will never have more than 1 entry found, then both formulas are equally good) As Hui mentioned in his 1st post, there's many ways to use the logic. If you prefer the true/false arguement order from the 2nd formula, you could write:

=IF(COUNTIF(..)>0,"Act","Don't act")
 
niting,


be aware that the result of the countif can be more that one, so if you have a number appearing 1 in your list you will 1, if it appears 15 times in your list you will get 15.


The result of COUNTIF is not Boolean
 
Thanks a lot , Luke, Hui and Kchiba.


now it is making sense to me. Probably little slow with logic, but thanks for your patience.
 
Forum,


Hie. Just when i thot i understood this logic, then only the formula seem to have given up on me.


I am trying to CF a colmumn which has debtor's name. The logic i m tryin to use is that if the particular bucket in which amount o/s against d debtor falls is greater than the credit term, then it should be hilited.


the credit term is in C2:C30 and the bucket is mentioned in D1, so I select the range of debtor's name and apply CF as follows-

Countif(C2<$d$1)>0

But it does not yield any result. Obviosuly, with the named range in the countif


Any help???


Thanks
 
Niting

Countif needs a range and a condition, seperated by a comma

so it should be like

COUNTIF(A1:A10,"<"&$D$1)>0

change the range to suit


If you want to use a Conditional Format as you have with 1 cell just use

=IF(C2<$D$1),1,0)

where 1 = true and 0 = false
 
You must log in or register to reply here.
Back
Top