Find two closest values that are higher

[calebm12]

Got an equation nightmare on my hands.
So i have 3 columns of data
A: State Abbreviate
B: Benchmark Number
C: New Number


I am trying to get an equation that in column D will return the 2
closest values in column B for a value in a cell in Column C.
So if column C1 is 100
And column D1:D5 is 101, 115, 98, 5,92
It will find values 101 and 115 and put them in column D. The trick
being was that i was hoping it would also return the State Abbreviate
for this record. So if Fl is 101 it would return Fl: 101


Any ideas on how to do this???

[DonkeyOte]

Questions:

So always looking for 2 numbers > criteria number ?

Are numbers unique ?

Returning the 2 numbers is not too difficult

D1: =SMALL($B$1:$B$5,COUNTIF($B$1:$B$5,"<="&$C$1)+ROWS(D$1:D1))
copied to D2

If the numbers are unique you can use INDEX with MATCH to return the associated state:

E1: =INDEX($A$1:$A$5,MATCH($D1,$B$1:$B$5,0))
copied to E2

if there are dupes you can still do it but formula becomes a little more complex, let us know.

[calebm12]

the numbers are not unique in that both column B and column C could have repeats. what i am doing is that each year a state submits a grant application. in the grant application they specify the unit cost of something. i want to compare this unit cost against all of the states unit costs from the previous year....and return the previous years 2 values that are above but closests. does that makes sense.

i tried (with lots of help from a not very responsive board) to get it to work. Ended up with this equation. The problem being that if the proposal year value is in the top 2 of all the values last year it returns a #n/a. train is a defined name for the column of last years proposals.

=IF(LARGE(train,1)<>$P7,INDEX(sa,MATCH($P7+SMALL(IF(train-$P7 > 0, train-$P7,9.9999999999999E+307),1),train,0)) & ": $" & INDEX(train,MATCH($P7+SMALL(IF(train-$P7 > 0, train-$P7,9.9999999999999E+307),1),train,0)),"N/A") & "; " & IF(AND(LARGE(train,1)<>$P7,LARGE(train,2)<>$P7),INDEX(sa,MATCH($P7+SMALL(IF(train-$P7 > 0, train-$P7,9.9999999999999E+307),2),train,0)) & ":$ " & INDEX(train,MATCH($P7+SMALL(IF(train-$P7 > 0, train-$P7,9.9999999999999E+307),2),train,0)),"N/A")

would it help if i attached a sample spreadsheet??? this thing has me all sorts of confused so i am wondering if all my typing is even more confusing

[calebm12]

just in case a working spreadhseet helsp visulaize....here it is.....i am working out of the close heading column!

REALLY APPRECIATE your time!

[DonkeyOte]

If as you imply you have posted this elsewhere please provide a link both here & "there" wherever "there" may be... a number of people are members of multiple boards so it would most likely be spotted, we're not opposed to x-posting as other forums can be but we would ask you provide all links so as to ensure people aren't spending time answering something that may have been answered (satisfactorily) elsewhere.

[calebm12]

i had posted this like 3 months ago.....and have been hunting and searchng for it and cant find it. i think it was on a google groups page....but i cant for the life of me track it down....

i know this goes against board protocol....but i just repicked up the project...saw it wasnt working and was gonna start fresh. i am only a member of this board, utteraccess, and google groups....and cant find reference of it anywhere.

[DonkeyOte]

OK just to see if I am going down the right route...

To handle possibility of duplicity and to avoid need for CSE Array I added the following:

Please Login or Register  to view this content.

This way you have unique numbers for all - but the variance between BN & P is minute and can be removed using Rounding whilst at the same time allowing you to differentiate between states with same value in P.

Please Login or Register  to view this content.

Which for me generated the following output:

Please Login or Register  to view this content.

Using the LOOKUP approach I hid error returns where there were not sufficient number of values to return - eg BL7 there was only one prior year value > current year value - namely North Dakota so only 1 value returned in the string.

If the above is along the right lines we can adjust for the Lower values on a similar theme.

[calebm12]

wow. thats it!
amazing. so i would need to add a calculation column (BN) for each high low calculation?
how could i adjust this for the lower?? change the smalls to larges?

many many thanks.....

[DonkeyOte]

For the Lows I "suspect" the following would work:

Please Login or Register  to view this content.

The above uses the same helpers as used in BL formulae - ie uses BN to differentiate as and when required.

[calebm12]

i will give it a shot.....many thanks!

[calebm12]

everything works great! thank you, thank you, thank you. you have wiped away my headache

[DonkeyOte]

Glad to hear it, please remember to mark thread as Solved (see FAQ / How To ?)