The extraordinary advantage of "Array Formula"

[wilchong via OfficeKB.com]

This ordinary Excel formula: SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A)) can
be replaced by array formula: {SUM(x = x)*( y = y)*(z = z)*(A:A))}.

Both formula will produce the same result. My question is that what is the
extra advantage on "array formula" over the ordinary formula.

Please advice.
Wilchong

 

[JE McGimpsey]

This ordinary Excel formula: SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A)) can
be replaced by array formula: {SUM(x = x)*( y = y)*(z = z)*(A:A))}.

Both formula will produce the same result. My question is that what is the
extra advantage on "array formula" over the ordinary formula.

Both are actually array formulae, but you don't need to use
CTRL-SHIFT-ENTER with SUMPRODUCT.

In general, presumably because of its optimized multiplication routines,

=SUMPRODUCT((x=x)*(y=y)*(z=z),(aj:ak))

calculates very slightly faster than

{=SUM((x=x)*(y=y)*(z=z)*(aj:ak))}

at least in XL versions prior to XL07 (I haven't seen that version
tested).

However, in the SUMPRODUCT() formula above, the use of the * operator
will cause the

(x=x)*(y=y)*(z=z)

portion to be calculated before passing the result on to those optimized
multiplication routines. So (again, at least in pre-XL07 versions) this
formula is even more efficient and quick to calculate:

=SUMPRODUCT(--(x=x),--(y=y),--(z=z),(aj:ak))

where the use of negation operators to convert boolean (TRUE/FALSE)
values to numeric 1/0s is very fast.

Of course, if you only have the one formula, you probably won't know
what to do with the microseconds that you save with that last formula.
But if you have thousands of these formulae in a calculation intensive
workbook, the difference may be perceptible.

 

[MartinW]

None, IMO. Array formulae are great in that they can do many
things more efficiently than can be done with normal formula,
However, when you can achieve the same funcionality from
a standard formula it is far better to use that, rather than the array.

I should add that this is not an across board sort of thing.
Each situation needs to be assessed on it's merits depending
on what you have and what you are trying to achieve.

HTH
Martin

 

[wilchong via OfficeKB.com]

Dear JE McGimpsey,
Your view is exactly what happen to my problem. I have a very important
spreadsheet which contains more than 20,000 data located in number of
spreadsheets. I used SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A)) to sum up
the value. The spreadsheet is terrible slow in calculation.

However, you told me that SUMPRODUCT(--(x=x),--(y=y),--(z=z),(aj:ak)) can be
even quick and a lot of faster than SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A))
.. As a result, I took your advice and tried the formular, unfortunally
failed! I found your suggested formula: SUMPRODUCT(--(x=x),--(y=y),--(z=z),
(aj:ak)) is just change the operator " * " to "--", that is? How come cannot
work?

Please advice!

Many thanks for your time and effort!
Wilchong

 

[wilchong via OfficeKB.com]

Hello MartinW,
From your view, what is the key benefit using array formula for huge database,
at the same time, normal formula also can achieve the same result like array
formula?
Please advice.

By the way, what is IMO stands for?
Wilchong

None, IMO. Array formulae are great in that they can do many
things more efficiently than can be done with normal formula,
However, when you can achieve the same funcionality from
a standard formula it is far better to use that, rather than the array.

I should add that this is not an across board sort of thing.
Each situation needs to be assessed on it's merits depending
on what you have and what you are trying to achieve.

HTH
Martin

This ordinary Excel formula: SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A))
can

[quoted text clipped - 6 lines]

Please advice.
Wilchong

 

[JE McGimpsey]

However, you told me that SUMPRODUCT(--(x=x),--(y=y),--(z=z),(aj:ak)) can be
even quick and a lot of faster than SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A))
.

No, I said they were quicker, but not "a lot" quicker. Frankly, the
improvements in hardware speed over the last several years has made the
difference even more slight.

To see why the double negation operators work, see

http://www.mcgimpsey.com/excel/doubleneg.html

 

[MartinW]

Hi Wilchong,

First up, IMO stands for In My Opinion you may also see it as
IMHO which is In My Humble Opinion.

To the problem at hand,
Charles Williams explains it far better than I can, he gives a lot
of information about this subject in this article he wrote for Microsoft.
http://msdn.microsoft.com/en-us/library/aa730921.aspx
It is a long read but very well worth it.

He also has a lot more information at his website.
http://www.decisionmodels.com/

HTH
Martin

Hello MartinW,
From your view, what is the key benefit using array formula for huge
database,
at the same time, normal formula also can achieve the same result like
array
formula?
Please advice.

By the way, what is IMO stands for?
Wilchong

None, IMO. Array formulae are great in that they can do many
things more efficiently than can be done with normal formula,
However, when you can achieve the same funcionality from
a standard formula it is far better to use that, rather than the array.

I should add that this is not an across board sort of thing.
Each situation needs to be assessed on it's merits depending
on what you have and what you are trying to achieve.

HTH
Martin

This ordinary Excel formula: SUMPRODUCT((x = x )*(y = y)*(z = z),(A:A))
can

[quoted text clipped - 6 lines]

Please advice.
Wilchong