Help for formula.

[nikos]

Hi to everyone.
I have some data in A1:A100 (numbers, text and empty cells).
I would like in B1:B100 cells to result all the positive numbers of A1:A100
column, in ascending order (1,5,12,37,......,55 etc.)
Any idea?
Thank you.

 

[Bernie Deitrick]

Nikos,

In B1, enter this

=IF(ISNUMBER(A1),
IF(A1>0,RANK(A1,$A$1:$A$100,1)+COUNTIF($A$1:A1,A1)-1-COUNTIF($A$1:$A$100,"<0"),""),"")

In C1, enter this
=IF(ISERROR(INDEX(A:A,MATCH(ROW(A1),B:B,FALSE))),"",INDEX(A:A,MATCH(ROW(A1),B:B,FALSE)))

and copy B1:C1 down to B2:C100

HTH,
Bernie
MS Excel MVP

 

[nikos]

Hi Bernie

I read your response and have taken some ideas from it. I think I made a
formula that gives the solution that I want in only one column.
The central idea is to use the Large() function across A column. The area is
A1:A100 and the k factor consisted of 2 parts.
The stable part, which is: Countif($A$1:$A$100; “>0†)
The changeable part, which is decreasing in every cell down by the amount
(Row(Ai)-Row($A$1)+1).
I tried and I think it works. The only problem is that if I have dates
values in A column (for example 04/09/09), they also included in B column
(because are numbers) and I looking for a way to reject these values. I want
only positive (clear) numbers.

Thanks.

 

[Bernie Deitrick]

nikos,

Dates are numbers, just formatted to appear as dates, and there isn't a
reliable way to determine if an entry is a date with just
worksheetfunctions. If you have an upper limit on the numbers you are
interested in, you could offset your LARGE index by the COUNTIF of numbers
greater than, say, 10,000. (Today, 4/9/2009, is 39,912)

HTH,
Bernie
MS Excel MVP

 

[nikos]

I ll try it. Thanks.

nikos,

Dates are numbers, just formatted to appear as dates, and there isn't a
reliable way to determine if an entry is a date with just
worksheetfunctions. If you have an upper limit on the numbers you are
interested in, you could offset your LARGE index by the COUNTIF of numbers
greater than, say, 10,000. (Today, 4/9/2009, is 39,912)

HTH,
Bernie
MS Excel MVP