Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formulas - too slow
Bob / Tom,
Thanks for your help. I have used Bob's array formula to the problem I had:- =INDEX(Sheet1!$J$1:$J$6,MATCH(1,(Sheet1!$A$1:$A$6= A8)*(MAX(IF((Sheet1!$A$1:$A$6=A8)*(Sheet1!$H$1:$H$ 6<39052),Sheet1!$H$1:$H$6))=Sheet1!$H$1:$H$6),0) ) I have to do some separate workings for this to work as I wanted it to. For a lot of the MAX values it was looking up I was getting a zero value which was correct. But if it returned a zero value I wanted it to look up the preceeding value for the previous MAX value. In any case:- Is there any way around the above formula without using an array formula as this creates massive downtime in my workbook. Is this a setup problem or a common problem with array formulas? Due to complexity of the formula etc and the array is this something that can be done in VB? Regards Matthew Balch |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formulas - too slow
Yes, an array formula can be can be slow to recalculate
Yes, you should be able to do it with macros. It depends on what you situation is whether that is practical or not. It is a one time thing or will the values being looked up or the values being returned change frequently. Or is it a batch thing, done periodically. Apparently Bob understands what you want, so I will defer to him. -- Regards, Tom Ogilvy "Matthew Balch" wrote in message ... Bob / Tom, Thanks for your help. I have used Bob's array formula to the problem I had:- =INDEX(Sheet1!$J$1:$J$6,MATCH(1,(Sheet1!$A$1:$A$6= A8)*(MAX(IF((Sheet1!$A$1:$A$6=A8)*(Sheet1!$H$1:$H$ 6<39052),Sheet1!$H$1:$H$6))=Sheet1!$H$1:$H$6),0) ) I have to do some separate workings for this to work as I wanted it to. For a lot of the MAX values it was looking up I was getting a zero value which was correct. But if it returned a zero value I wanted it to look up the preceeding value for the previous MAX value. In any case:- Is there any way around the above formula without using an array formula as this creates massive downtime in my workbook. Is this a setup problem or a common problem with array formulas? Due to complexity of the formula etc and the array is this something that can be done in VB? Regards Matthew Balch |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Large array, slow calcualtion of formulas | Excel Discussion (Misc queries) | |||
Super slow table array formulas | Excel Worksheet Functions | |||
MY ARRAY FORMULA IS SLOW IF DATA LIST IS MORE THAN 10000R0WS | Excel Programming | |||
Sumproduct formulas & slow response | Excel Worksheet Functions | |||
Slow Array formula or is there any alternative | Excel Programming |