In what way has excel 2007 improved on Array functions?

[Bernard Liengme]

One way is to permit full column/row references
Example =SUMPRODUCT(--(A:A="Jones"), B:B)
works in XL2007 but not in earlier versions
best wishes

 

[T. Valko]

See this:

http://support.microsoft.com/kb/166342

 

[Shane Devenshire]

Hi,

1. Arrays can now use full column references - it was 65535 it is now
unlimited
2. Number of array formulas that can refer to another worksheet was limited
to 65,536 that limit is removed
3. Arrays can now use full row references - it was 255, it is now unlimited

=================

Please enter a quesion in the body of your emai; if you don't the web won't
let us reply, instead it displays the following error message.

"An error occurred while sending your post

We're sorry, but there was a problem with the system and your post was not
received. The error has been reported to Operations and will be investigated
as soon as possible. Please try again later."

This means, that to respond we need to start a separate thread - which is a
bad practice and wastes time.
In this case I needed to start another program, Windows Mail, to respond to
this.

 

[T. Valko]

3.Arrays can now use full row references - it was 255, it is now unlimited

AFAIK, arrays have always been able to use entire rows. At least in all the
"recent" versions this has been the case. Can't remember much before Excel
97

Excel 2002:

=SUM(IF(1:1="x",2:2))

Works just fine.

 

[Rick Rothstein]

As a matter of fact, if you put in the entire row reference this way...

=SUM(IF(A1:IV1="x",A2:IV2))

Excel will change it to this for you...

=SUM(IF(1:1="x",2:2))