Closest top 2, closest bottom 2

  • Thread starter Thread starter calebfda
  • Start date Start date
C

calebfda

Hi

I am at a lost of where to even begin this....or it is even possible

i have 2 columns of data.
Column A is state
Column B is money owed to that state

So lets say
A1 is florida and B1 is 29,000

I am trying for column C to tell me what values (and if possible the
cooresponding state) are the 2 closest values above it and the 2
closest values below it in B1:B50. It can appear as a string of text
like 27,000 CA 28944 (OR) 31000 (FL) 31200 (GA)

Is there a way to do this??
 
You are busy!

In Column C type the heading Rank this is what we you can use to lookup data.

I created range names for State, $Owed and Rank. Select the Table and choose
Insert>Names>Create and select the Top Row Option, deselect the Left Column.

In E1 to G1 type Rank State $Owed

Enter the state to compare in F5
Enter the following formulas.

e5: =INDEX(Rank,MATCH(F4,State,0))
E4: = e5-1
E3: =e4-1
E6: =E5+1
E7: =E6+1
F3: =INDEX(State,MATCH(E3,Rank,0),1) and copy to F2, F6:F7
G1: =INDEX(Owed,MATCH(E2,Rank,0),1) and copy down to G1

Note: this will fail if two or more states owe the same amount.

HTH
Peter Atherton.
 
I forgot to include the rank function for column C. However, as pointed out
in the last post the closest values fail when values in amount owed are
duplicated. The solution is to use a modified RANK function that I call
Nominal Rank. The first duplicated value gives the true rank and the second
duplicated value has an added one.

Enter the following formula and copy down.

C2: =RANK(B2,Owed)+COUNTIF($B$2:B2,B2)-1

HTH
Peter
 
Back
Top