If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
INDEX use Question
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:AB 5""),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? |
#2
|
|||
|
|||
INDEX use Question
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... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109223 |
#3
|
|||
|
|||
INDEX use Question
.. 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 --- "Smitty" wrote: 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:AB 5""),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? |
#4
|
|||
|
|||
INDEX use Question
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 "Smitty" wrote in message ... 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:AB 5""),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? |
#5
|
|||
|
|||
INDEX use Question
"T. Valko" wrote:
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:AB 5""),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. |
#6
|
|||
|
|||
INDEX use Question
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 -- Biff Microsoft Excel MVP "Smitty" wrote in message ... "T. Valko" wrote: 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:AB 5""),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. |
Thread Tools | |
Display Modes | |
|
|