Excel 2003 - Logical Expressions

  • Thread starter Thread starter Craig Brandt
  • Start date Start date
C

Craig Brandt

Puzzling:

I have a table with several named columns and the cells in the columns are
boolean data (True or False).
If I put the following formula several columns to the right in row 10:

=IF(WB001B,A10,"")

it copies the data from column A to the cell. If I replace the formula with
this formula:

=IF(TC,A10,"")

it also copies the data over, indicating that the two named variables in row
10, TC and WB001, are both TRUE (which they are). But if I replace the
formula with

=IF(AND(WB001B,TC),A10,"")

It does not bring over A10.

Why not?

Craig
 
Craig,

That is by design.

Both TC and WB001B are named arrays - But when they are entered by themselves, Excel just passes the
single element from the same row of the array to the formula. (You get an error if you do that on a
row where the named range isn't defined...)

When you enter theose names in the AND function, Excel processes the two arrays together, and if any
element multiplication returns False, the whole AND returns False.

To return to the use of only the values from the same row, use

=IF(WB001B*TC,A10,"")


HTH,
Bernie
MS Excel MVP
 
Bernie:

Thanks for the response. I tried it and it works but I need a little more
help.

What I was planning on doing is use the NOT function to create a formula
where the user can select only those "A10's" where certain conditions apply
and if I was only looking for "TRUE" conditions the * solution will work.
How do I handle the cases where I want the "A10's" where several conditions
are true and several must be false?

Craig


Bernie Deitrick said:
Craig,

That is by design.

Both TC and WB001B are named arrays - But when they are entered by
Click to expand...
themselves, Excel just passes the
 
Hi Craig,

The IF function can only accept one condition. So if you supply a whole
column, like WB001, it assumes you mean the cell in that column that is on
the same row as the IF function itself.
This is called implicit intersection.
The AND function however, can accept more than one condition. So no
intersection takes place. You can see it happening if you use Evaluate
Formula.
 
Craig,

Something along the lines of

=IF(Name1*Name2*Name3*NOT(Name4)*NOT(Name5),A10,"")

where Name1,2,3 must be TRUE, and Name4,5 must be FALSE.

In this logic system, a formula with addition, like so

=IF(Name1+Name5,A10,"")

is used for OR, * is used for AND, NOT( Name1 * Name 2 ) is NAND, NOT( Name1
+ Name 2 ) is NOR, and so on.....

HTH,
Bernie
 
To avoid this, you can use *explicit* intersection:

=IF(AND(WB001B 10:10,TC 10:10),A10,"")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel
 
Back
Top