| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Need some help with a formula...thanks in advance
I have a tab called Record. Here are the cells it contains:
A1: 0-.4 A2: .5-.9 A3: 1.0-1.4 A4: +1.5 A5: PL This repeats in cells A6-A10,A11-A15,etc. In another tab called Graph I have the following in cell B2 (cell B1: 0-.4) =VLOOKUP(B1,Record!A:D,4,0) The above formula works fine as it finds the first 0-.4 and gives me the number in cell D. I would like a formula for cell B3 to find the next occurance of 0-.4 and give me the number in the corresponding cell D. Anyone know how to do it? I belive it is something like this (this is another formula in my spreadhsheet but I can't follow it). I believe I could use the same formula but just need to reference the Record tab instead of the Scores tab, B1 instead of Capitals, and the Q and S references change to something. =IF(ISTEXT(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000 "))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Scores!$ Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000"))), ROW()-1))),"") |
|
#2
|
|||
|
|||
|
Try...
=INDEX(Record!D1:D100,SMALL(IF(Record!A1:A100=B1,R OW(Record!A1:A100)-ROW( Record!A1)+1),2)) ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly. Hope this helps! In article , Jambruins wrote: I have a tab called Record. Here are the cells it contains: A1: 0-.4 A2: .5-.9 A3: 1.0-1.4 A4: +1.5 A5: PL This repeats in cells A6-A10,A11-A15,etc. In another tab called Graph I have the following in cell B2 (cell B1: 0-.4) =VLOOKUP(B1,Record!A:D,4,0) The above formula works fine as it finds the first 0-.4 and gives me the number in cell D. I would like a formula for cell B3 to find the next occurance of 0-.4 and give me the number in the corresponding cell D. Anyone know how to do it? I belive it is something like this (this is another formula in my spreadhsheet but I can't follow it). I believe I could use the same formula but just need to reference the Record tab instead of the Scores tab, B1 instead of Capitals, and the Q and S references change to something. =IF(ISTEXT(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS" ,ROW(INDIRECT("1:50000"))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000 "))),ROW()-1))),"") |
|
#3
|
|||
|
|||
|
Hi!
Try this: Use this formula in Graph B2: =IF(COUNTIF(record!A:A,B$1)=ROWS($1:1),VLOOKUP(B$ 1,OFFSET(record!A$1,(ROW(1:1)-1)*5,,5,4),4,0),"") Copy down as needed. Biff "Jambruins" wrote in message ... I have a tab called Record. Here are the cells it contains: A1: 0-.4 A2: .5-.9 A3: 1.0-1.4 A4: +1.5 A5: PL This repeats in cells A6-A10,A11-A15,etc. In another tab called Graph I have the following in cell B2 (cell B1: 0-.4) =VLOOKUP(B1,Record!A:D,4,0) The above formula works fine as it finds the first 0-.4 and gives me the number in cell D. I would like a formula for cell B3 to find the next occurance of 0-.4 and give me the number in the corresponding cell D. Anyone know how to do it? I belive it is something like this (this is another formula in my spreadhsheet but I can't follow it). I believe I could use the same formula but just need to reference the Record tab instead of the Scores tab, B1 instead of Capitals, and the Q and S references change to something. =IF(ISTEXT(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000 "))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Scores!$ Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000"))), ROW()-1))),"") |
|
#4
|
|||
|
|||
|
Need some help with a formula...thanks in advance
thanks Domenic, it works perfect.
"Domenic" wrote: Try... =INDEX(Record!D1:D100,SMALL(IF(Record!A1:A100=B1,R OW(Record!A1:A100)-ROW( Record!A1)+1),2)) ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly. Hope this helps! In article , Jambruins wrote: I have a tab called Record. Here are the cells it contains: A1: 0-.4 A2: .5-.9 A3: 1.0-1.4 A4: +1.5 A5: PL This repeats in cells A6-A10,A11-A15,etc. In another tab called Graph I have the following in cell B2 (cell B1: 0-.4) =VLOOKUP(B1,Record!A:D,4,0) The above formula works fine as it finds the first 0-.4 and gives me the number in cell D. I would like a formula for cell B3 to find the next occurance of 0-.4 and give me the number in the corresponding cell D. Anyone know how to do it? I belive it is something like this (this is another formula in my spreadhsheet but I can't follow it). I believe I could use the same formula but just need to reference the Record tab instead of the Scores tab, B1 instead of Capitals, and the Q and S references change to something. =IF(ISTEXT(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS" ,ROW(INDIRECT("1:50000"))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000 "))),ROW()-1))),"") |
|
#5
|
|||
|
|||
|
Need some help with a formula...thanks in advance
Domenic,
Acutally there is still a problem. If I put the formula you gave me into cell B3 it does find the next occurance of 0-.4. However, I copied the formula down but cell B4 gives me the same answer as cell B3. Should I be using $ before and after the A and D in the formula? Should the +1, 2 change when I copy it down? Thanks "Domenic" wrote: Try... =INDEX(Record!D1:D100,SMALL(IF(Record!A1:A100=B1,R OW(Record!A1:A100)-ROW( Record!A1)+1),2)) ....confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly. Hope this helps! In article , Jambruins wrote: I have a tab called Record. Here are the cells it contains: A1: 0-.4 A2: .5-.9 A3: 1.0-1.4 A4: +1.5 A5: PL This repeats in cells A6-A10,A11-A15,etc. In another tab called Graph I have the following in cell B2 (cell B1: 0-.4) =VLOOKUP(B1,Record!A:D,4,0) The above formula works fine as it finds the first 0-.4 and gives me the number in cell D. I would like a formula for cell B3 to find the next occurance of 0-.4 and give me the number in the corresponding cell D. Anyone know how to do it? I belive it is something like this (this is another formula in my spreadhsheet but I can't follow it). I believe I could use the same formula but just need to reference the Record tab instead of the Scores tab, B1 instead of Capitals, and the Q and S references change to something. =IF(ISTEXT(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS" ,ROW(INDIRECT("1:50000"))),ROW()-1))),(INDEX(Scores!$S$1:$S$50000,SMALL(IF(Sco res!$Q$1:$Q$50000="CAPITALS",ROW(INDIRECT("1:50000 "))),ROW()-1))),"") |
|
#6
|
|||
|
|||
|
Need some help with a formula...thanks in advance
Try...
B3, copied down: =INDEX(Record!D$1:D$100,SMALL(IF(Record!$A$1:$A$10 0=$B$1,ROW(Record!$A$1: $A$100)-ROW(Record!$A$1)+1),ROWS(B$3:B3)+1)) ....confirmed with CONTROL+SHIFT+ENTER. This will return the second occurrence to B3, the third occurrence to B4, and so on. Hope this helps! In article , Jambruins wrote: Domenic, Acutally there is still a problem. If I put the formula you gave me into cell B3 it does find the next occurance of 0-.4. However, I copied the formula down but cell B4 gives me the same answer as cell B3. Should I be using $ before and after the A and D in the formula? Should the +1, 2 change when I copy it down? Thanks |
|
#7
|
|||
|
|||
|
Need some help with a formula...thanks in advance
thanks Dom, perfect
"Domenic" wrote: Try... B3, copied down: =INDEX(Record!D$1:D$100,SMALL(IF(Record!$A$1:$A$10 0=$B$1,ROW(Record!$A$1: $A$100)-ROW(Record!$A$1)+1),ROWS(B$3:B3)+1)) ....confirmed with CONTROL+SHIFT+ENTER. This will return the second occurrence to B3, the third occurrence to B4, and so on. Hope this helps! In article , Jambruins wrote: Domenic, Acutally there is still a problem. If I put the formula you gave me into cell B3 it does find the next occurance of 0-.4. However, I copied the formula down but cell B4 gives me the same answer as cell B3. Should I be using $ before and after the A and D in the formula? Should the +1, 2 change when I copy it down? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| dragging a formula | Excel Discussion (Misc queries) | |||
| Formula in VBasic | Excel Discussion (Misc queries) | |||
| IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
| Howdo U copy a formula down a column, that uses data in another w. | Excel Worksheet Functions | |||
| Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Linear Mode
