Multi-cel array formula not working

**sunilmulay** · 06-21-2009, 10:29 PM

Hi there

Please see attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour.

Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.

Could you please help me figure out where the problem is?

Many thanks
Sunil

**Domenic** · 06-22-2009, 12:59 AM

In general, array formulas take longer to calculate. Nevertheless, for what it's worth try the following...

1) Select AQ50:IP50.

2) Enter the following formula and confirm with CONTROL+SHIFT+ENTER...

=IF((AQ10:IP10>=AG50)*(AQ16:IP16<=AH50),IF(AN50="Y",4,2),"")

**sunilmulay** · 06-22-2009, 01:56 AM

Hi Domenic

Thanks for your help - yes that works.

In my workbook users may create hundreds of rows such as that one. Before the user even starts the workbook stands at 15Mb in Excel 2003 format and 5Mb in 2007 format. This is why I though I'd try the array formulas as they would probably (hopefully) shrink the size down dramatically.

What's your advice/view on this?

And also, I see how you have structured your formula, which is of course much neater. Why didn't my version work though? Wasn't it technically correct?

Appreciate your feedback.

Many thanks
Sunil

**DonkeyOte** · 06-22-2009, 02:47 AM

File size aside Array formulae (incl. Sumproduct) are generally much slower performance wise than non-array formulae, this is amplified when used in conjunction with large ranges, worse still if used in a Volatile context ... it would be my advice to avoid using Arrays in any significant quantity, ie concentrate on efficiency of calculation rather than elegance.

**sunilmulay** · 06-22-2009, 08:46 AM

Thanks for your advice.

Reading through one of the links you forwarded, it seems that for range arrays there could be benefits in terms of speed as well as size.

I'm wondering if I should give it a try, because I am a bit concerned about the file size really. Speed wise, the workbook seems to be doing alright.....

many thank again - I will spend some time reading through those posts.....

Regards
Sunil