Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with databasefunction?
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. -- Amedee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with database function?
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. -- HTH Bob "Amedee Van Gasse" wrote in message ... 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. -- Amedee |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with databasefunction?
On 7 apr, 17:04, "Bob Phillips" wrote:
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with database function?
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). -- HTH Bob "Amedee Van Gasse" wrote in message ... On 7 apr, 17:04, "Bob Phillips" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with databasefunction?
On 7 apr, 17:29, "Bob Phillips" wrote:
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... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with database function?
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(C20,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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with databasefunction?
On 7 apr, 19:15, Charles Williams wrote:
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(C20,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. -- Amedee |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with database function?
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. -- HTH Bob "Amedee Van Gasse" wrote in message ... On 7 apr, 17:29, "Bob Phillips" wrote: 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... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with databasefunction?
On 8 apr, 12:12, "Bob Phillips" wrote:
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. -- Amedee |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
speeding up calculation: replacing array formula with database function?
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 -- HTH Bob "Amedee Van Gasse" wrote in message ... On 8 apr, 12:12, "Bob Phillips" wrote: 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. -- Amedee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing a data range in a nested array function | Excel Worksheet Functions | |||
Replacing a Table-array with a cell reference in vlookup | Excel Worksheet Functions | |||
Simplifying/speeding up this formula | Excel Worksheet Functions | |||
Speeding up calculations | Excel Discussion (Misc queries) | |||
Replacing a number value with text from array | Excel Worksheet Functions |