Sumif - FastExcel

[Naz]

Hi all,

I have a spreadsheet that I have been using for the year and now it is time
to update it. The biggest problem with the old one was the re-calculate time.
I have 2 worksheets in the workbook, 1 containing data and the other a List
of employees (over 500). The List sheet picks up the pay of each staff from
the data sheet every month. I used to use an array formula to do this then
used a conjoined sumif (Sumif(Range,PayRef&Month&Costcode,Sumrange) with
helper column as it was faster, but as the Data sheet gets bigger as the
years goes on the recalculate time is now at 12 secs.

Is there a faster alternative to Sumif?
Is there better method of summing large ranges of data (data sheet contains
14000 rows)?
Has anyone used the product FastExcel (www.fastexcel.com) to optimise there
spreadsheets and is it any good?

All help is greatly ppreciated

_______________________
Naz,
London

 

[Niek Otten]

I used FastExcel on a sheet with which I had recalc tome problems.
It helped me spot the problem area in minutes and then it didn't take much
time (in my case!) to cure the problem.
Summing large ranges is generally very, very fast. A helper column and a
normal SUM are probably faster than SUMIF.
Your problem might be the VLOOKUP. You probably use FALSE as 4th argument.
This causes Excel to search the range from top to bottom. If you can sort
the data and omit the 4th argument, you may find a great reduction in
calculation time. But you'll have to check yourself that the value to be
searched was indeed the one found (to check for missing data)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[RagDyeR]

Actually Niek, I believe that the False (0) argument *stops* at the *first*
exact match, while the True (1) argument causes the *entire* range to be
searched.

Did you actually find False to be slower then True?

Also, from personal experience, Index & Match are *much* faster then
Vlookup.
--

Regards,

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

I used FastExcel on a sheet with which I had recalc tome problems.
It helped me spot the problem area in minutes and then it didn't take much
time (in my case!) to cure the problem.
Summing large ranges is generally very, very fast. A helper column and a
normal SUM are probably faster than SUMIF.
Your problem might be the VLOOKUP. You probably use FALSE as 4th argument.
This causes Excel to search the range from top to bottom. If you can sort
the data and omit the 4th argument, you may find a great reduction in
calculation time. But you'll have to check yourself that the value to be
searched was indeed the one found (to check for missing data)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Niek Otten]

Hi RD,

Indeed with 4th argument FALSE, the search stops as soon as the value is
found. If it just isn't present, that means the whole table has to be
searched.
With a sorted list, necessary to be able to establish the closest match,
very fast search methods are possible and are indeed used by Excel.
It would be nice if you could instruct Excel to assume a sorted list,
although you require exact matches. Many people have built UDFs to do just
that.

10.000 VLOOKUPS in a 65000 elements table, value to be found somewhere in
the middle:

4th argument FALSE: 28 seconds. Value not present: 53 seconds.
4th argument TRUE: .032 seconds
INDEX and MATCH: .015 seconds

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Aladin Akyurek]

Actually Niek, I believe that the False (0) argument *stops* at the *first*
exact match, while the True (1) argument causes the *entire* range to be
searched.

No. With the match-type set to TRUE (or 1), the lookup functions execute
a binary search routine, while FALSE/0 causes a lineair search.

Did you actually find False to be slower then True?

Yes, absolutely.

Also, from personal experience, Index & Match are *much* faster then
Vlookup.

=INDEX(ReturnRange,MATCH(LookupValue,MatchRange,1))

and

=VLOOKUP(LookupValue,Table,Idx,1)

=LOOKUP(LookupValue,MatchRange,ReturnRange)

won't differ "much" in performance.

 

[Aladin Akyurek]

Hi all,

