Home |
Search |
Today's Posts |
#1
|
|||
|
|||
improve formula offset and indirect
Hi,
Can someone have a look at the below formula and some ideas on how to improve the arrays, I understand I should be using the offset and indirect to find the exact range to improve performance, however not to sure where to brgin with this. =SUM(IF((DUDListData!$T$2:$T$65536=DUDListData!$Y$ 1)*(DUDListData!$U$2:$U$65536=Summary!$G$2)=1,1,0) )/SUM(IF(DUDListData!$U$2:$U$65536=Summary!$G$2,1,0) ) Many thanks John |
#2
|
|||
|
|||
Hi!
IF you used dynamic named ranges: Normally entered: =SUMPRODUCT(--(Rng1=DUDListData!$Y$1),--(Rng2=Summary!$G$2))/COUNTIF(Rng2,Summary!$G$2) Biff "John Contact" wrote in message ... Hi, Can someone have a look at the below formula and some ideas on how to improve the arrays, I understand I should be using the offset and indirect to find the exact range to improve performance, however not to sure where to brgin with this. =SUM(IF((DUDListData!$T$2:$T$65536=DUDListData!$Y$ 1)*(DUDListData!$U$2:$U$65536=Summary!$G$2)=1,1,0) )/SUM(IF(DUDListData!$U$2:$U$65536=Summary!$G$2,1,0) ) Many thanks John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT, OFFSET et. al. | Excel Worksheet Functions | |||
offset and indirect function | Excel Worksheet Functions | |||
offset and indirect function | Excel Worksheet Functions | |||
Offset and Indirect functions | Excel Discussion (Misc queries) | |||
Offset, indirect, match function limitation on linked worksheets. | Excel Worksheet Functions |