C Chip Pearson Sep 16, 2003 #2 You have to convert the strings returned by ADDRESS to an actual range using INDIRECT. E.g., =AVERAGE(INDIRECT(ADDRESS(4,26)&":"&ADDRESS(4,33)))
You have to convert the strings returned by ADDRESS to an actual range using INDIRECT. E.g., =AVERAGE(INDIRECT(ADDRESS(4,26)&":"&ADDRESS(4,33)))
H Harlan Grove Sep 16, 2003 #3 You have to convert the strings returned by ADDRESS to an actual range using INDIRECT. E.g., =AVERAGE(INDIRECT(ADDRESS(4,26)&":"&ADDRESS(4,33))) Click to expand... ... Or eliminate the unnecessary ADDRESS calls. Assuming this is actually parametrized as =AVERAGE(INDIRECT(ADDRESS(w,x)&":"&ADDRESS(y,z))), try =AVERAGE(INDIRECT("R"&w&"C"&x&":R"&y&"C"&z,0)) [safest] or =AVERAGE(OFFSET($A$1,x-1,w-1,y-w+1,z-x+1)) [shortest & maybe fastest]
You have to convert the strings returned by ADDRESS to an actual range using INDIRECT. E.g., =AVERAGE(INDIRECT(ADDRESS(4,26)&":"&ADDRESS(4,33))) Click to expand... ... Or eliminate the unnecessary ADDRESS calls. Assuming this is actually parametrized as =AVERAGE(INDIRECT(ADDRESS(w,x)&":"&ADDRESS(y,z))), try =AVERAGE(INDIRECT("R"&w&"C"&x&":R"&y&"C"&z,0)) [safest] or =AVERAGE(OFFSET($A$1,x-1,w-1,y-w+1,z-x+1)) [shortest & maybe fastest]
