Look at a list and give closest value UP and DOWN

[sg2004]

Hi all.
Given a required price (cell F8 currently showing 4025 as below), I need to find within the table at the bottom (Range is E11:E21) the value that is closest to the required price (one that is larger, one that is smaller). This gives boundary values that are then represented as M and N in cells F6 and F7 (Currently showing 4055 and 3975 WHICH I'VE PUT IN MANUALLY BUT WANT TO MAKE AUTOMATED)


<TABLE style="WIDTH: 218pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=290 border=0 x:str><COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 136pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" width=181 height=22>M</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffffcc" width=109 x:num="4055.25">$4,055</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" height=22>N </TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" x:num="3975.25">$3,975</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #ccffff" height=22>Required Price</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99" x:num="4065">$4,025</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 136pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=181 border=0 x:str><COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 136pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" width=181 height=18 x:num="4295.25">$4,295</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4215.25">$4,215</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4135.25">$4,135</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="4055.25">$4,055</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3975.25">$3,975</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl24 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #99ccff" height=18 x:num="3895.25" x:fmla='=BDP("LMCADP 20090520 LME Comdty","px_eval")'>$3,895</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3815.25">$3,815</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3735.25">$3,735</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3655.25">$3,655</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3575.25">$3,575</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #ccffff" height=18 x:num="3495.25">$3,495</TD></TR></TBODY></TABLE>

Please help with the code for this in each of the 2 cells, I'm a newbie

 

[Domenic]

Try...

F6, confirmed with CONTROL+SHIFT+ENTER:

=MIN(IF(E11:E21>F8,E11:E21))

F7, confirmed with CONTROL+SHIFT+ENTER:

=MAX(IF(E11:E21<F8,E11:E21))

Hope this helps!

xl-central.com

 

[sg2004]

in F6 i have =MIN(IF(E11:E21>F8,E11:E21))
in F7 i have =MAX(IF(E11:E21>F8,E11:E21))

I have submitted both with ctrl shift enter.

In F6 I get the upper boundary which is good (i.e. i get the value from the list that is slightly larger than REQUIRED price)

In F7 though I don't get the lower boundary, instead the largest value in the list.

Help?
Thanks

 

[Domenic]

Actually, I didn't notice that part of my post was cut off. Here it is again in its entirety...

Try...

F6, confirmed with CONTROL+SHIFT+ENTER:

=MIN(IF(E11:E21 > F8,E11:E21))

F7, confirmed with CONTROL+SHIFT+ENTER:

=MAX(IF(E11:E21 < F8,E11:E21))

Hope this helps!

xl-central.com

 

[sg2004]

Yeah!
Thanks!

Last thing honest,
Next to the list there is another list of values.

When the boundaries are successfully picked out into cells F6 and F7, how can I have in F3 and F4, the values of the cells immediately to the right of those values in their original list.

So if it picked out 4055 and 3975 from the list, how could I have cells F3 and F4 pick up what is in F14 and F15 (which are the values next to our boundaries in the list)

Thanks

 

[Ron Coderre]

Using your posted scenario...try these regular formulas...

F6: =LARGE(E11:E21,COUNTIF(E11:E21,">="&F8))
F7: =SMALL(E11:E21,COUNTIF(E11:E21,"<="&F8))

Note: if the Req'd Price is outside the range of listed values
one of the formulas will return #NUM!

To avoid those errors, try these variations:

F6: =IF(F8 > MAX(E11:E21),"Too Big",LARGE(E11:E21,COUNTIF(E11:E21,">="&F8)))
F7: =IF(F8 < MIN(E11:E21),"Too Small",SMALL(E11:E21,COUNTIF(E11:E21,"<="&F8)))

Array formulas may be shorter....but, nobody I work with
seems to remember to CTRL+SHIFT+ENTER the darn things!

For your further requirements....
Try this:

F3: =VLOOKUP(F6,$E$11:$F$21,2,0)
F4: =VLOOKUP(F7,$E$11:$F$21,2,0)

Is that something you can work with?

 

[sg2004]

Thanks very much guys. much apprec