Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 | |
|
|
![]() |
||||
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 |