Sort a Range
 

Sorry Everyone, I know this one has been on here a dozen times, but I cant
find any relating posts.

Say I have 2 columns and 4 rows of data such as:

A B

1 2800 $2000

2 2700 $1952

3 2940 $1700

4 2852 $2100


I want the next 3 rows to sort this from smallest to largest according to
value in column A. I have a fixed # of data points (3 in this example) and
would like to use formulas rather than the sort command so that I can have a
x-y scatter plot of the sorted data that updates automatically when ever new
data is entered up in rows 1-3. So the new data should look like this:

A B

5 2700 $1952

6 2800 $2000

7 2852 $2100

8 2940 $1700

I know this can be done with the vlookup for the column b part once I have
ranked column A, but I don't know how to find the 2nd largest (or nth
largest) or 2nd smallesst value. In reality my sheet may contain up to 15
points so I would like to have a clean formula.

Thanks

One way ..

Set up an adjacent "tie-breaker"=20
helper col in say, col C

Put in C1: =3DIF(A1=3D"","",A1+ROW()/10^10)
Copy C1 down to C4=20
(the extent of your data-set in cols A and B)

Now, put in A5:

=3DINDEX(A$1:A$4,MATCH(SMALL($C:$C,ROW(A1)),$C:$C, 0))

Copy A5 across to B5,=20
fill down to B8
(i.e. by the number of rows in your data-set)

Format B5:B8 in currency

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----
"Brandt" wrote:
Sorry Everyone, I know this one has been on here a dozen=20
times, but I cant=20
find any relating posts.

Say I have 2 columns and 4 rows of data such as:

A B =20

1 2800 $2000

2 2700 $1952

3 2940 $1700

4 2852 $2100


I want the next 3 rows to sort this from smallest to=20
largest according to=20
value in column A. I have a fixed # of data points (3 in=20
this example) and=20
would like to use formulas rather than the sort command=20
so that I can have a=20
x-y scatter plot of the sorted data that updates=20
automatically when ever new=20
data is entered up in rows 1-3. So the new data should=20
look like this:

A B =20

5 2700 $1952

6 2800 $2000

7 2852 $2100

8 2940 $1700

I know this can be done with the vlookup for the column b=20
part once I have=20
ranked column A, but I don't know how to find the 2nd=20
largest (or nth=20
largest) or 2nd smallesst value. In reality my sheet may=20
contain up to 15=20
points so I would like to have a clean formula.

Thanks
.

Why do you keep mentioning *3* data points and rows, when both your examples
show *4*?
Am I missing something?

Anyway, since you stated that your sheet may contain 15 points,
let's assume the maximum.
With data in A1:A15, enter this formula in A16:

=SMALL($A$1:$A$15,ROW(A1))

And drag down to copy.

This will give you a #NUM! error where the formula doesn't find data in A1
:A15.
I don't know if you're plotting the Column A numbers or the Column B
dollars, but that error might interfere with your graph.

If it does, you can try this :

=IF(ISERROR(SMALL($A$1:$A$15,ROW(A1))),#N/A,SMALL($A$1:$A$15,ROW(A1)))

Where I believe the #N/A error is very friendly with plotting graphs.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Brandt" wrote in message
...
Sorry Everyone, I know this one has been on here a dozen times, but I cant
find any relating posts.

Say I have 2 columns and 4 rows of data such as:

A B

1 2800 $2000

2 2700 $1952

3 2940 $1700

4 2852 $2100


I want the next 3 rows to sort this from smallest to largest according to
value in column A. I have a fixed # of data points (3 in this example) and
would like to use formulas rather than the sort command so that I can have a
x-y scatter plot of the sorted data that updates automatically when ever new
data is entered up in rows 1-3. So the new data should look like this:

A B

5 2700 $1952

6 2800 $2000

7 2852 $2100

8 2940 $1700

I know this can be done with the vlookup for the column b part once I have
ranked column A, but I don't know how to find the 2nd largest (or nth
largest) or 2nd smallesst value. In reality my sheet may contain up to 15
points so I would like to have a clean formula.

Thanks

Brandt,

A
5 =LARGE($A$1:$A$4,4)
6 =LARGE($A$1:$A$4,3)
7 =LARGE($A$1:$A$4,2)
8 =LARGE($A$1:$A$4,1)

B
5 VLOOKUP(A5,$A$1:$B$4,2,FALSE)
6 VLOOKUP(A6,$A$1:$B$4,2,FALSE)
7 VLOOKUP(A7,$A$1:$B$4,2,FALSE)
8 VLOOKUP(A8,$A$1:$B$4,2,FALSE)

