Average formula with filter for two criteria

Hi,

I have mastered the AVERAGE formula, including the specification of static criteria. For example, ($A$1..$K$5000,10,$E$1..$E$8) to lookup the block specified, to average numbers in the 10th column but to pick rows only where a column whose heading is denoted at E1 contains one of the values in E2 through E8. Here's an example row from the data block, where the value 107 in the 8th column matches the criteria:
1445585069 41 1300689105 87.123.456.43 479 nz c 107 overall a 5

This formula works for static values in the criteria block. I also need a formula that works for relative values. A summary sheet is to lay out averages of rows where, as well as the static criteria match, there is a match of both user number (in the first column below and corresponding to the 41 in the second column of the above example row) and also for a given supplier (in the top row below and corresponding to the 479 of the above example row).

479 45 466 33 234 42 33

41
42
43

Googling, I see a tip for Google Spreadsheet. From that, I have come up with this syntax:
=AVERAGE(filter($Rawdata:$A$1..$K$5000;($Rawdata:B=$A1)*($Rawdata:E=A$2),10,$E$1..$E$8))

I have tried this (as @davg) in QuattroPro (which I have access to currently) - but I get "invalid reference". I'd really appreciate any guidance in getting this to work in QuattroPro or in Excel - which I can use on a colleague's machine!

Many thanks,

Andy

Now in Excel, I've got the following to produce a value:
=AVERAGE(IF($B$14:$B$10000=$A5,$K$14:$K$10000))

I've also got multiple criteria to work, with:
=AVERAGE(IF(($B$14:$B$10000=$A5),AND($E$14:$E$10000=E$2),$K$14:$K$10000))

No syntax errors, but the math is not right. Each delivers the average of all values in the K14:K10000 range, not the average of those rows which match the specified criteria.

Can anyone see where I've gone wrong, please?

Try

=AVERAGE(IF(($B$14:$B$10000=$A5)*($E$14:$E$10000=E$2),$K$14:$K$10000))

Look at AVERAGIFS function.

Many thanks John.

AVERAGEIFS has cracked it - and allows a huge number of conditions, which should allow me to do everything I need to do!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.