INDEX use Question

[Smitty]

I'm calculating averages on 2 different golf courses. For each course, I'm
using the formulas:
..
F1 - {=SUM(M5:INDEX(A5:M5,LARGE(COLUMN(A5:M5)*(A5:M5<>""),3)))/3}
F2 - {=SUM(AB5:INDEX(P5:AB5,LARGE(COLUMN(P5:AB5)*(P5:AB5<>""),3)))/3}
..
BOTH formulas are on the same row (Row 5). F1 works great to find the last 3
scores. F2 gives a #REF! error. F2 seems to fail at the INDEX calc. Does F2
formula not work because it does not start with column A?

 

[Max]

.. F2 gives a #REF! error

Are there any #REF! error(s) within the range P5:AB5 ?
Worth checking out this possibility as a first resort
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---

 

[T. Valko]

Does F2 formula not work because it does not
start with column A?

Probably. It depends on what this evaluates to:

COLUMN(P5:AB5)*(P5:AB5<>"")

F1 - INDEX(A5:M5
F2 - INDEX(P5:AB5

In both formulas you're indexing a range. When you index a range the INDEX
function "stores" the values of the range in *specific positions*. These
positions are numbered starting from 1 to the total number of cells in the
range.

A5:M5 =13 cells = positions 1 to 13
P5:AB5 = 13 cells = positions 1 to 13

When you calculate this:

COLUMN(P5:AB5)*(P5:AB5<>"")

It returns the *column number* and the column number is probably outside the
position numbers. For example, it might return column number 20 but 20 is
outside the position range of 1 to 13. What you have to do is convert the
column numbers to the actual position numbers of the indexed range. In the
F1 formula this happens naturally since the indexed range starts at cell A5
which is column 1 and this aligns with position 1.

So, try it like this (array entered):

=SUM(AB5:INDEX(P5:AB5,LARGE(COLUMN(P5:AB5)*(P5:AB5<>""),3)-COLUMN(P5)+1))/3

 

[Smitty]

:

Does F2 formula not work because it does not start with column A? Probably.
It depends on what this evaluates to:

COLUMN(P5:AB5)*(P5:AB5<>"")

F1 - INDEX(A5:M5
F2 - INDEX(P5:AB5

In both formulas you're indexing a range. When you index a range the INDEX
function "stores" the values of the range in *specific positions*. These
positions are numbered starting from 1 to the total number of cells in the
range.

A5:M5 =13 cells = positions 1 to 13
P5:AB5 = 13 cells = positions 1 to 13

When you calculate this:

COLUMN(P5:AB5)*(P5:AB5<>"")

It returns the *column number* and the column number is probably outside the
position numbers. For example, it might return column number 20 but 20 is
outside the position range of 1 to 13. What you have to do is convert the
column numbers to the actual position numbers of the indexed range. In the
F1 formula this happens naturally since the indexed range starts at cell A5
which is column 1 and this aligns with position 1.

So, try it like this (array entered):

{=SUM(AB5:INDEX(P5:AB5,LARGE(COLUMN(P5:AB5)*(P5:AB5<>""),3)-COLUMN(P5)+1))/3}

Biff
Microsoft Excel MVP
------------------------
Biff,

You really are a MVP!!

Your formula works correctly! I assume the addition of "-COLUMN(P5)+1" to my
existing formula defines the actual position numbers of the indexed range,
because I do not start with position 1.

 

[T. Valko]

Here's how it works:

P5 = column 16
Q5 = column 17
R5 = column 18
S5 = column 19

We need to convert 16:19 to 1:4

COLUMN(P5)-COLUMN(P5)+1 = 1

It breaks down to 16-16+1=1, so:

COLUMN(P5)-COLUMN(P5)+1 = 1
COLUMN(Q5)-COLUMN(P5)+1 = 2
COLUMN(R5)-COLUMN(P5)+1 = 3
COLUMN(S5)-COLUMN(P5)+1 = 4