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.

Employee Name	Pet's Name	Income	Postcode	Age
A	Alpha	$ 53,000	2215	45
B	Bravo	$ 74,000	2310	64
C	Charlie	$ 39,000	2165	21
D	Delta	$ 98,000	2946	88
E	Echo	$ 225,000	2000	33
F	Foxtrot	$ 48,000	2445	46
G	Charlie	$ 85,000	2646	30

<tbody>
</tbody>

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)),"-"))"

 

[DRSteele]

Welcome to the forum.

This is certainly simpler. The functions like AVERAGEIFS were created to be more effciient than the AVERAGE(IF...) constructions that required CTRL+Shift+Enter. I also note that workbooks containing large numbers of array formulas will be slow, as will ones with even moderate occurrences of IFERROR (which is notoriously slow.)

Book1
	A	B	C	D	E	F	G	H	I	J
1	Employee Name	Pet's Name	Income	Postcode	Age		Postal Code range	2200	2500
2	A	Alpha	$53,000	2215	45		Age	50
3	B	Bravo	$74,000	2310	64					50500
4	C	Charlie	$39,000	2165	21
5	D	Delta	$98,000	2946	88
6	E	Echo	$225,000	2000	33
7	F	Foxtrot	$48,000	2445	46
8	G	Charlie	$85,000	2646	30
Sheet1

Cell Formulas
Range	Formula
J3	=AVERAGEIFS(C2:C8,D2:D8,"<="&I1,D2:D8,">="&H1,E2:E8,"<="&H2)

_{^{<strike>
</strike>}}

 

[artofchemistry]

Hi DrSteele,

Thanks for your quick reply.

I remember trying to use AVERAGEIFS when I was developing the spreadsheet, and have tried again to use it. I don't think it can work in my situation as the range is a matrix (x rows by y columns where x and y are >1) not just a single range as per my simple example above. This is why I went with the arrays originally. A better example would be similar to above but rather than the range being "C2:C8" it would be "C2:D8" and there'd be an extra condition to return the average "Income" or "Bonus" in addition to the other criteria - any thoughts on this?

Employee Name	Pet's Name	Income	Bonus	Postcode	Age
A	Alpha	$ 53,000	$ 5,000	2215	45
B	Bravo	$ 74,000	$ 10,000	2310	64
C	Charlie	$ 39,000	$ 3,500	2165	21
D	Delta	$ 98,000	$ 535	2946	88
E	Echo	$ 225,000	$ 300	2000	33
F	Foxtrot	$ 48,000	$ 3,900	2445	46
G	Charlie	$ 85,000	$ 8,000	2646	30

<colgroup><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

As for the IFERRORs being notorious for hogging processing power, thanks for the tip! I'll try cull these down as I do have ALOT of them in my spreadsheet. Do you think if I was to replace them with conditional formatting it would speed up? (ie anywhere there's an error, have the text colour take on the cell background colour. I don't want #N/As or DIVOs throughout my data tables).

Many thanks for your help :D

 

[Fazza]

At first glance, AVERAGEIFS looks suitable. (So long as no users of the spreadsheet have an Excel version that predates AVERAGEIFS.)

Another aproach is to use a query (table). for the specific example, the specific SQL would be

SELECT Avg(Income) AS [Average Income]
FROM YourDefinedName
WHERE Postcode BETWEEN 2200 AND 2500 AND Age < 50

Queries are excellent when there are large datasets.
VBA functions might be suitable too.

In practice, the approach that is best will depend on the specifics of what is required.
(BTW, I'm guessing that a solution with a lot of array formulas applied over large datasets will be the slowest approach.)

 

[DRSteele]

Let's see. Function AGGREGATE is fast, does not require CSE and handles arrays well. It does not handle arrays for the AVERAGE sub-function, but we can trick it by using the PERCENTILE sub-function (that's the 18 in argument1) with the k value being set to 0.5. So, we get the average by creating an array of values AND errors (which AGGREGATE will ignore when we put 6 in argument2) and calculating the 50th percentile.

So with our above data that now includes Bonus in ColumnD and all other columns shifted rightways by one, try this formula:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AGGREGATE(18,6,C2:D8/((E2:E8<=J1)*(E2:E8>=I1)*(F2:F8<=I2)),0.5)[/FONT]

 

[DRSteele]

If you have Excel365 Insider edition, the new function FILTER makes this task simple and very efficient.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AVERAGE(FILTER(C2:D8,(E2:E8<=J1)*(E2:E8>=I1)*(F2:F8<=I2)))[/FONT]

 

[artofchemistry]

Hi guys, sorry I meant to tag you in my reply but tagged you in this thread instead!

DrSteele,

Thanks once again for your response. The aggregate function works much better, but unfortunately it does not appear to return the true average of the dataset. The 50%ile returns the median number if the dataset has an odd number of data points or the average of the two central data points if there is an even number. So its CLOSE but not the true average, which unfortunately isn't OK for my application. Any idea of any other subfunctions we could use to get the actual average of the array?

Also, I don't have Excel365 insider edition so FILTER is not an option for me

Fazza,

Thanks for your response. I've considered using SQL but am kind of scared of having to learn how to use it. Would you recommend it for someone with decent excel skills and basic programming abilities? It may be worth the effort if its as efficient as i've heard.

Cheers and many thanks to you both.

 

[DRSteele]

Sorry my mistake. That percentile operation does not work. Before the end of Winter/19, MS will have all of the new functions available to all 365 users. So if you can get 365, you'll have an efficient solution to your problem.

 

[Fazza]

SQL is nothing to be scared of. It is simple - I think the solution shows that.

For most queries (basically other than UPDATE, DELETE & INSERT), the basic form is

SELECT fields you want
FROM table
WHERE criteria

text is delimited like 'this'

The value in learning SQL will depend on what you need to do. Likely you will find on-line help - like this forum - to give you solutions without actually needing to learn the specifics. regards

PS. SQL can be used in Excel (directly without programming) in query tables and pivot tables. When used in VBA there are further options like ADO.

 

[artofchemistry]

Excellent, thank you both for your help. You're legends