Saturday, January 16, 2010

Excel Formula : Closest Match

Arun Singla one of my blog reader has sent me Excel Formula in response to my previous post Custom Function : Excel Closest Match using VBA.

Formula sent by Arun gives similar results as achieved by Custom Function using VBA. I am sharing the formula with you.

For getting less than equal to value
=IF(COUNTIF(Data,Target)>=1,Target,SMALL(Data,COUNTIF(Data,"<"&Target)))
For getting greater than equal to value
=IF(COUNTIF(Data,Target)>=1,Target,LARGE(Data,COUNTIF(Data,">"&Target)))

In above formula : Data is unsorted data range name, target is the named range having target value.

Now let me explain how does this formula works. I will explain first formula which calculates less than equal to value.

Step 1 - Formula calculates the number of values matching target value using countif function of excel. COUNTIF(Data,Target) gives the total number of values matching exactly with the target. In case you get even one value matching with the target, you return the target as closest match, no need for further working.

Step 2 - In case formula does not get matching value at step 1 , it moves to find the closest match. SMALL(Data,COUNTIF(Data,"<"&Target)) Returns the k-th smallest value in a data set. COUNTIF(Data,"<"&Target) gives us total number of data points less than target. We are using this as k-th smallest value which is closest match less than the target value. I hope this explanation is not confusing to you. Check out the file with this formula to find closest match in excel to understand it better.
Posted by Yogesh Gupta at 10:05 PM
Labels: ,
Reactions: 

10 comments:

  1. =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) array entered. - for the closest match

    sam
    ReplyDelete
  2. Same can be achieved using the lookup formulae and also a comination of index() and match() formulae by using the appropriate value in the last argument of lookup/match formulae.
    ReplyDelete
  3. Limitation being - the data has to be sorted by the lookup value.
    ReplyDelete
  4. Hello Sam

    Thanks for sharing this formula, however this does not work on the data I have in file uploaded along with this post. I entered it as CSE (Array) formula but results into error.

    You wanna give it a try ? share your file if you succeed
    Regards//Yogesh Gupta
    ReplyDelete
  5. Hello Vipul

    Thanks for your inputs, however we are dealing with unsroted range here. Some times it is not possible to sort a range just to make a particular formula work.

    Regards//Yogesh Gupta
    ReplyDelete
  6. Yest it does...I have uploaded the file. I have been using it for many years

    http://cid-af0f671fc64e8122.skydrive.live.com/self.aspx/New%20folder/CMATCH.xls

    sam
    ReplyDelete
  7. Hello Sam

    Thanks for sharing file with the formula. Now I realised that why this was not working when I tried to use this formula.

    This array formula fails to work in case the Data range in more than one column. I was trying it with data in two column and was getting error.

    Thanks
    Yogesh Gupta
    ReplyDelete
  8. Hi Yogesh..you are right my formula does not handle multiple columns...
    May be we can modify your formulas a bit
    =SMALL(Data,COUNTIF(Data,"<="&Target)))
    will work. So we dont need the IF if an exact match is available

    sam
    ReplyDelete
  9. Can I some how use the same formula to get the top 10 orders with customer name, value etc. Ie the hole row?
    ReplyDelete
  10. Hello Rickard

    What you are aksing for is possible technically and will need lots of formulas. Instead I will go for a PivotTable and filter it for TOP 10 values. This will be more robust and easy way to do what you need.

    Let me know in case you need any help in making PivotTables and filtering them on TOP 10 Values.
    ReplyDelete

Subscribe to: Post Comments (Atom)