For a lot of formulas you could use in A5: =LARGE($A$1:$A$4,C5). Copy down
to A8. Then put 4, 3, 2, etc., starting in C5 and down (put 1 in C8, then
drag the Fill Handle up holding Ctrl). You can hide that column.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Brandt" wrote in message
...
Sorry Everyone, I know this one has been on here a dozen times, but I cant
find any relating posts.

Say I have 2 columns and 4 rows of data such as:

A B

1 2800 $2000

2 2700 $1952

3 2940 $1700

4 2852 $2100


I want the next 3 rows to sort this from smallest to largest according to
value in column A. I have a fixed # of data points (3 in this example)
and
would like to use formulas rather than the sort command so that I can have
a
x-y scatter plot of the sorted data that updates automatically when ever
new
data is entered up in rows 1-3. So the new data should look like this:

A B

5 2700 $1952

6 2800 $2000

7 2852 $2100

8 2940 $1700

I know this can be done with the vlookup for the column b part once I have
ranked column A, but I don't know how to find the 2nd largest (or nth
largest) or 2nd smallesst value. In reality my sheet may contain up to 15
points so I would like to have a clean formula.

Thanks

Sorry,

I wrote the question with 3 data points then realized that a simple answer
would be to find the min then the max and then the other one. So I added a
4th point, but missed some updates. Anyway, thanks for the help!!!


"RagDyer" wrote:

Why do you keep mentioning *3* data points and rows, when both your examples
show *4*?
Am I missing something?

Anyway, since you stated that your sheet may contain 15 points,
let's assume the maximum.
With data in A1:A15, enter this formula in A16:

=SMALL($A$1:$A$15,ROW(A1))

And drag down to copy.

This will give you a #NUM! error where the formula doesn't find data in A1
:A15.
I don't know if you're plotting the Column A numbers or the Column B
dollars, but that error might interfere with your graph.

If it does, you can try this :

=IF(ISERROR(SMALL($A$1:$A$15,ROW(A1))),#N/A,SMALL($A$1:$A$15,ROW(A1)))

Where I believe the #N/A error is very friendly with plotting graphs.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Brandt" wrote in message
...
Sorry Everyone, I know this one has been on here a dozen times, but I cant
find any relating posts.

Say I have 2 columns and 4 rows of data such as:

A B

1 2800 $2000

2 2700 $1952

3 2940 $1700

4 2852 $2100


I want the next 3 rows to sort this from smallest to largest according to
value in column A. I have a fixed # of data points (3 in this example) and
would like to use formulas rather than the sort command so that I can have a
x-y scatter plot of the sorted data that updates automatically when ever new
data is entered up in rows 1-3. So the new data should look like this:

A B

5 2700 $1952

6 2800 $2000

7 2852 $2100

8 2940 $1700

I know this can be done with the vlookup for the column b part once I have
ranked column A, but I don't know how to find the 2nd largest (or nth
largest) or 2nd smallesst value. In reality my sheet may contain up to 15
points so I would like to have a clean formula.

Thanks

Thanks for the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Brandt" wrote in message
...
Sorry,

I wrote the question with 3 data points then realized that a simple answer
would be to find the min then the max and then the other one. So I added a
4th point, but missed some updates. Anyway, thanks for the help!!!


"RagDyer" wrote:

Why do you keep mentioning *3* data points and rows, when both your
examples
show *4*?
Am I missing something?

Anyway, since you stated that your sheet may contain 15 points,
let's assume the maximum.
With data in A1:A15, enter this formula in A16:

=SMALL($A$1:$A$15,ROW(A1))

And drag down to copy.

This will give you a #NUM! error where the formula doesn't find data in A1
:A15.
I don't know if you're plotting the Column A numbers or the Column B
dollars, but that error might interfere with your graph.

If it does, you can try this :

=IF(ISERROR(SMALL($A$1:$A$15,ROW(A1))),#N/A,SMALL($A$1:$A$15,ROW(A1)))

Where I believe the #N/A error is very friendly with plotting graphs.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Brandt" wrote in message
...
Sorry Everyone, I know this one has been on here a dozen times, but I cant
find any relating posts.

Say I have 2 columns and 4 rows of data such as:

A B

1 2800 $2000

2 2700 $1952

3 2940 $1700

4 2852 $2100


I want the next 3 rows to sort this from smallest to largest according to
value in column A. I have a fixed # of data points (3 in this example)
and
would like to use formulas rather than the sort command so that I can have
a
x-y scatter plot of the sorted data that updates automatically when ever
new
data is entered up in rows 1-3. So the new data should look like this:

A B

5 2700 $1952

6 2800 $2000

7 2852 $2100

8 2940 $1700

I know this can be done with the vlookup for the column b part once I have
ranked column A, but I don't know how to find the 2nd largest (or nth
largest) or 2nd smallesst value. In reality my sheet may contain up to 15
points so I would like to have a clean formula.

Thanks