Perhaps something along these lines would be of some help ..
A sample construct is available at:
http://www.savefile.com/files/8103514
Conditional_Averaging_TeamZR-1_wks.xls
Assuming source data is within A2:C11,
and we have the defined ranges:
MapKpa =Sheet1!$A$2:$A$11
RPM =Sheet1!$B$2:$B$11
grmcyc =Sheet1!$C$2:$C$11
With the lower & upper limits for MapKpa & RPM
specified in say, F2:F3 and G2:G3 respectively,
Put in F4, array-enter the formula
(i.e. press CTRL+SHIFT+ENTER):
=AVERAGE(IF((MapKpa=F2)*(MapKpa<=G2)*(RPM=F3)*(R PM<=G3),grmcyc))
F4 will return the required "Average grmcyc"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Team ZR-1" wrote in message
...
How do I correct to make conditonal formula with number values ?
My main worksheet has at leat 24 columns and as many as 30 rows, all have
numbered values. Rows are defined names.
I want to get average values when at least 2 conditions have certain
values.
I tried =SUM(IF(AND(MapKpa:MapKpa=40),(RPM:RPM=1000),grmcy c:grmcyc))
and answer given is really total of all 30,000 row values for grmcyc and
not
when mapkpa values = 40 and RPM = 1000.
Also best would be if the conditions allow lets say when Mapkpa is between
40 and 45 and RPMs between 1000 and 1500 to have a window of grmcyc
average
I used Sum but really want an average of grmcyc when Mapkpa and RPM meet
the
conditions so I can build a results table of what the grmcyc average value
was from 20 to 105 KPA in 5 KPA windows along with smaller RPM ranges.
Thanks,