Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
I am collecting a series of data labelled numerically from 1-1000. I need to
determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
I'm sure there is a more ellegant way to go about this, but I think this will
work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Thanks that is what I was looking for.
Not sure how that works, but it works, thanks! "Elkar" wrote: I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
=IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"")
Since we don't really care about the rank that RANK( ) returns, I am very curious if MATCH( ) can be used for this purpose as well. If yes, is there a difference between the two in terms of speed if the file is huge? It looks like both RANK and MATCH (FALSE) don't require the data to be sorted and take care of duplicate numbers. Can someone shed some light on this please? I am trying to learn here. Epinn "bman342" wrote in message ... I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Here's another way that returns the missing values in a contiguous block.
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Biff,
Thanks for the formula. I like the contiguous block. I like RANK too. I played with the MATCH formula and I took out the INDEX function. It seemed to give identical results. Do you see any problem not having INDEX? Is INDEX a case of good programming technique (i.e. index/match usually go together) that Bob taught me? I am trying to save some time by not doing INDEX, seeing that this is an array formula. ;) I am curious about what you think. Appreciate your advice. Epinn "Biff" wrote in message ... Here's another way that returns the missing values in a contiguous block. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Forgot to paste the array formula that I experimented with. A smaller range for testing ......
=SMALL(IF(ISNA(MATCH(ROW($1:$20),A$1:A$10,0)),ROW( $1:$20)),ROW(A1)) Please understand that my only purpose is to learn and sometimes I have a unique way of seeing things. Epinn "Epinn" wrote in message ... Biff, Thanks for the formula. I like the contiguous block. I like RANK too. I played with the MATCH formula and I took out the INDEX function. It seemed to give identical results. Do you see any problem not having INDEX? Is INDEX a case of good programming technique (i.e. index/match usually go together) that Bob taught me? I am trying to save some time by not doing INDEX, seeing that this is an array formula. ;) I am curious about what you think. Appreciate your advice. Epinn "Biff" wrote in message ... Here's another way that returns the missing values in a contiguous block. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Is INDEX a case of good programming technique (i.e. index/match usually go
together) Hmmm..... It may be just a force of habit (in my case). Removing Index does seem to work (though not exhaustively tested). Removing Index didn't improve performance, though. Actually, I used to use this: =INDEX(ROW($1:$1000),SMALL(IF(COUNTIF(A$1:A$1000,R OW(A$1:A$1000))=0,ROW($1:$1000)),ROW(A1))) But replacing Countif with Match improves performance significantly on large sequences. Biff "Epinn" wrote in message ... Biff, Thanks for the formula. I like the contiguous block. I like RANK too. I played with the MATCH formula and I took out the INDEX function. It seemed to give identical results. Do you see any problem not having INDEX? Is INDEX a case of good programming technique (i.e. index/match usually go together) that Bob taught me? I am trying to save some time by not doing INDEX, seeing that this is an array formula. ;) I am curious about what you think. Appreciate your advice. Epinn "Biff" wrote in message ... Here's another way that returns the missing values in a contiguous block. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Thanks Biff for sharing.
But replacing Countif with Match improves performance significantly on large sequences. This is why I want to learn from you. Your practical experience is precious. I prefer MATCH to COUNTIF as I don't want to split up the couple (INDEX/MATCH). ;) In case you are interested, when I first suggested using MATCH, I was thinking of just using MATCH (similar to RANK) without using CSE or SMALL. But contiguous block is good unless someone is really concerned about speed. Do you want to know what inspired me to drop INDEX? I used "evaluate formula" and I saw it not doing much. I do believe that dropping it won't speed up ...... Bob, I forgot to start a new thread. You know what, if only a couple of posts I may just squeeze in because sometimes I think efficiency is important too. Bman342, thank you for the question and please feel free to come back with any follow-up questions. I am sure the experts won't mind me saying this as they are quite friendly on top of being knowledgeable. Epinn "Biff" wrote in message ... Is INDEX a case of good programming technique (i.e. index/match usually go together) Hmmm..... It may be just a force of habit (in my case). Removing Index does seem to work (though not exhaustively tested). Removing Index didn't improve performance, though. Actually, I used to use this: =INDEX(ROW($1:$1000),SMALL(IF(COUNTIF(A$1:A$1000,R OW(A$1:A$1000))=0,ROW($1:$1000)),ROW(A1))) But replacing Countif with Match improves performance significantly on large sequences. Biff "Epinn" wrote in message ... Biff, Thanks for the formula. I like the contiguous block. I like RANK too. I played with the MATCH formula and I took out the INDEX function. It seemed to give identical results. Do you see any problem not having INDEX? Is INDEX a case of good programming technique (i.e. index/match usually go together) that Bob taught me? I am trying to save some time by not doing INDEX, seeing that this is an array formula. ;) I am curious about what you think. Appreciate your advice. Epinn "Biff" wrote in message ... Here's another way that returns the missing values in a contiguous block. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Even better, thanks!
"Biff" wrote: Here's another way that returns the missing values in a contiguous block. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
Thanks, Epinn, your solution worked as well. Now I need to spend some time
understanding HOW these work (after nn nested functions my brain begins to cramp). "Epinn" wrote: Thanks Biff for sharing. But replacing Countif with Match improves performance significantly on large sequences. This is why I want to learn from you. Your practical experience is precious. I prefer MATCH to COUNTIF as I don't want to split up the couple (INDEX/MATCH). ;) In case you are interested, when I first suggested using MATCH, I was thinking of just using MATCH (similar to RANK) without using CSE or SMALL. But contiguous block is good unless someone is really concerned about speed. Do you want to know what inspired me to drop INDEX? I used "evaluate formula" and I saw it not doing much. I do believe that dropping it won't speed up ...... Bob, I forgot to start a new thread. You know what, if only a couple of posts I may just squeeze in because sometimes I think efficiency is important too. Bman342, thank you for the question and please feel free to come back with any follow-up questions. I am sure the experts won't mind me saying this as they are quite friendly on top of being knowledgeable. Epinn "Biff" wrote in message ... Is INDEX a case of good programming technique (i.e. index/match usually go together) Hmmm..... It may be just a force of habit (in my case). Removing Index does seem to work (though not exhaustively tested). Removing Index didn't improve performance, though. Actually, I used to use this: =INDEX(ROW($1:$1000),SMALL(IF(COUNTIF(A$1:A$1000,R OW(A$1:A$1000))=0,ROW($1:$1000)),ROW(A1))) But replacing Countif with Match improves performance significantly on large sequences. Biff "Epinn" wrote in message ... Biff, Thanks for the formula. I like the contiguous block. I like RANK too. I played with the MATCH formula and I took out the INDEX function. It seemed to give identical results. Do you see any problem not having INDEX? Is INDEX a case of good programming technique (i.e. index/match usually go together) that Bob taught me? I am trying to save some time by not doing INDEX, seeing that this is an array formula. ;) I am curious about what you think. Appreciate your advice. Epinn "Biff" wrote in message ... Here's another way that returns the missing values in a contiguous block. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for missing data in a series?
You're welcome. Thanks for the feedback!
Biff "bman342" wrote in message ... Even better, thanks! "Biff" wrote: Here's another way that returns the missing values in a contiguous block. Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(ROW($1:$1000),SMALL(IF(ISNA(MATCH(ROW($1:$1 000),A$1:A$750,0)),ROW($1:$1000)),ROW(A1))) Copy down umtil you get #NUM! errors meaning all the missing values have been returned. This method works but can be slow on large data sets. Also note that due to the use of the ROW function the largest number in the sequence can be 65,536 (in current versions of Excel). Biff "Elkar" wrote in message ... I'm sure there is a more ellegant way to go about this, but I think this will work for you: Let's say your data is in A1:A750 In a seperate column, enter the formula: =IF(ISNA(RANK(ROW(),$A$1:$A$1000)),ROW(),"") Copy down through row 1000 The displayed results should be your missing data. HTH, Elkar "bman342" wrote: I am collecting a series of data labelled numerically from 1-1000. I need to determine which in the series are missing. ie I know I have 750 data elements out of 1000, so I know there are 250 missing. But I need to detmine which specific data elements are missing. Is there a function, or routine to automate this? Thx! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can i use the IF function in a chart to choose its data series? | Charts and Charting in Excel | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
How many data series can i format | Charts and Charting in Excel | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |