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 : 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...

Some videos you may like

This Week's Hot Topics