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
|