speeding up calculation: replacing array formula with databasefunction?

[Amedee Van Gasse]

Hi,

I could use some advice on optimizing a formula.
I currently have the following array formula in the cells of column D:

{=MAX(IF($A$2:$A$13695=$A13695;$B$2:$B$13695;FALSE))}

In words: take the highest value of column B for every cell in B where
the value in column A is equal to the current value of A.

Example:
01/04/2010 5937 5937
01/04/2010 5936 5937
07/04/2010 5943 5943
07/04/2010 5942 5943

As we all know, excessive usage of array formulas makes calculation
painfully slow. As you can see I am now at row 13695 and rows keep
getting added.

Can the formula be constructed in another way, for example with
database functions.
I suppose that I could use the DMAX function, but I've been staring at
the third parameter (criterium) and I can't wrap my mind around it.
All documentation seems to suggest that I need a separate table for
the criterium.

If a database function is the wrong way to go, please tell me.

 

[Bob Phillips]

I would say it is a good way to go, but you do have to setup the criteria as
a separate table. But that is good IMO, as you have the criteria clearly
defined, visible and auditable, not embedded in a formula.

Plus, you don't have to worry about ranges, you can use the whole columns,
=DMAX(A:B,"Amt",K1:K2)

One other thing, the master data has to have headings.

 

[Amedee Van Gasse]

I would say it is a good way to go, but you do have to setup the criteria as
a separate table. But that is good IMO, as you have the criteria clearly
defined, visible and auditable, not embedded in a formula.

Plus, you don't have to worry about ranges, you can use the whole columns,
=DMAX(A:B,"Amt",K1:K2)

One other thing, the master data has to have headings.

Hi Bob,

No problem with the headings.

K1 is obviously the same as the heading of column A, "Date".
But I am really confused about what I should put in K2.

Perhaps I should mention that I don't need this DMAX value one time,
but on every row, depending on the current value in column A.

 

[Bob Phillips]

Amedee,

Your array formula was checking the dates against $A13695, so you can just
put =$A13695 in K2, that is what I did in my test, it does not have to be a
static value (It could even be the result of a formula, i.e. an expression).

 

[Amedee Van Gasse]

Amedee,

Your array formula was checking the dates against $A13695, so you can just
put =$A13695 in K2, that is what I did in my test, it does not have to be a
static value (It could even be the result of a formula, i.e. an expression).

Bob,

On row 2 the array formula is checking the dates against $A2.
On row 3 the array formula is checking the dates against $A3.
On row 4 the array formula is checking the dates against $A4.
....
On row 13695 the array formula is checking the dates against $A13695.

Does that mean that I have to enter an array formula in K2??
#confused...

 

[Charles Williams]

Assuming you data is sorted (or can be sorted) on column A and starts
in Row 2 then you don't need array formulas:

in column C (or somewhere suitable) add a helper column containing
=IF(A2<>A1,COUNTIF(A:A,"="&A2),0)
and fill down
in Column D put
=IF(C2>0,MAX(OFFSET(B2,0,0,C2,1)),D1)
and fill down

If you can Sort Ascending on Column A and Descending on Column B
then you would just need this formula (filled down) in D
=IF(A2<>A1,B2,D1)

regards
Charles

 

[Amedee Van Gasse]

Assuming you data is sorted (or can be sorted) on column A and starts
in Row 2 then you don't need array formulas:

Your assumption is correct. The data is (or can be) sorted: column A
ascending, column B descending.
One problem: the data in B isn't always numerical. Sometimes it is the
text "N/A", and that gets sorted before the numbers.
I will have to add a bit of code to the import function, to replace "N/
A" (or any non-numerical text) with the value 0.
Something like

shDAT.Cells(NewRow, 2).Value = Val(cvsf.getItem(i).Version)

so that Val("Kilmer") = 0.

in column C (or somewhere suitable) add a helper column containing
=IF(A2<>A1,COUNTIF(A:A,"="&A2),0)
and fill down
in Column D put
=IF(C2>0,MAX(OFFSET(B2,0,0,C2,1)),D1)
and fill down

If you can Sort Ascending on Column A and Descending on Column B
then you would just need this formula (filled down) in D
=IF(A2<>A1,B2,D1)

Meh. I should have thought of that. KISS.
Thanks Charles. Calculation is now several magnitudes faster.
I still have to clock the time increase because of implementing the
Val() function, but I can't imagine that it will add a lot.

 

[Bob Phillips]

Amedee,

I know Charles has suggested an alternative approach, but to get DMAX
working in your case I think you would need to have a table of all of the
unique dates, like so

Amt.........Amt.........Amt etc
01-Apr....07-Apr...........etc.

and then point at the relevant criteria.

For instance

=DMAX($A$1:$B$5,"Amt",INDEX($K$1:$L$2,0,MATCH(A2,$K$2:$L$2,0)))

where K2:L2 is that extended criteria table.

 

[Amedee Van Gasse]

Amedee,

I know Charles has suggested an alternative approach, but to get DMAX
working in your case I think you would need to have a table of all of the
unique dates, like so

Amt.........Amt.........Amt etc
01-Apr....07-Apr...........etc.

and then point at the relevant criteria.

For instance

=DMAX($A$1:$B$5,"Amt",INDEX($K$1:$L$2,0,MATCH(A2,$K$2:$L$2,0)))

where K2:L2 is that extended criteria table.

Thank you Bob, but that would increase complexity.
I went with Charles' approach.
By adding the Val() function in my import procedure, I could also
remove an IF in another formula.
All together I only had to change 3 lines of code.

But thanks anyway.

 

[Bob Phillips]

I agree, even though my suggestion isn't really complex Charles' is a
better approach for your situation. But as I had worked it out I shared it,
all adds to our knowledge base <bg>