ARRAY FORMULAS IN ADVANCED FILTER ("CRITERIA")

[galileogali]

HELP ALADIN!!!!!
WHERE IS MR. AKYUREK?


ADVANCED FILTERS: CRITERIA WITH FORMULAS
My questions:
1) CAN BE USED ARRAYS FORMULAS TO DEFINE CRITERIA OF ADVANCED FILTERS?


IF THE ANSWER IS “NOT”, IT IS TO ME SUFFICIENT. IF THE ANSWER IS “YES”, CAN CONTRIBUTE AN EXAMPLE?

2) CAN EXIST CASES IN THAT the DEFINITION OF CRITERIA MUST BE ADAPTED ACCORDING TO IS to FILTERING -in-place or to FILTERING in another place?


IF THE ANSWER IS “NOT”, IT IS TO ME SUFFICIENT. IF THE ANSWER IS “YES”, CAN CONTRIBUTE AN EXAMPLE?
GalileoGali

 

[PaddyD]

Not being Aladin, I'm tempted not to answer, but what the hell...

...why don't you tell us what you are actually trying to do?

 

[galileogali]

Excuse Me.

What it interests to me is a theoretical answer. SIMPLY: CAN BE USED ARRAY formulas lIKE CRITERIA IN ADVANCED FILTERS?


(The replacement by SUMAPRODUCTO WORKS. THAT ALREADY)

THANKS

GALI

 

[Aladin Akyurek]

Excuse Me.

What it interests to me is a theoretical answer. SIMPLY: CAN BE USED ARRAY formulas lIKE CRITERIA IN ADVANCED FILTERS?


(The replacement by SUMAPRODUCTO WORKS. THAT ALREADY)

THANKS

GALI

The answer is no. The proc for Advanced Filter runs the formula specified in the criteria range on every record. If the formula is a formula that requires control+shift+enter, the proc appears not to know what to do with "control+shift+enter." A limited search did not reveal any explicit note on having control+shift+enter'ed formulas in the criteria range. The issue is not that it has a problem with array objects as the case with SumProduct as you noticed proves. The following example corroborates this observation:

AdvFilterWithArrayFormulas.xls
	A	B	C	D	E	F
1					0.3
2	X	Y
3	a	2.3			FALSE
4	a	4.2			X	Y
5	b	3.4			a	4.2
6	b	5.2			b	3.4
7	c	4.2			b	5.2
8	a	3.8			c	4.2
9	a	2.9
10
Sheet1

The formula in the criteria range, in F3, is:

=EVAL(($B3-AVERAGE(IF($A$3:$A$9=A3,$B$3:$B$9)))>=$E$1)

If you remove EVAL...

=($B3-AVERAGE(IF($A$3:$A$9=A3,$B$3:$B$9)))>=$E$1

and confirm the formula with control+shift+enter, an empty result list is all what we get.

Conclusion: No CSE-formulas in the criteria range.

Note that this also confirms Paddy's experience reported in your repost-thread.

 

[galileogali]

Thank you, Aladin. Your answer has been very clear and very clear the "contra - example".