Look at a list and give closest value UP and DOWN

sg2004

New Member
Joined
Mar 20, 2009
Messages
46
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 :)
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

New Member
Joined
Mar 20, 2009
Messages
46
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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

New Member
Joined
Mar 20, 2009
Messages
46

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Using your posted scenario...try these regular formulas...
Code:
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:
Code:
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:
Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top