I have a spreadsheet that I have been using for the year and now it is time
to update it. The biggest problem with the old one was the re-calculate time.
I have 2 worksheets in the workbook, 1 containing data and the other a List
of employees (over 500). The List sheet picks up the pay of each staff from
the data sheet every month. I used to use an array formula to do this then
used a conjoined sumif (Sumif(Range,PayRef&Month&Costcode,Sumrange) with
helper column as it was faster, but as the Data sheet gets bigger as the
years goes on the recalculate time is now at 12 secs.

Is there a faster alternative to Sumif?
Is there better method of summing large ranges of data (data sheet contains
14000 rows)?
Has anyone used the product FastExcel (www.fastexcel.com) to optimise there
spreadsheets and is it any good?

All help is greatly ppreciated

_______________________
Naz,
London

FastExcel might be used the way profiler programs are used in a C or
Common Lisp environment. That is, as a diagnostic tool which can show
which formulas requires the most time. One important feature is that it
allows you to optimize the worksheet calculation sequence.

SumIf with concatenation of relevant ranges is defintely faster than an
equivalent SumProduct formula or a Sum formula that must operate on
conditional expressions (the so-called array formulas). It appears also
faster than a DSum formula, implemented with data tables, although they
are close in performance.

An optimization would be, if possible, to avoid formulas with Offset and
Indirect (as often used in setting up dynamic named ranges).

 

[Niek Otten]

<Many people have built UDFs to do just that.>

Here's an example of such a function. Timing the same test as in previous
post: 0.9 seconds. Which is proof that UDFs are always relatively slow, even
if you use Excels built-in functions.
===========================================================================================

Function VLookupSort(SearchArgument As Range, SearchTable As Range, _
ColumnNo As Long, Optional SortDirection, Optional NotFound)
' Works as Vlookup, exact match (4th argument = FALSE)
' But takes advantage of the fact that a table is sorted
' and thus is much faster
' Also permits table to be sorted descending (Sortdirection -1)
' Optional argument for return value if item not found, defaults to #NA
Dim ItemFound

If IsMissing(SortDirection) Then SortDirection = 1

ItemFound = Application.Match(SearchArgument, Intersect(SearchTable,
SearchTable.Cells(1).EntireColumn), _
SortDirection)
If SearchTable(ItemFound, 1) <> SearchArgument Then
If IsMissing(NotFound) Then
VLookupSort = CVErr(xlErrNA)
Else
VLookupSort = NotFound
End If
Else
VLookupSort = _
SearchTable(ItemFound, ColumnNo)
End If
End Function
===========================================================================================

 

[Naz]

Hi Niek,

Thanks for your response, the spreadsheet doesn't have VLookups, (I had them
banned a while ago !) and I know that its the sumif, as at the moment the
data is 14000 rows, if I changed it to say 7000 then the time halves.
I would be grateful if you could explain what you mean by

A helper column and a normal SUM are probably faster than SUMIF.


How would you use a normal SUM and helper column to achieve this?



_______________________
Naz,
London

 

[Niek Otten]

Say your formula is something like =SUMIF(A1:A30000,"A",B1:B30000)
You could also fill B1:B30000 with =IF(A1="A",B1,0) and in C have
=SUM(B1:B30000)

Depending on the number of cells in A and B, one method might be
considerably faster than the other


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[RagDyeR]

I was not exactly explicit enough when I stated that I found that Index &
Match was much faster then Vlookup.

To be more precise, in a 40,000 row by 56 column WB, accessing closed WBs on
different drives (servers), a double Vlookup (error trapping) formula, with
0 as 4th argument, was replaced with a Match + Index & Match (error
trapping) formula, with 0 as the 3rd argument.

The opening time of the original WB was just under 5 minutes.
With the revised Index & Match formulas, the time was reduced to just under
3 minutes.
Recalc times went from 1½ minutes to just under a minute.

To me, that's "much" faster.<g>

--

Regards,

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

Actually Niek, I believe that the False (0) argument *stops* at the *first*
exact match, while the True (1) argument causes the *entire* range to be
searched.

No. With the match-type set to TRUE (or 1), the lookup functions execute
a binary search routine, while FALSE/0 causes a lineair search.

Did you actually find False to be slower then True?

Yes, absolutely.

Also, from personal experience, Index & Match are *much* faster then
Vlookup.

=INDEX(ReturnRange,MATCH(LookupValue,MatchRange,1))

and

=VLOOKUP(LookupValue,Table,Idx,1)

=LOOKUP(LookupValue,MatchRange,ReturnRange)

won't differ "much" in performance.

 

[Aladin Akyurek]

Is it not possible to set the 40,000 x 56 area in ascending order and
maintain in the sorted state?

 

[RagDyer]

I guess I should apologize for again not being too clear (feel like a novice
OP).

There are approx. 80,000 lookup (Index - Match) formulas in this WB, 2 for
each record, where the records are all usually closed, and spread across 2
servers.
I mentioned the fact that there are 56 columns, since almost each is linked
directly to various cells (data) contained in each record (dyeorder), and
this linking contributes heavily to the open and recalc times in the WB.

I realized the reduction in opening time from just revising the lookup
formula from Vlookup to Index & Match.

And I'm repeating what I posted here a couple of weeks ago.
I made this revision after reading one or YOUR posts, a couple of years ago,
in which you stated a definite efficiency advantage in using Index & Match
as opposed to error trapping double Vlookups.

 

[Aladin Akyurek]

I guess I should apologize for again not being too clear (feel like a novice
OP).

There are approx. 80,000 lookup (Index - Match) formulas in this WB, 2 for
each record, where the records are all usually closed, and spread across 2
servers.
I mentioned the fact that there are 56 columns, since almost each is linked
directly to various cells (data) contained in each record (dyeorder), and
this linking contributes heavily to the open and recalc times in the WB.

I realized the reduction in opening time from just revising the lookup
formula from Vlookup to Index & Match.

