Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can i use the IF function in a chart to choose its data series? Edd UK Charts and Charting in Excel 2 April 2nd 23 07:57 PM
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
How many data series can i format Derek Charts and Charting in Excel 4 June 8th 06 05:29 PM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 01:56 PM
Extending a Chart Data Series from an Array - Can it be done? Jon Peltier Charts and Charting in Excel 4 November 30th 04 03:30 AM


All times are GMT +1. The time now is 05:02 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"