Questions about functions
 

I have a column of numbers, and what I want to do is return the 7 highest of
the numbers. Then, I want to be able to return the row number of each one
them. The only problem I have is, if two or more of the high numbers are the
same, it will keep returning the same the row number, instead of the next and
so on. And I CANT use the sort functions because I dont wanna mess up my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant find it.

Questions about functions
 

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7 highest
of
the numbers. Then, I want to be able to return the row number of each one
them. The only problem I have is, if two or more of the high numbers are
the
same, it will keep returning the same the row number, instead of the next
and
so on. And I CANT use the sort functions because I dont wanna mess up my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant find it.

Questions about functions
 

T. Walko,

Thanks for the help. I got one more question? Can you by chance translate
into Quatro Pro form? I cant find a quattro pro help site anywhere!

Please let me know,
thanks,
Cory

"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7 highest
of
the numbers. Then, I want to be able to return the row number of each one
them. The only problem I have is, if two or more of the high numbers are
the
same, it will keep returning the same the row number, instead of the next
and
so on. And I CANT use the sort functions because I dont wanna mess up my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant find it.

Questions about functions
 

Sorry, haven't used QP since the mid 90's!

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
T. Walko,

Thanks for the help. I got one more question? Can you by chance translate
into Quatro Pro form? I cant find a quattro pro help site anywhere!

Please let me know,
thanks,
Cory

"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7
highest
of
the numbers. Then, I want to be able to return the row number of each
one
them. The only problem I have is, if two or more of the high numbers
are
the
same, it will keep returning the same the row number, instead of the
next
and
so on. And I CANT use the sort functions because I dont wanna mess up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant find
it.

Questions about functions
 

T. Valko,

I tried the example you mention in Excel, and it didnt work. For the D1-D7
cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned
"#NUM!".

Any ideas?

Thanks,
Cory

"T. Valko" wrote:

Sorry, haven't used QP since the mid 90's!

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
T. Walko,

Thanks for the help. I got one more question? Can you by chance translate
into Quatro Pro form? I cant find a quattro pro help site anywhere!

Please let me know,
thanks,
Cory

"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7
highest
of
the numbers. Then, I want to be able to return the row number of each
one
them. The only problem I have is, if two or more of the high numbers
are
the
same, it will keep returning the same the row number, instead of the
next
and
so on. And I CANT use the sort functions because I dont wanna mess up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant find
it.

Questions about functions
 

Here's a small sample file that demonstrates this:

delete.xls 14kb

http://cjoint.com/?iFhvKXIgVr

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
T. Valko,

I tried the example you mention in Excel, and it didnt work. For the D1-D7
cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned
"#NUM!".

Any ideas?

Thanks,
Cory

"T. Valko" wrote:

Sorry, haven't used QP since the mid 90's!

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
T. Walko,

Thanks for the help. I got one more question? Can you by chance
translate
into Quatro Pro form? I cant find a quattro pro help site anywhere!

Please let me know,
thanks,
Cory

"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7
highest
of
the numbers. Then, I want to be able to return the row number of
each
one
them. The only problem I have is, if two or more of the high numbers
are
the
same, it will keep returning the same the row number, instead of the
next
and
so on. And I CANT use the sort functions because I dont wanna mess
up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant
find
it.

Questions about functions
 

Did you try typing in the formulae? If so, you probably made a typing
error. Just copy the formulae from Biff's message & paste into the formula
bar for the relevant cells. And read his instructions carefully.
--
David Biddulph

"Cory from Eugene" wrote in
message ...
T. Valko,

I tried the example you mention in Excel, and it didnt work. For the D1-D7
cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned
"#NUM!".

Any ideas?

Thanks,
Cory

....
"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7
highest
of
the numbers. Then, I want to be able to return the row number of
each
one
them. The only problem I have is, if two or more of the high numbers
are
the
same, it will keep returning the same the row number, instead of the
next
and
so on. And I CANT use the sort functions because I dont wanna mess
up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant
find

Questions about functions
 

T. Valko,

Hey, i just wanted to say that your first solution was the right one and it
worked!! Thanks so much. I just have one more question. Do you know how to do
the same thing for searching columns? I tried to do it using the same ideas
as the row functions, but it didnt work.

Thanks so much!!
Cory

"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7 highest
of
the numbers. Then, I want to be able to return the row number of each one
them. The only problem I have is, if two or more of the high numbers are
the
same, it will keep returning the same the row number, instead of the next
and
so on. And I CANT use the sort functions because I dont wanna mess up my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant find it.

Questions about functions
 

David,

yeah, his help did the trick! Now i just need to do the same for searching
columns, but cant figure it out. Any ideas?

Thanks,
Cory

"David Biddulph" wrote:

Did you try typing in the formulae? If so, you probably made a typing
error. Just copy the formulae from Biff's message & paste into the formula
bar for the relevant cells. And read his instructions carefully.
--
David Biddulph

"Cory from Eugene" wrote in
message ...
T. Valko,

I tried the example you mention in Excel, and it didnt work. For the D1-D7
cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned
"#NUM!".

Any ideas?

Thanks,
Cory

....
"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
I have a column of numbers, and what I want to do is return the 7
highest
of
the numbers. Then, I want to be able to return the row number of
each
one
them. The only problem I have is, if two or more of the high numbers
are
the
same, it will keep returning the same the row number, instead of the
next
and
so on. And I CANT use the sort functions because I dont wanna mess
up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant
find

