| 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 ** |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
17 gave me 25/04/07 .. data must be DESCENDING ORDER.
39 gave 28/04/07 28/04/2007 40 19 27/04/2007 35 15 26/04/2007 28 12 25/04/2007 23 11 24/04/2007 16 9 23/04/2007 10 8 "Jaydubs" wrote: 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 ** |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
Dear Toppers,
And in Ascending order? Thanks ! -- ** Fool on the hill ** "Toppers" wrote: 17 gave me 25/04/07 .. data must be DESCENDING ORDER. 39 gave 28/04/07 28/04/2007 40 19 27/04/2007 35 15 26/04/2007 28 12 25/04/2007 23 11 24/04/2007 16 9 23/04/2007 10 8 "Jaydubs" wrote: 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 ** |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
This is formula for ascending order but this give 24/04/07 for 17.
=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))) This will give correct result BUT not if value is = to last in table i.e 40 or mo =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) ,"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) No more ideas! HTH "Jaydubs" wrote: Dear Toppers, And in Ascending order? Thanks ! -- ** Fool on the hill ** "Toppers" wrote: 17 gave me 25/04/07 .. data must be DESCENDING ORDER. 39 gave 28/04/07 28/04/2007 40 19 27/04/2007 35 15 26/04/2007 28 12 25/04/2007 23 11 24/04/2007 16 9 23/04/2007 10 8 "Jaydubs" wrote: 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 ** |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
Yes, I figured that out as well, any solutions to get the proper date?
-- ** Fool on the hill ** "Toppers" wrote: This is formula for ascending order but this give 24/04/07 for 17. =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))) This will give correct result BUT not if value is = to last in table i.e 40 or mo =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) ,"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) No more ideas! HTH "Jaydubs" wrote: Dear Toppers, And in Ascending order? Thanks ! -- ** Fool on the hill ** "Toppers" wrote: 17 gave me 25/04/07 .. data must be DESCENDING ORDER. 39 gave 28/04/07 28/04/2007 40 19 27/04/2007 35 15 26/04/2007 28 12 25/04/2007 23 11 24/04/2007 16 9 23/04/2007 10 8 "Jaydubs" wrote: 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 ** |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
Solutions offered get the correct date BUT fail when values are outside
values in table. What do you want returned if we have a value of 45 or 7. "Jaydubs" wrote: Yes, I figured that out as well, any solutions to get the proper date? -- ** Fool on the hill ** "Toppers" wrote: This is formula for ascending order but this give 24/04/07 for 17. =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))) This will give correct result BUT not if value is = to last in table i.e 40 or mo =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) ,"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) No more ideas! HTH "Jaydubs" wrote: Dear Toppers, And in Ascending order? Thanks ! -- ** Fool on the hill ** "Toppers" wrote: 17 gave me 25/04/07 .. data must be DESCENDING ORDER. 39 gave 28/04/07 28/04/2007 40 19 27/04/2007 35 15 26/04/2007 28 12 25/04/2007 23 11 24/04/2007 16 9 23/04/2007 10 8 "Jaydubs" wrote: 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 ** |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Selecting the first time a number appears
Hello Toppers,
A good night's rest has changed my point of view. I now saw your second formula and used it. It worked. I am sorry to bug you so much, you have been an excellent help !! Thanks mate ! -- ** Fool on the hill ** "Toppers" wrote: Solutions offered get the correct date BUT fail when values are outside values in table. What do you want returned if we have a value of 45 or 7. "Jaydubs" wrote: Yes, I figured that out as well, any solutions to get the proper date? -- ** Fool on the hill ** "Toppers" wrote: This is formula for ascending order but this give 24/04/07 for 17. =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))) This will give correct result BUT not if value is = to last in table i.e 40 or mo =IF(ISNA(INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) ,"",INDEX($A$1:$A$6,MATCH(D2,$B$1:$B$6,1)+1)) No more ideas! HTH "Jaydubs" wrote: Dear Toppers, And in Ascending order? Thanks ! -- ** Fool on the hill ** "Toppers" wrote: 17 gave me 25/04/07 .. data must be DESCENDING ORDER. 39 gave 28/04/07 28/04/2007 40 19 27/04/2007 35 15 26/04/2007 28 12 25/04/2007 23 11 24/04/2007 16 9 23/04/2007 10 8 "Jaydubs" wrote: 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) | |||
Linear Mode
