I know exactly what you're talking about.

That database I referred to, where I cut back the open and recalc times, was
a 40,000 row X 32 field sheet.

That's over one and a quarter million formulas.
You can picture what that was like at the start when there were double
Vlookups.

Being familiar with my experience using that old, large database sheet, and
plugging in your numbers, I figure that there's a 17% difference, that's 7½
minutes - over a 44 + minute open, where my original, *bad* open time was an
*actual* 24-25 minutes.

Even if your times are flawed (evenly flawed), they *still* show that 17%
*advantage*.

Interesting!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"T. Valko" wrote in message
...
Are we talking minutes, seconds, milliseconds ... ?

Milliseconds

Every millisecond adds up!

Note that those tests are on 1 single formula. If there were 100's or even
1000's of similar formulas the difference could be significant. Granted,
most files aren't big enough or so complex that maximum efficiency needs
to
be a consideration *but* if you get into the habit of being as efficient
as
possible to the best of ones ability all the time, then you're ahead of
the
curve.

--
Biff
Microsoft Excel MVP


"Ragdyer" wrote in message
...
So Biff, what time fragments do those numbers represent?

Are we talking minutes, seconds, milliseconds ... ?

What would those results equate to the OP's scenario?

I have personally saved almost 50% in opening and recalc times (25 down
to
14) for large WBs being used as databases simply by changing double
lookups
and Vlookups (w/error traps) to Index and Match formulas, so this is
*not*
a
confrontational question.
Are there really *appreciable* savings between the 2 versions?
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"T. Valko" wrote in message
...
calculation times screencap:

http://img267.imageshack.us/img267/6...ltimes1rq2.jpg

The multiplication version is slightly faster when the number of
variables
is small. As the number of variables increases the calc time also
increases
"signifcantly".

The --(ISNUMBER(MATCH(...)) version is faster in 2 out of 3 tests. Also
note
that the number of variables seems to not slow this version down
(although
the number of variables is small).

Calc times measured using Charles Williams RangeTimer method.

--
Biff
Microsoft Excel MVP


"Bernard Liengme" wrote in message
...
Thanks for this, We life and learn
cheers
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"RagDyer" wrote in message
...
If you've seen my posts over the years, you might have noticed my
*distaste* for the unary form of Sumproduct.

Besides the fact that it *can't* be used on different sized ranges
(column vs. row), and the fact that it allows text numbers to be
by-passed without any notice, it *also* doesn't work in this
scenario.

Just revise your test formula from unary to asterisk, which is the
form
my suggested formula used.

=SUMPRODUCT((A1:A9={"A","B"})*B1:B9)

No problem with this ... is there?
--
Regards,

RD



------------------------------------------------------------------------
-
--
Please keep all correspondence within the NewsGroup, so all may
benefit
!


------------------------------------------------------------------------
-
--
"Bernard Liengme" wrote in message
...
That will earn a #VALUE! error
Let A1:A9 have letters a, b or c in each cell
Let B1:B9 have numbers
Then your method becomes =SUMPRODUCT(--(A1:A9={"A","B"}),B1:B9)
But the part (A1:A9={"A","B"}) generates an 18 element array of
TRUE
and
FALSE values. But since B1:B9 is a nine element array, we have a
mismatch.

T Valko's method with ISNUMBER(MATCH overcomes the problem and let
one
use {"A","B"}

best wishes from Canada
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"RagDyer" wrote in message
...
Little shorter:


=SUMPRODUCT(('M:\WORKBOOK1\[SHEET1]SHEET1'!$A$7:$A$70={"INT","IT","MA"})
*'M:\WORKBOOK1\[SHEET1]SHEET1'!$AK$7:$AK$70)

--
HTH,

RD



----------------------------------------------------------------------
-
----
Please keep all correspondence within the NewsGroup, so all may
benefit
!


----------------------------------------------------------------------
-
----
"torooo" wrote in message


...
Thanks for your assistance, the formula works (without the extra
parenthesis).

Best