I don't understand this statement...

[Henry Gilbert]

Hi Chandoo,
A friend showed me a statement the other day and there is a piece of it I don't under stand. It was multiple COUNTIF's, AND, OR combined,, plus a weird twist I've never seen before and I hoped you could explain it to me. The weird part looks like this "=COUNTIF(SIFPrevMonth!$G:$G,"Injury/Illness")*AND(OR(COUNTIF(" , I don't get the purpose of the asterisk. I will post the complete statement in hope you can help.

Thanks,
Henry

The Complete Statement

=COUNTIF(SIFPrevMonth!$G:$G,"Injury/Illness")*AND(OR(COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Health Environment and Safety")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Information Technology")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Supply Chain Management")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Finance")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Geological & Geophysical Operations")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Human Resources")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Information & Technology")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Appraisal")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Business Development & Planning")+COUNTIF(SIFPrevMonth!$B:$B,"DWEP:Exploration")))

 

[Somendra Misra]

Hi Henry,

Welcome to chandoo.org forum.

All I can see that here * is just doing the multiplication with the logical result of AND i.e. either TRUE or FALSE which inturn will convert the result to either 0 or the count of Injury/Illness in column G of SIFprevMonth sheet.

The thing is in OR condition there is a series of addition of count of various condition in column B of SIF Prev Month sheet, which will be a positive number which OR will treats as TRUE or 0 which OR will treats as FALSE. So AND will work and finally the multiplication.

If you can upload a sample file with the purpose of this formula may be we can help you with an alternative or can explain you in much more depth.

Regards,

 

[Asheesh]

First of all welcome onboard...

asterrisk indicates multiple, if you are looking for the detailed explanation, can you please post the sample file.

 

[NARAYANK991]

Hi ,

The weird part that you have questioned is not really weird ; what is weird is the fact that an AND and an OR have used together.

First , to your question.

When we use a mathematical operation on two booleans , we are actually converting the boolean expressions into numeric quantities.

In Boolean logic , a TRUE is equivalent to 1 , and a FALSE is equivalent to 0.

When we carry out an operation such as TRUE * TRUE , what we will end up with is 1 ; TRUE * FALSE will give us 0 , as will the others FALSE * TRUE and FALSE * FALSE.

However , what is weird in the posted formula is why an AND has been used outside the OR , and why the COUNTIF is used in its normal syntax , instead of the array syntax.

The COUNTIF family of functions has 2 versions ; one is something like the following :

=COUNTIF(datarange , scalar value)

Another is :

=COUNTIF(scalar value , datarange)

A third version :

=COUNTIF(datarange , vector value)

is merely a variant of the second version above , since it will also result in an array output.

Here , when I use the term datarange , I mean a range of cells , which may be a single column multiple rows range , or a single row multiple columns range or a combination with multiple rows and multiple columns.

When I use the term scalar value , I mean a single value , which is to be used by referring to an Excel worksheet cell. The first version can also use a literal value , where the scalar value is directly specified in the formula , but the second version cannot.

To make all of this concrete , let us consider a datarange which is G1:G10 , and which consists of the letters :

G1 : A
G2 : B
G3 : A
G4 : blank
G5 : A
G6 : B
G7 : C
G8 : C
G9 : blank
G10 : A

We can have the following :

=COUNTIF(datarange , "A") - returns 4

The above is the first version , but with the scalar value specified within the formula ; "A" is a literal.

We cannot use the following :

=COUNTIF("A" , datarange)

Suppose now we decide to have the scalar value 'A' in a worksheet cell , say M1 , then we can have :

=COUNTIF(datarange , M1) - returns 4

We can also have :

=COUNTIF(M1 , datarange) - returns 0 !

However , the 0 does not really represent the output , which is now an array of values :

{1;0;1;0;1;0;0;0;0;1}

What this tells us is that the value in M1 ( the letter A ) occurs in datarange in the first , third , fifth and tenth positions.

Generally speaking , using an OR around this formula , as in :

=OR(COUNTIF(M1 , datarange))

is perfectly valid , and in this case it will return TRUE provided the above formula is entered as an array formula , using CTRL SHIFT ENTER.

However , using an additional AND , as in :

=AND(OR(COUNTIF(M1 , datarange)))

is superfluous , since for the AND function there is only one parameter , which in this case is the OR function ; thus , if the OR function returns a TRUE output , the AND function will also return the same TRUE output ; similarly , if the OR function returns a FALSE output , the AND function will also return FALSE.

Narayan

 

[Grigoriy]

Hello, everyone!
Has anyone ever encountered boolean logic MINUS sign?
Something like IF the first statement is TRUE, the second CAN be either FALSE OR TRUE,
and if the 1-st is FALSE the second is ought to be FALSE and can not ever be TRUE (otherwise, the whole statement becomes "-1")?

 

[vletm]

Grigoriy
You should open a new thread.
( This thread is already few Years old. )
You would reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org