| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting the first time a number appears
Dear Excel(lent) users,
I have a sheet in which I have the following columns: A : Date B: Number range, cummulatif +3 (positive estimation) C: Number range, cummulatif -2 (negative estimation) Now I want to know the following: I want to know the date in A, which is the first date a certain number is shown in Column B. For instance: A B C --------------------------------------------- 23-04-07 10 8 24-04-07 17 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 17 etc etc The first time the number 17 appears in column B is on 24-04-07 so I want that date as a return, the first time the number 17 appears in column C is on 28-04-07 so I want that date as a return. Can you please help me ?? -- ** Fool on the hill ** |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
If D2=17 then:
=IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0))) =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0))) HTH "Jaydubs" wrote: Dear Excel(lent) users, I have a sheet in which I have the following columns: A : Date B: Number range, cummulatif +3 (positive estimation) C: Number range, cummulatif -2 (negative estimation) Now I want to know the following: I want to know the date in A, which is the first date a certain number is shown in Column B. For instance: A B C --------------------------------------------- 23-04-07 10 8 24-04-07 17 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 17 etc etc The first time the number 17 appears in column B is on 24-04-07 so I want that date as a return, the first time the number 17 appears in column C is on 28-04-07 so I want that date as a return. Can you please help me ?? -- ** Fool on the hill ** |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
Unfortunately I am working with a dutch version, so I need to translate some
key elements in the formula. What is ? INDEX ISNA MATCH -- ** Fool on the hill ** "Toppers" wrote: If D2=17 then: =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0))) =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0))) HTH "Jaydubs" wrote: Dear Excel(lent) users, I have a sheet in which I have the following columns: A : Date B: Number range, cummulatif +3 (positive estimation) C: Number range, cummulatif -2 (negative estimation) Now I want to know the following: I want to know the date in A, which is the first date a certain number is shown in Column B. For instance: A B C --------------------------------------------- 23-04-07 10 8 24-04-07 17 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 17 etc etc The first time the number 17 appears in column B is on 24-04-07 so I want that date as a return, the first time the number 17 appears in column C is on 28-04-07 so I want that date as a return. Can you please help me ?? -- ** Fool on the hill ** |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
Hello thanx for your previous reply, I figured it out, but what happens if
the number does not appear in either Column B or C.....? No I would appreciate a similar formula, which looks for the nearest number compared to the 17 (for instance 18 in the example below). In this example the first time 17 or higher is hit is 25-07-07 in Column B and 28-04-07 in Column C. A B C --------------------------------------------- 23-04-07 10 8 24-04-07 16 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 19 Can you please help again? -- ** Fool on the hill ** "Jaydubs" wrote: Unfortunately I am working with a dutch version, so I need to translate some key elements in the formula. What is ? INDEX ISNA MATCH -- ** Fool on the hill ** "Toppers" wrote: If D2=17 then: =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0))) =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0))) HTH "Jaydubs" wrote: Dear Excel(lent) users, I have a sheet in which I have the following columns: A : Date B: Number range, cummulatif +3 (positive estimation) C: Number range, cummulatif -2 (negative estimation) Now I want to know the following: I want to know the date in A, which is the first date a certain number is shown in Column B. For instance: A B C --------------------------------------------- 23-04-07 10 8 24-04-07 17 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 17 etc etc The first time the number 17 appears in column B is on 24-04-07 so I want that date as a return, the first time the number 17 appears in column C is on 28-04-07 so I want that date as a return. Can you please help me ?? -- ** Fool on the hill ** |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
PROVIDING the data in columns A, B & C are is DESCENDING sequence this will
work =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,-1))),"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,-1))) A value greater than 40 (to use yoour example) will return an error value of blank. HTH "Jaydubs" wrote: Hello thanx for your previous reply, I figured it out, but what happens if the number does not appear in either Column B or C.....? No I would appreciate a similar formula, which looks for the nearest number compared to the 17 (for instance 18 in the example below). In this example the first time 17 or higher is hit is 25-07-07 in Column B and 28-04-07 in Column C. A B C --------------------------------------------- 23-04-07 10 8 24-04-07 16 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 19 Can you please help again? -- ** Fool on the hill ** "Jaydubs" wrote: Unfortunately I am working with a dutch version, so I need to translate some key elements in the formula. What is ? INDEX ISNA MATCH -- ** Fool on the hill ** "Toppers" wrote: If D2=17 then: =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0))) =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0))) HTH "Jaydubs" wrote: Dear Excel(lent) users, I have a sheet in which I have the following columns: A : Date B: Number range, cummulatif +3 (positive estimation) C: Number range, cummulatif -2 (negative estimation) Now I want to know the following: I want to know the date in A, which is the first date a certain number is shown in Column B. For instance: A B C --------------------------------------------- 23-04-07 10 8 24-04-07 17 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 17 etc etc The first time the number 17 appears in column B is on 24-04-07 so I want that date as a return, the first time the number 17 appears in column C is on 28-04-07 so I want that date as a return. Can you please help me ?? -- ** Fool on the hill ** |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
Hello Topper,
Thanks for your solution, unfortunately it gives me a day before the actual day of reaching the number required. In the example below it gave me 24-04-07 instead of the required 25-04-07 (column B). -- ** Fool on the hill ** "Toppers" wrote: PROVIDING the data in columns A, B & C are is DESCENDING sequence this will work =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,-1))),"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,-1))) A value greater than 40 (to use yoour example) will return an error value of blank. HTH "Jaydubs" wrote: Hello thanx for your previous reply, I figured it out, but what happens if the number does not appear in either Column B or C.....? No I would appreciate a similar formula, which looks for the nearest number compared to the 17 (for instance 18 in the example below). In this example the first time 17 or higher is hit is 25-07-07 in Column B and 28-04-07 in Column C. A B C --------------------------------------------- 23-04-07 10 8 24-04-07 16 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 19 Can you please help again? -- ** Fool on the hill ** "Jaydubs" wrote: Unfortunately I am working with a dutch version, so I need to translate some key elements in the formula. What is ? INDEX ISNA MATCH -- ** Fool on the hill ** "Toppers" wrote: If D2=17 then: =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,0))) =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0)))," ",INDEX($A$1:$A$6,MATCH(D2,$C$1:$C$6,0))) HTH "Jaydubs" wrote: Dear Excel(lent) users, I have a sheet in which I have the following columns: A : Date B: Number range, cummulatif +3 (positive estimation) C: Number range, cummulatif -2 (negative estimation) Now I want to know the following: I want to know the date in A, which is the first date a certain number is shown in Column B. For instance: A B C --------------------------------------------- 23-04-07 10 8 24-04-07 17 9 25-04-07 23 11 26-04-07 28 12 27-04-07 35 15 28-04-07 40 17 etc etc The first time the number 17 appears in column B is on 24-04-07 so I want that date as a return, the first time the number 17 appears in column C is on 28-04-07 so I want that date as a return. Can you please help me ?? -- ** Fool on the hill ** |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Entering a number on any sheet one time only | Excel Discussion (Misc queries) | |||
| How Do I Eliminate the Serial Number for time from Date? | Excel Discussion (Misc queries) | |||
| auto insert invoice number that increases by one each time opened | Excel Worksheet Functions | |||
| Time as number | Excel Discussion (Misc queries) | |||
| avoid retype the number more than one time withen acolumn | Excel Discussion (Misc queries) | |||
Hybrid Mode
