I need help plotting averages of data based on multiple columns

so I have a sheet that I have made...
trying to plot data
have 3 columns of data
RPM, MAP, AFR
and I am tying to find an average value of AFR based on meet criteria of MAP and RPM

example being, I want to know average AFR between RPM 4750~5250 and MAP 92.5~97.5 (essentially data centered around 5000 RPM and 95 MAP)...

I will be doing this for multiple values...but I just need one version of the formula to be used and I will make changes to all cells necessary.
I'm basically plotting Average values over an X/Y Axis Chart
Charting average values, not just data points.


Spreadsheet attached with a small ish sample set and an example of the X/Y chart I am trying to populate with Avereage AFR at various Values


thanks in advance for all of your help

Assuming you are working in Excel 2007 or newer, it seems to me like the AVERAGEIFS() function should be able to do this for you. http://office.microsoft.com/en-us/ex...in=HA010277524

The Div/0 error is usually returned in cases where nothing in the data set meets all of the criteria. As I personally don't usually bother with error trapping, I'm not very good at it. There is the IFERROR() function that can be used =IFERROR(your function,what you want returned if your function returns an error). http://office.microsoft.com/en-us/ex...587.aspx?CTT=1

I just noticed that your sample file is in 2003 format. Do you need this to be backward compatible with 2003 and earlier? If so, we will probably have to do something different, because IFERROR and AVERAGEIFS are not available in earlier versions of Excel.

no... I just saved it as that..
it can be xlsx

it actually has to be xlsx to have enough cells for the data I am importing


IFERROR doesnt help as theres no way to tell it to look at every cell before averaging to determine if its blank or if it can use it for the average
Though I do use it to filter out Blank cells after the end result

for now, I made a setup worksheet...
tell it where the cells end..in the example..it was cell 184
the data logs I have actually have columns to JJ
so I also made a cell to tell it the data I want to plot...
just happens to be RPM is Column C and MAP is Column AD and AFR is Column J

then I used some Indirect() to fill in the formula....for now

here is what I ended up with for 95 KPA and 5000 RPM
Chart on a second page
=IFERROR(AVERAGEIFS(INDIRECT("'Import Data'!"&Setup!$B$5&3):INDIRECT("'Import Data'!"&Setup!$B$5&Setup!$B$6),INDIRECT("'Import Data'!"&Setup!$B$4&3):INDIRECT("'Import Data'!"&Setup!$B$4&Setup!$B$6),">"&AVERAGE(Chart!$A2:$A3),INDIRECT("'Import Data'!"&Setup!$B$4&3):INDIRECT("'Import Data'!"&Setup!$B$4&Setup!$B$6),"<"&AVERAGE(Chart!$A1:$A2),INDIRECT("'Import Data'!"&Setup!$B$3&3):INDIRECT("'Import Data'!"&Setup!$B$3&Setup!$B$6),">"&AVERAGE(K$19:L$19),INDIRECT("'Import Data'!"&Setup!$B$3&3):INDIRECT("'Import Data'!"&Setup!$B$3&Setup!$B$6),"<"&AVERAGE(L$19:M$19)),"")


have to change a little bit of it for the corners and edges of the table as there is no cell above/below the edges for the </> portions...and I dont want it to treat them as 0.....
but thats not hard.. just means I leave out a > portion and a < portion on some cells.