Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 89
Default Finding next number in a series

I have a series of numbers in one column (sample):

1
2
4
5
7
16


What formula would find the smallest available number? In other words how
would I find "3"?


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Finding next number in a series

Hello,

Array-enter
=INDEX(ROW(INDIRECT(MIN(A1:A6)&":"&MAX(A1:A6))),MA TCH(TRUE,ISNA(MATCH(ROW(INDIRECT(MIN(A1:A6)&":"&MA X(A1:A6))),A1:A6,0)),
0))

Regards,
Bernd
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Finding next number in a series

Assuming the range of numbers is 1 to 16.

Array entered** :

=SMALL(IF(ISNA(MATCH(ROW(INDIRECT("1:16")),A2:A7,0 )),ROW(INDIRECT("1:16"))),1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Iriemon" wrote in message
...
I have a series of numbers in one column (sample):

1
2
4
5
7
16


What formula would find the smallest available number? In other words how
would I find "3"?




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
series objects; finding specific ones in the collection cate Charts and Charting in Excel 2 April 4th 10 03:25 PM
User Selectable Series and Number of Series for Line Chart Dave in NJ Charts and Charting in Excel 2 February 23rd 09 01:18 AM
Finding Minimum Value in series, excluding zero values [email protected] Excel Worksheet Functions 5 January 30th 07 09:21 PM
Finding series index if I know the Series Name Barb Reinhardt Charts and Charting in Excel 2 January 23rd 07 02:01 PM
Help needed in finding specific series via row. hadmybreaktoday Excel Worksheet Functions 5 August 24th 05 06:13 PM


All times are GMT +1. The time now is 12:17 PM.

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

About Us

"It's about Microsoft Excel"