Multi-condition array formulas

[artofchemistry]

Hi all

I've been using multi-conditional array formulas to extract specific data from multiple tabs in a spreadsheet that meet certain criteria. They work absolutely perfect, however, they require so much processing power that I've had to switch from automatic to manual calculations for the entire workbook.

I work in the engineering industry so my application is a bit more complicated than the dataset below, but I'll just use this as a simple example.

Looking at the data-set, what I would want to calculate is, for example, the average income for employees whose postcode is between 2200 and 2500 AND who's age is less than 50. This is pretty easy to do with an '{=AVERAGE(IF((postcode>2200)*(postcode<2500)*(age<50),(INCOME)))}' where the words are just the cell-ref arrays.


[TABLE="width: 454"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Pet's Name[/TD]
[TD]Income[/TD]
[TD]Postcode[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Alpha[/TD]
[TD] $ 53,000[/TD]
[TD="align: right"]2215[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Bravo[/TD]
[TD] $ 74,000[/TD]
[TD="align: right"]2310[/TD]
[TD="align: right"]64[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Charlie[/TD]
[TD] $ 39,000[/TD]
[TD="align: right"]2165[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Delta[/TD]
[TD] $ 98,000[/TD]
[TD="align: right"]2946[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Echo[/TD]
[TD] $ 225,000[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]Foxtrot[/TD]
[TD] $ 48,000[/TD]
[TD="align: right"]2445[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]Charlie[/TD]
[TD] $ 85,000[/TD]
[TD="align: right"]2646[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

So, my question is, is there a more efficient or less processing intensive way to perform this calculation in excel?

Look forward to any responses.

many thanks

For reference, an example of an actual formula I use looks something like this: "=IF(COUNTBLANK(D$9)=1,"",IFERROR(AVERAGE(IF(('Op Data'!$H$6:$EU$6='Summary'!$B18)*('Op Data'!$H$7:$EU$7='Summary'!$C18)*('Op Data'!$H$5:$EU$5='Summary'!D$9)*('Op Data'!$H$1:$EU$1='Summary'!$B$1)*('Op Data'!$H$8:$EU$1398<>0),'Op Data'!$H$8:$EU$1398)),"-"))"

 

[Fazza]

you're welcome

there is a GUI for queries. It is like MS Access's interface. So one can do basic queries without learning SQL.
For example save the file & give the data a simple defined name, like "MyData"

Then ALT-D-N-N and follow the wizard. At the last step take the option to edit in MS Query. This opens the GUI & if you play around in MS Query you can learn a bit. Select the columns/fields, apply criteria, apply formulas like MAX/MIN/AVG/SUM, etc (join tables even). You can see the SQL via a button too.

all the best