DAverage with dynamic constraints? (Excel 2000)

[ChrisGreaves]

How can one perform a DAverage (or similar) or create a Pivot Table with dynamic constraints?

The data is in the first block. The column “Filtered Score” is not part of the defined table of data; it has been added to assist in understanding the constraints.
The second block holds constraints for the DAverage function. The constraint is currently set to ask for all “APL BOREALIS” entries whose score is less than 10.
The third block holds an optimistic (but incorrect) summary.

I’d like to know, for each business, the average score of those scores which are less than 10, for each company.
My constraint table is set to isolate “APL Borealis” and Scores “<10”, and the DAverage function (cell F40) correctly reports 5.5.
Of course, flushed with success I’d like to fill-down the formula through F39:F45, but this won’t work, because the two-line constraint identifies “APL Borealis”.
I can’t work out how to make the constraints dependent on the row 39:45 where my DAverage is required.

In desperation I toyed with a Pivot table, but can’t see how to incorporate a constraint (“<10”) into the pivot table.

It is quite possible that my mind has “locked on” to an incorrect avenue of thought; I chose DAverage because I want an average with constraints, and recalled that the Database functions tended to utilize constraints.
I could write a VBA function, I know …

Thanks for any kicks, nudges or other devices that might point me in the right direction.
(signed) "Sleepy" of Toronto

[HansV]

I'd use an array formula using AVERAGE and IF.

Enter the following formula in E39 and confirm with Ctrl+Shift+Enter:

=AVERAGE(IF(($A$3:$A$30=A39)*($E$3:$E$30<10),$E$3:$E$30))

Fill down from E39 to E45.

[ChrisGreaves]

I'd use an array formula ...

Hans, thanks.
I too would have used an array formula, had I thought of one.
What a powerful device! And I've known of the technique Ctrl-Shift-Enter for years - Excel help has that lovely example of predicting house resale values - but haven't spent the time to familiarize myself with the higher-level understanding of the technique.

This is a brilliant use of two common functions combined into an array-like solution.

Go to the top of the class!

[HansV]

If you search this forum and its equivalent in the Windows Secrets Lounge (and no doubt other discussion boards) for array formula, you'll find many other applications of the same idea.

They are all based on some interesting/useful properties of Excel:
1. An expression such as $A$3:$A$30=A39 results in an array of TRUE/FALSE values.
2. If you perform calculations (such as multiplication) with TRUE/FALSE values, TRUE is equivalent to 1 (not to -1 as in VBA!) and FALSE to 0.
3. Aggregation functions such as SUM and AVERAGE only look at numeric values; they ignore text and booleans.