AND function be used as criteria in DB functions?

Colin Legg · #1 11-20-2012, 10:24 AM

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:

Quote:

Criteria created as the result of a formula

You can use a calculated value that is the result of a formula as your criterion. Remember the following important points:

The formula must evaluate to TRUE or FALSE.
Because you are using a formula, enter the formula as you normally would, and do not type the expression in the following way: =''=entry''
Do not use a column label for criteria labels; either keep the criteria labels blank or use a label that is not a column label in the range (in the examples below, Calculated Average and Exact Match). If you use a column label in the formula instead of a relative cell reference or a range name, Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion. You can ignore this error because it does not affect how the range is filtered.
The formula that you use for criteria must use a relative reference to refer to the corresponding cell in the first row (in the examples below, C7 and A7).
All other references in the formula must be absolute references of criteria created as the result of a formula.

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.

56_kruiser · #2 11-20-2012, 10:28 AM

Quote:

Originally Posted by Colin Legg

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:

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.

56_kruiser · #3 12-06-2012, 01:28 PM

Quote:

Originally Posted by Colin Legg

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.

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Acos, Cos and Sin functions in DAX PowerPivot	eramzan	Excel	0	03-01-2012 11:32 AM
Using IF and Vlookup functions	cpladson	Excel	3	12-14-2011 11:57 PM
Assign functions to F1, etc.	ibclare	Office	4	06-02-2011 03:22 PM
** Using Functions	djreyrey	Excel Programming	10	02-08-2011 04:18 PM
Automated Functions??	nickypatterson	Outlook	0	08-27-2009 01:50 PM