And I'm repeating what I posted here a couple of weeks ago.
I made this revision after reading one or YOUR posts, a couple of years ago,
in which you stated a definite efficiency advantage in using Index & Match
as opposed to error trapping double Vlookups.
--
Regards,

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




WBs on

RD,

I'm aware of that as the reason can be gleaned from:

http://www.mrexcel.com/board2/viewtopic.php?t=40233

You didn't respond to:

"Is it not possible to set the 40,000 x 56 area in ascending order and
maintain in the sorted state?"

If the answer would be yes, you can really resort to a significantly
faster formula. <g>

For example:

Let A1:B11 house:

{"Item","Value";"DAX",5;"GDA",7;"MNA",4;"NGA",9;"PAX",3;"PDA",8;"QWA",6;"SAP",8;"TRA",2;"XZA",5}

Let also D39 house the following lookup values:

{"DAX";"MNA";"MIA";"PDA";"PKA";"SQA";"SAP"}

Ordinarily we invoke in E3 and copy down...

=VLOOKUP(D3,$A$2:$B$11,2,0)


If we can sort and maintain A2:B11 in ascending order on its
match-range, it's significantly faster to invoke in E3 and copy down:

=IF(LOOKUP(D3,$A$2:$A$11)=D3,LOOKUP(D3,$A$2:$B$11),"")

I picked up this setup from Charles Williams, which, I have to admit, is
also faster than a setup I devised (even published).

If you also add the following as the first record to the table:

=CHAR(1), =""

the LOOKUP formula won't turn up any #N/A, while the first one with
match-type set to 0 will need something like:

=IF(ISNUMBER(MATCH(D3,$A$2:$B$11,0)),VLOOKUP(D3,$A$2:$B$11,2,0),"")

to avoid #N/A's.

 

[RagDyeR]

<<<"You didn't respond to:
"Is it not possible to set the 40,000 x 56 area in ascending order and
maintain in the sorted state?"
If the answer would be yes, you can really resort to a significantly
faster formula. <g>">>>


I thought I did respond.

<<<"There are approx. 80,000 lookup (Index - Match) formulas in this WB, 2
for
each record, where the records are all usually closed, and spread across 2
servers.">>>

To rephrase my answer to your query, I am *not* looking up anything on this
database type WB.
This WB *contains* approximately 80,000 lookup type formulas which are
polling various WSs contained within each of the 40,00 records displayed in
this database.

So, this is *not* one formula searching a large WB, but many formulas
searching ,many WBs.
--

Regards,

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

I guess I should apologize for again not being too clear (feel like a novice
OP).

There are approx. 80,000 lookup (Index - Match) formulas in this WB, 2 for
each record, where the records are all usually closed, and spread across 2
servers.
I mentioned the fact that there are 56 columns, since almost each is linked
directly to various cells (data) contained in each record (dyeorder), and
this linking contributes heavily to the open and recalc times in the WB.

I realized the reduction in opening time from just revising the lookup
formula from Vlookup to Index & Match.

And I'm repeating what I posted here a couple of weeks ago.
I made this revision after reading one or YOUR posts, a couple of years ago,
in which you stated a definite efficiency advantage in using Index & Match
as opposed to error trapping double Vlookups.
--
Regards,

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




WBs on

RD,

I'm aware of that as the reason can be gleaned from:

http://www.mrexcel.com/board2/viewtopic.php?t=40233

You didn't respond to:

"Is it not possible to set the 40,000 x 56 area in ascending order and
maintain in the sorted state?"

If the answer would be yes, you can really resort to a significantly
faster formula. <g>

For example:

Let A1:B11 house:

{"Item","Value";"DAX",5;"GDA",7;"MNA",4;"NGA",9;"PAX",3;"PDA",8;"QWA",6;"SAP
",8;"TRA",2;"XZA",5}

Let also D39 house the following lookup values:

{"DAX";"MNA";"MIA";"PDA";"PKA";"SQA";"SAP"}

Ordinarily we invoke in E3 and copy down...

=VLOOKUP(D3,$A$2:$B$11,2,0)


If we can sort and maintain A2:B11 in ascending order on its
match-range, it's significantly faster to invoke in E3 and copy down:

=IF(LOOKUP(D3,$A$2:$A$11)=D3,LOOKUP(D3,$A$2:$B$11),"")

I picked up this setup from Charles Williams, which, I have to admit, is
also faster than a setup I devised (even published).

If you also add the following as the first record to the table:

=CHAR(1), =""

the LOOKUP formula won't turn up any #N/A, while the first one with
match-type set to 0 will need something like:

=IF(ISNUMBER(MATCH(D3,$A$2:$B$11,0)),VLOOKUP(D3,$A$2:$B$11,2,0),"")

to avoid #N/A's.