Questions about functions
 

The formulas I posted (and the sample file) are based on the data being in
columns. Like this:

10
22
51
39

If you want to do the same thing for data running *across* a row, like this:

10...22...51...39

Then the formulas would be something like this:

Data in the range A1:Z1

Entered in A3:

=LARGE($A1:$Z1,COLUMNS($A:A))

Array formula** entered in A4:

=SMALL(IF($A1:$Z1=A3,COLUMN($A1:$Z1)),COUNTIF($A3: A3,A3))

Select both A3 and A4 and copy across as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
David,

yeah, his help did the trick! Now i just need to do the same for searching
columns, but cant figure it out. Any ideas?

Thanks,
Cory

"David Biddulph" wrote:

Did you try typing in the formulae? If so, you probably made a typing
error. Just copy the formulae from Biff's message & paste into the
formula
bar for the relevant cells. And read his instructions carefully.
--
David Biddulph

"Cory from Eugene" wrote in
message ...
T. Valko,

I tried the example you mention in Excel, and it didnt work. For the
D1-D7
cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned
"#NUM!".

Any ideas?

Thanks,
Cory

....
"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote
in
message ...
I have a column of numbers, and what I want to do is return the 7
highest
of
the numbers. Then, I want to be able to return the row number of
each
one
them. The only problem I have is, if two or more of the high
numbers
are
the
same, it will keep returning the same the row number, instead of
the
next
and
so on. And I CANT use the sort functions because I dont wanna
mess
up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant
find

Questions about functions
 

Biff, you're the man. thanks for the help

Cory

"T. Valko" wrote:

The formulas I posted (and the sample file) are based on the data being in
columns. Like this:

10
22
51
39

If you want to do the same thing for data running *across* a row, like this:

10...22...51...39

Then the formulas would be something like this:

Data in the range A1:Z1

Entered in A3:

=LARGE($A1:$Z1,COLUMNS($A:A))

Array formula** entered in A4:

=SMALL(IF($A1:$Z1=A3,COLUMN($A1:$Z1)),COUNTIF($A3: A3,A3))

Select both A3 and A4 and copy across as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
David,

yeah, his help did the trick! Now i just need to do the same for searching
columns, but cant figure it out. Any ideas?

Thanks,
Cory

"David Biddulph" wrote:

Did you try typing in the formulae? If so, you probably made a typing
error. Just copy the formulae from Biff's message & paste into the
formula
bar for the relevant cells. And read his instructions carefully.
--
David Biddulph

"Cory from Eugene" wrote in
message ...
T. Valko,

I tried the example you mention in Excel, and it didnt work. For the
D1-D7
cells, all it returned was "TRUE". Then, in the E1-E7 cells it returned
"#NUM!".

Any ideas?

Thanks,
Cory

....
"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote
in
message ...
I have a column of numbers, and what I want to do is return the 7
highest
of
the numbers. Then, I want to be able to return the row number of
each
one
them. The only problem I have is, if two or more of the high
numbers
are
the
same, it will keep returning the same the row number, instead of
the
next
and
so on. And I CANT use the sort functions because I dont wanna
mess
up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just cant
find

Questions about functions
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
Biff, you're the man. thanks for the help

Cory

"T. Valko" wrote:

The formulas I posted (and the sample file) are based on the data being
in
columns. Like this:

10
22
51
39

If you want to do the same thing for data running *across* a row, like
this:

10...22...51...39

Then the formulas would be something like this:

Data in the range A1:Z1

Entered in A3:

=LARGE($A1:$Z1,COLUMNS($A:A))

Array formula** entered in A4:

=SMALL(IF($A1:$Z1=A3,COLUMN($A1:$Z1)),COUNTIF($A3: A3,A3))

Select both A3 and A4 and copy across as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Cory from Eugene" wrote in
message ...
David,

yeah, his help did the trick! Now i just need to do the same for
searching
columns, but cant figure it out. Any ideas?

Thanks,
Cory

"David Biddulph" wrote:

Did you try typing in the formulae? If so, you probably made a typing
error. Just copy the formulae from Biff's message & paste into the
formula
bar for the relevant cells. And read his instructions carefully.
--
David Biddulph

"Cory from Eugene" wrote in
message ...
T. Valko,

I tried the example you mention in Excel, and it didnt work. For the
D1-D7
cells, all it returned was "TRUE". Then, in the E1-E7 cells it
returned
"#NUM!".

Any ideas?

Thanks,
Cory

....
"T. Valko" wrote:

Try this:

Numbers in the range A1:A20

Enter this formula in D1:

=LARGE(A$1:A$20,ROWS($1:1))

Enter this array formula** in E1:

=SMALL(IF(A$1:A$20=D1,ROW(A$1:A$20)),COUNTIF(D$1:D 1,D1))

Select both D1 and E1 and copy down a total of 7 rows or as
needed.

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Cory from Eugene"
wrote
in
message
...
I have a column of numbers, and what I want to do is return the
7
highest
of
the numbers. Then, I want to be able to return the row number
of
each
one
them. The only problem I have is, if two or more of the high
numbers
are
the
same, it will keep returning the same the row number, instead
of
the
next
and
so on. And I CANT use the sort functions because I dont wanna
mess
up
my
values

Any suggestions?

Please let me know. lol It's probably an easy answer, I just
cant
find