finding the right number
 

I have a worksheet with a column of numbers in A, and I need to use a
function in B and find the next highest number to populate in B. The
problem is I cannot sort them manually as this sheet will be changing
dynamically with information on other worksheets. There are also empty
cells in the data. For instance, A1 - A9 would be:

2
19
36

99
52
17

103

So, what I would want B1 - B9 to figure out with functions is:

17
36
52

103
99
19

(SAFE RANGE)

by safe range I mean that because there is no number higher than 103, I
would add 50 so B9 would report 153, for example.

Any help would be appreciated.

Thank you,
Kevin


--
kevindict
------------------------------------------------------------------------
kevindict's Profile: http://www.excelforum.com/member.php...o&userid=15385
View this thread: http://www.excelforum.com/showthread...hreadid=465703

B1 = IF(MAX(A$1:A$9)=A1,"(safe
range)",IF(ISBLANK(A1),"",SMALL(A$1:A$9,RANK(A1,A$ 1:A$9,1)+1))) (copy
down)



kevindict Wrote:
I have a worksheet with a column of numbers in A, and I need to use a
function in B and find the next highest number to populate in B. The
problem is I cannot sort them manually as this sheet will be changing
dynamically with information on other worksheets. There are also empty
cells in the data. For instance, A1 - A9 would be:

2
19
36

99
52
17

103

So, what I would want B1 - B9 to figure out with functions is:

17
36
52

103
99
19

(SAFE RANGE)

by safe range I mean that because there is no number higher than 103, I
would add 50 so B9 would report 153, for example.

Any help would be appreciated.

Thank you,
Kevin


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=465703

THIS WORKED GREAT!

Thank you very much.


--
kevindict
------------------------------------------------------------------------
kevindict's Profile: http://www.excelforum.com/member.php...o&userid=15385
View this thread: http://www.excelforum.com/showthread...hreadid=465703