closest

  1. F

    Formula to find closest Thursday to a date

    I have a sheet with a date in Column B. I need to find the closest Thursday that is 150 days prior to this date. I can't seem to get the formula to make this happen. Any help would be appreciated.
  2. S

    need tricky formula

    I'm looking for a non-array formula that will find the position in a list of the number closest to another number. In A1:A6, let's say I have 1;2;7; ;8;9 The closest number to 6 would be 7, so the formula would return 3 for the 3rd position. The closest number to 3 would be 2, so the formula...
  3. I

    Looking for Assistance with Formula (Top 30 Values Closest to ZERO) Please :)

    <tbody> 1 <tbody> -0.14 </tbody> top 30 values in column B1-B32 that is closest to 0 2 <tbody> -0.04 </tbody> I want to project it as a formula in C1-C30 3 <tbody> -0.02 </tbody> 4 <tbody> 0.09 </tbody> 5 <tbody> 0.23 </tbody> 6 <tbody> -0.12 </tbody> 7 <tbody>...
  4. U

    Format the 3 numbers in a column closest to ZERO

    Ok so here is what I'm trying to do. Here are my numbers .. I want to highlight the three lowse numbers closest to zero. Which would be numbers 0.1 , -.08 and 0.9 ... I have tried several formulas but nothing has worked. Any suggestions or the right conditional format formula would be...
  5. R

    Select the greatest value in the list and return a cell next to it

    Hi, How could I go about taking my cell value in S33 and selecting the closest greater than value in T19:T30, then returning the corresponding Overload in the column to the left of the closest greater than value? Which formulas would I use to achieve this? Thanks in advance.
  6. K

    Distance between addresses

    Hi, I am looking for a way to find the closest destination based on a post code entered by an user. As below, when an user inputs "Post code A", I would like the macro to use maps:) and bring back the closest location to post code A which for e.g may be Post code Y Post code entered : Post...
  7. J

    Sumproduct - Closest to?

    <colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead> E F G H </thead><tbody> 36 1. Trapstyle Thea 96 14.50 £127.89 37 1. Trapstyle Thea 94 16.50 £134.52 38 1. Trapstyle Thea 91 16.00 £138.52 39 1. Trapstyle Thea 89 16.00 £142.52 40 1. Trapstyle Thea 85 16.00...
  8. R

    Mutiple lookup, Exact and closest.

    Hello, I have some data in a table that looks like this Text, float, Result 1, Result 2 I am hoping that someone can help with a formula that will lookup the row with the closest float where the text matches. I would say it is a multiple criteria lookup where one criteria is an exact...
  9. A

    conditional format closest values across three ranges

    Hi I have a bit of a challenging conundrum (for me at least!). Id like a conditional formatting rule to highlight the three closes numbers across the three columns, but with only 1 cell highlighted per column. So if my data was as follows, the closest 3 would be the ones in bold. (1 per...
  10. G

    Looking for closest value without duplicates

    Hi guys, I am looking for a way to match the closest value in a column without any duplicates. I used the following method : https://www.extendoffice.com/documents/excel/4279-excel-find-closest-nearest-value-greater-than-less-than.html This method worked fine : however I am looking for one...
  11. A

    Index-match return exact match, if not closest match

    I have this formula : INDEX(A2:A20,MATCH(1,(F10=C2:C20)*(F11=B2:B20),0) It returns the exact match. I need the formula to return the closest match if the there's no exact match this is my data : A2:A20 I need to find the PO that is closest to the date 19-03-17 , the result should be : P91180...
  12. S

    forcasting an out of stock date

    I have an excel sheet where I forecast an out of stock date. {=MIN(IF(Sheet1!K4:K368>0,Sheet1!K4:K368))}. formula stopped working properly and is looking at the number closest to zero which Is a past date. I Only need the formula to look at todays date forward
  13. N

    VBA highlight closest values in a range

    Hi everyone, I have been trying to figure out a code that would highlight the two closest values of a range selection. What I mean by this is the two closest values (.9999 and .9998 for example) anywhere in a given range and be the two numbers must be adjacent in the range. I am very new to...
  14. D

    Closest Value

    Hi, Please help me, i need to get the closest number from the value from the Lower/Higher column. Let say value 5, the closest is the Lower number which is 7. Thanks <colgroup><col><col><col><col></colgroup><tbody> Value Lower Higher Result 5 7 11 7 7 9 13 9 4 5 9 4 15 17 21 17 32...
  15. O

    Closest Sum to a Given Number

    Hello, I am trying to create some type of formula or VBA script that will look at a series of given numbers and identify which numbers if summed together are closest to another given number. For example if I am given the series of numbers shown below, which numbers if summed together are...
  16. J

    Year from date

    Hey everyone!! Hope you all had a great holiday season!! I have J4 set with "=Mode(B4:B105)" which column B contain various numbers. Column A cells are dates, (A4=1/1/2019, A5=12/28/2018....etc going backward) How can I change the value of J4 ":B105" to the column B cell number closest to a...
  17. A

    Match closest time

    Hello, I have a list of stocks that were bought at a specific time but i don't know the price. I have a list of prices by time but it may differs from the time of action (differ in ms). Hoe can i match it to the closest time please in order to find the price? Time Action Price...
  18. R

    Formula for closest value

    I have a table somewhat like: Red 20 Red 30 Black 30 Black 20 Red 25 Red 15 Black 40 Closest to: 23 I want to write a formula that will return the value in column B that closest to 23 when column A equals Red. In this case 25.
  19. C

    Choosing the best cell

    Hi This post has two requests I have the data shown below: <tbody> A B C D E F 1 Target 1 Target 2 Value of Target 1 Value of Target 2 Best Target Best Value 2 5 4 -0.78 2.22 </tbody> I would like to research two possible options as I am not sure which will suit my purpose best...
  20. A

    To look the most close value in a list o matrix of excel

    Hi, I have become obssesed to finish a little program to find the optimum ECT (Edge Crush Test) to get the best BCT (Box Compression Test), and I am stuck with this part I have enclosed an small archive in order to explain more clearly my problem. I get in a cell a number that might be close or...

Watch MrExcel Video

This Week's Hot Topics

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