Home |
Search |
Today's Posts |
#1
|
|||
|
|||
finding the largest number from the last row and look up its name.
Hi,
I have a spreadsheet that contains the following information: 1) Col A - time stamps (number of entries can change). 2) Col B to Col M contain data corresponding to ColA. Each col is independent of each other. From ColA thru M all have same number of rows. I need to be able to find the largest number from ColB to ColM on the last none zone row. Therefore, if there are 10 rows, I am only insterested in getting the data on the 10th row. Once the value is retrieved, I'll need to lookup the 1st row on that col which contains the name of the test. say ColC has the largest number, then it needs to return C1. I hv tried using offset along w/ countA, i can't quite seem to put them together. Thanks, |
#2
|
|||
|
|||
One try ..
Assuming source table is in cols A to M in Sheet1, with *no* duplicate max numbers in the last row within cols B to M In Sheet2 ------------ Put in say, B2: =INDEX(Sheet1!1:1,MATCH(MAX(OFFSET(Sheet1!1:1,COUN TA(Sheet1!A:A)-1,)),OFFSET (Sheet1!1:1,COUNTA(Sheet1!A:A)-1,),0)) B2 should return what you're after -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jeff" wrote in message ... Hi, I have a spreadsheet that contains the following information: 1) Col A - time stamps (number of entries can change). 2) Col B to Col M contain data corresponding to ColA. Each col is independent of each other. From ColA thru M all have same number of rows. I need to be able to find the largest number from ColB to ColM on the last none zone row. Therefore, if there are 10 rows, I am only insterested in getting the data on the 10th row. Once the value is retrieved, I'll need to lookup the 1st row on that col which contains the name of the test. say ColC has the largest number, then it needs to return C1. I hv tried using offset along w/ countA, i can't quite seem to put them together. Thanks, |
#3
|
|||
|
|||
1) Col A - time stamps
Just in case the "time-stamps" in col A in Sheet1 may interfere, you could try instead in B2 (in Sheet2) =INDEX(Sheet1!B1:M1,MATCH(MAX(OFFSET(Sheet1!B1:M1, COUNTA(Sheet1!A:A)-1,)),OF FSET(Sheet1!B1:M1,COUNTA(Sheet1!A:A)-1,),0)) (It's the same formula as the earlier one essentially, but now with "Sheet1!B1:M1" replacing "Sheet1!1:1") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
Reluctantly...
=INDEX($B$1:$M$1,MATCH(MAX(INDEX(B:M,MATCH(9.99999 999999999E+307,A:A),0)),INDEX(B:M,MATCH(9.99999999 999999E+307,A:A),0),0)) In fact, you can have more than 1 test showing the same max score. Just picking up the first test from row 1 would be unsatisfactory. Lets try a different approach which takes the ties into account... To recap: The data range is A:M, with column A from A2 on housing dates and B1:M1 the headers. In N2 enter: =MATCH(9.99999999999999E+307,A:A) In N3 enter: Data In N4 enter & copy down: =IF(ROW()-ROW(N$4)+1<=12,INDEX($B:$M,$N$2,ROW()-ROW(N$4)+1),"") This formula extracts the last row of data in B:M and puts it in a vertical range. Note that this vertical range consists of 12 data points. In O3 enter: Rank In O4 enter & copy down: =IF(ISNUMBER(N4),RANK(N4,$N$4:$N$15)+COUNTIF($N$4: N4,N4)-1,"") This formula ranks the data points of interest. In P1 enter: 1 We call this the value of N of Top N. Your query involves in fact extracting a Top 1 test list on the basis of the last scores on the tests that are taken/applied. P2: =MAX(IF(INDEX(N4:N15,MATCH(P1,O4:O15,0))=N4:N15,O4 :O15))-P1 which must be confirmed with control+shift+enter instead of the usual enter. This formula determines the number of ties that Nth top value might have. This calculation allows for dynamically adjusting the size of the list of tests which satisfy the condition of being highest. In P3 enter: Top Test List In P4 enter & copy down: =IF(ROW()-ROW(P$4)+1<=$P$1+$P$2,INDEX($B$1:$M$1,MATCH(ROW()-ROW(P$4)+1,$O$4:$O$15,0)),"") which gives you the desired list of highest scoring tests. Jeff wrote: Hi, I have a spreadsheet that contains the following information: 1) Col A - time stamps (number of entries can change). 2) Col B to Col M contain data corresponding to ColA. Each col is independent of each other. From ColA thru M all have same number of rows. I need to be able to find the largest number from ColB to ColM on the last none zone row. Therefore, if there are 10 rows, I am only insterested in getting the data on the 10th row. Once the value is retrieved, I'll need to lookup the 1st row on that col which contains the name of the test. say ColC has the largest number, then it needs to return C1. I hv tried using offset along w/ countA, i can't quite seem to put them together. Thanks, |
#5
|
|||
|
|||
Thanks Max, it works after modifing it slightly.
=INDEX(B1:M1,1,MATCH(MAX(OFFSET(Sheet1!B2:M2,COUNT A(Sheet1!A:A)-1,)),OFFSET(Sheet1!B2:M2,COUNTA(Sheet1!A:A)-1,),0)) "Max" wrote: 1) Col A - time stamps Just in case the "time-stamps" in col A in Sheet1 may interfere, you could try instead in B2 (in Sheet2) =INDEX(Sheet1!B1:M1,MATCH(MAX(OFFSET(Sheet1!B1:M1, COUNTA(Sheet1!A:A)-1,)),OF FSET(Sheet1!B1:M1,COUNTA(Sheet1!A:A)-1,),0)) (It's the same formula as the earlier one essentially, but now with "Sheet1!B1:M1" replacing "Sheet1!1:1") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
Glad to hear that !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jeff" wrote in message ... Thanks Max, it works after modifing it slightly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|