I read some docs that say this: AND and OR functions can be used as criteria in DB functions. It gives this example:

=AND(E4>F9,D7<A5,M3>G1,B1=“FY 2010”)



with the final argument being the result that is displayed (I presume meaning if that above is listed in the criteria, it would resulst in FY 2010 being the criteria).

But I can't seem to find anything that indicates that AND would work like that, or make anything work with it.

Any comments/thoughts?

This statement is perfectly valid and will return FALSE or TRUE. What problem do you have?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Maybe my experimentation with it has been wrong.

The problem that I have is that the criteria in all the examples in the documentation (not using any AND statements in examples), expects a criteria statement, such as B1=FY 2010, not a True or a False value. It is referring to DB functions.

Below is the definition of what Criteria is from the Excel Help:

That is not a True or False value. I've tried entering the AND in the criteria similar to the example and it always returns an error. Again, the document says that the AND and OR functions can be used as Criteria in DB function. It appears to me the document I am reading is wrong.

The helpfile that I have says that you can create AND and OR logic in your criteria by correctly laying out (horizontal and vertical) the criteria in a range.

For example, the helpfile says that to create this logic:
Boolean logic: (Salesperson = "Davolio" OR Salesperson = "Buchanan")
you have to lay out the Salespersons as a list in the same column (there's a picture demonstrating it).

If your source document is the helpfile then I think you've misunderstood "AND" and "OR" boolean logic to mean the AND() and OR() worksheet functions. If your document is something else then point us to it?

__________________
Colin

RAD Excel Blog

Thanks, yes, using it within a function as you describe would be able to be done, as you say.

I can't specifically point you to the document, as it is printed, and copyrighted. But I think that the document, on this particular topic, is written poorly. It lists it exactly as I wrote in the initial post. And it has the title on the page that it can be used that way in DB functions. Based on being written that way, and based on the fact that earlier in the document they discussed the AND and OR functions, also exampled the same way, I presumed that was what they meant.

I appreciate the feedback. I think the final thought is that they meant as you described, which then does make sense.

Oh, but you can use formulas which return TRUE or FALSE as criteria too. For calculated criteria like this, you must leave the column header blank. For example, in the helpfile:
So with your formula:
=AND(E4>F9,D7<A5,M3>G1,B1=“FY 2010”)

It should return TRUE if all 3 criteria are met or FALSE if they aren't. When you use it in your criteria range, provided you have followed the rules in the helpfile, it should act as a filter accordingly.

__________________
Colin

RAD Excel Blog

Funny. I read that a bit ago, and it didn't sink in. I was focused on a different aspect.

Ok. I may be wrong, but it seems to me that the only case where you would use a criteria that evaluates to True or False is in a situation where you would want to return either All or None of the DB that is being referenced. Would that be right? It seems to me that is all that could happen when using True or False as a criteria.

No, the criteria in your formula is applied to each row in the database. So you can use it to apply criteria across a row in the database which would be difficult or impossible to do by laying out the criteria how I mentioned earlier.

I've attached an example. In it I want to get a sum but only where the name begins with a capital "C". The criteria I'm using is:

=EXACT(LEFT(B3,1),"C")

So the LEFT() function gets the first letter and EXACT() returns TRUE or FALSE depending on if the first letter is a capital "C".

Does that make sense?

Attached Files

Book1.xlsx (8.6 KB, 7 views)

__________________
Colin

RAD Excel Blog

Hi, yes, that all makes sense, and I follow.

I think where the confusion is, is that I was interpreting the statement of returning True or False literally, ie, useing the value True as THE criteria. Not the answer to the criteria.

I'm in synch.

I appreciate your input.

By the way...anyone reading this thread...if you read my original post, the problem and hence my conofusion, is that the third argument -> B1-"FY 2010", is not a third criteria, but the results displayed if the first two arguments, as criteria, is true.

My thought is that the entire example is incorrect, and that the third argument in the AND would be criteria, not results.

Sorry, but that's incorrect.

In this formula:
=AND(E4>F9,D7<A5,M3>G1,B1="FY 2010")

All of the items inside the AND() are criteria.

E4>F9 is criteria1 - a comparison which returns True or False
D7<A5 is criteria2 - a comparison which returns True of False
M4>G1 is criteria3 - a comparison which returns True or False
B1="FY 2010" is criteria4 - a comparison which returns True or False

Then
=AND(criteria1 , criteria2, criteria3, criteria4)

returns True if all four criteria are True, otherwise it returns False.

__________________
Colin

RAD Excel Blog