Here's a revised sample construct (to suit actual layout):
http://cjoint.com/?breycwYwIH
TeamZR1_veformula_2.xls

... please look at the #NUM error in VE sheet
which gets values from data sheet and
then makes table from VE table.
What is needed to correct this ?

The earlier formula is an array formula which cannot accept entire col
references. Your 3 defined ranges: grmcyc, MapKpa, RPM were pointing to
entire col references (eg: =Data!$C:$C).

Let's redefine the 3 ranges in the formula to be dynamic ranges:

grmcyc =OFFSET(Data!$M$2,,,COUNTA(Data!$M:$M)-1)
MapKpa =OFFSET(Data!$N$2,,,COUNTA(Data!$N:$N)-1)
RPM =OFFSET(Data!$C$2,,,COUNTA(Data!$C:$C)-1)

Then in Sheet: VE,
we'll also revise the table's format to better suit, viz.:

Have the RPM running across in pairs (the lower & upper limits)
in C2:C3, B2:B3, D2:D3 ... viz:

RPM
400.0 800.0 1200.0
800.0 1200.0 1600.0 etc

and the MapKpa figures (again in pairs)
running down in A4:B4, A5:B5, A6:B6 ... , viz:

MapKpa
15.0 20.0
20.0 25.0
25.0 30.0
30.0 35.0
etc

Then put in the starting cell C4, and array-enter
(press CTRL+SHIFT+ENTER):

=IF(ISERROR(AVERAGE(IF((MapKpa=$A4)*(MapKpa<$B4)* (RPM=C$2)*(RPM<C$3),grmcy
c))),"---",AVERAGE(IF((MapKpa=$A4)*(MapKpa<$B4)*(RPM=C$2) *(RPM<C$3),grmcyc
)))

and copy C4 across and down to populate the table

The revised formula above is basically the same as before, except re-set to
point correctly at the limits for RPM & MapKpa (with no overlaps), and with
an error-trap added to return a neater: "---" instead of "ugly" error msgs.
It should work ok now.

P/s: Please keep discussions within the newsgroup
thread for the benefit of all.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---