Does someone have a method for identifying a set of numbers by their position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie

=MATCH(10,A1:A20,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Natalie" wrote in message
...
Does someone have a method for identifying a set of numbers by their
position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie

I won't know that the number is necessarily 10. Just that it is not equal to
zero.


--
Natalie


"Bob Phillips" wrote:

=MATCH(10,A1:A20,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Natalie" wrote in message
...
Does someone have a method for identifying a set of numbers by their
position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie

But you said 10 would return 4, 5 would return 7. You could not get 7 for 5
unless you knew there was a 5 there.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Natalie" wrote in message
...
I won't know that the number is necessarily 10. Just that it is not equal
to
zero.


--
Natalie


"Bob Phillips" wrote:

=MATCH(10,A1:A20,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Natalie" wrote in message
...
Does someone have a method for identifying a set of numbers by their
position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by
their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie

Try

=IF(LARGE(A$1:A$9,ROW())0,MATCH(LARGE(A$1:A$9,ROW ()),A$1:A$9,0),"")

and formula-copy down to row 9

It will present the position required, but in descending sort order.

Hope this helps


Natalie Wrote:
Does someone have a method for identifying a set of numbers by their
position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by
their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=480372

"Natalie" wrote
I won't know that the number is necessarily 10.
Just that it is not equal to zero.

Just another guess

Assuming your list is in A1 down

Put in B1: =IF(A1<0,ROW(A1),"")
Copy B1 down until the last row of data in col A

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thanks Bryan, So near yet so far.
unfortunately its a bad example because whilst your formula works
brilliantly for this example (i.e. the numbers are in descending order) if I
swap the numbers 5 and 2 around in the list, 2 is still 9 using your formula
instead of its new position 7, and 5 is still 7 instead of its new position 9.
Any ideas? This ones really stumped me.
--
Natalie


"Bryan Hessey" wrote:


Try

=IF(LARGE(A$1:A$9,ROW())0,MATCH(LARGE(A$1:A$9,ROW ()),A$1:A$9,0),"")

and formula-copy down to row 9

It will present the position required, but in descending sort order.

Hope this helps


Natalie Wrote:
Does someone have a method for identifying a set of numbers by their
position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by
their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=480372

Natalie,

Isn't that because it is referring to the rank, not the number. So when 2 is
in position 9, the third largest is 9, when it is in position 7, the 2nd
largest is in in 9?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Natalie" wrote in message
...
Thanks Bryan, So near yet so far.
unfortunately its a bad example because whilst your formula works
brilliantly for this example (i.e. the numbers are in descending order) if
I
swap the numbers 5 and 2 around in the list, 2 is still 9 using your
formula
instead of its new position 7, and 5 is still 7 instead of its new
position 9.
Any ideas? This ones really stumped me.
--
Natalie


"Bryan Hessey" wrote:


Try

=IF(LARGE(A$1:A$9,ROW())0,MATCH(LARGE(A$1:A$9,ROW ()),A$1:A$9,0),"")

and formula-copy down to row 9

It will present the position required, but in descending sort order.

Hope this helps


Natalie Wrote:
Does someone have a method for identifying a set of numbers by their
position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by
their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=480372

Natalie,

If you want to know the sequence in which they occur, then after the
formula in column B

=IF(LARGE(A$1:A$9,ROW())0,MATCH(LARGE(A$1:A$9,ROW ()),A$1:A$9,0),"")

use

=SMALL(B$1:B$9,ROW())
or
=IF(ISERROR(SMALL(B$1:B$9,ROW())),"",SMALL(B$1:B$9 ,ROW()))

and then use the offset on that column

=IF(C1<"",OFFSET(A$1,C1-1,0),"")

Also, a

=COUNTIF(B1:B9,"0")

will tell you how many numbers there are.



Natalie Wrote:
Thanks Bryan, So near yet so far.
unfortunately its a bad example because whilst your formula works
brilliantly for this example (i.e. the numbers are in descending order)
if I
swap the numbers 5 and 2 around in the list, 2 is still 9 using your
formula
instead of its new position 7, and 5 is still 7 instead of its new
position 9.
Any ideas? This ones really stumped me.
--
Natalie


"Bryan Hessey" wrote:


Try

=IF(LARGE(A$1:A$9,ROW())0,MATCH(LARGE(A$1:A$9,ROW ()),A$1:A$9,0),"")

and formula-copy down to row 9

It will present the position required, but in descending sort order.

Hope this helps


Natalie Wrote:
Does someone have a method for identifying a set of numbers by
their
position
within in a list.
For example
0,0,0,10,0,0,5,0,2
If I want to identify all the items that are not equal to zero, by
their
position
10 would return 4, 5 would return 7, and 2 would return 9
I need to identify every occurance.
--
Natalie


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=480372




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=480372