INDEX use Question

S

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?
 
N

NBVC

Try instead:


Code:
--------------------
=SUM(AB5:INDEX(P5:AB5,MATCH(LARGE(COLUMN(P5:AB5)*(P5:AB5<>""),3),COLUMN(P5:AB5)*(P5:AB5<>""),0)))/3
--------------------


confirmed with CTRL+SHIFT+ENTER


Alter you other formula similarly...
 
T

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
 
S

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top