• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Nearest in a Range

G

GB

Member
Hi,

I have spent countless hours trying to solve a problem where, in simple terms, I am trying to the find the nearest value in a range referencing 3 criteria. In the following link I have supplied exactly what I am trying to achieve, describing my requirements.


http://dl.dropbox.com/u/60464004/Excel/FindNearest.xlsx


Appreciate your help.

regards

GB
 
Hi ,


Quite an interesting problem. However , I think the first step is to simplify matters ; looking at your formula in column G ( labelled Validation ) , do you think it can be simplified a bit ?


=IF(AND(F5>MaxRate,FindRate+MaxRate>=E5,FindRate+MaxRate<=F5,A5>=FromDate,A5<=ToDate),ABS(B5-FindRate-MaxRate),IF(AND(E5<0,FindRate-MaxRate>=E5,FindRate-MaxRate<=F5,A5>=FromDate,A5<=ToDate),ABS(B5-FindRate+MaxRate),IF(AND(FindRate>=E5,FindRate<=F5,A5>=FromDate,A5<=ToDate),ABS(B5-FindRate),"")))


What are the valid values of MaxRate and FindRate ? Can both be positive as well as negative ? Can MaxRate be less than FindRate ?


If F5 > MaxRate , then given certain conditions , the check FindRate+MaxRate<=F5 becomes redundant ; similarly for the other conditions within this formula. Can you clarify ?


Secondly , since the check for any date having to be between FromDate and ToDate is a basic check , can we not have a helper column doing only this check :


=AND(A5>=FromDate,A5<=ToDate)


Not that this changes anything , but it does make the subsequent formulae easier to formulate and read. Especially when you do not have a restriction about using helper columns.


Narayan
 
Hi GB,


How will you get multiple values for a single query when you have sorted out result between dates, each date will come up with a single value. The "smallest" should be found when you have more then one values for a date. if you want to find the smallest between the two dates that meet your criteria then you can simply use small() for rest of the criteria has been validated already in validation column? Isn't it so?


Faseeh
 
Hi, GB!


Maybe I was falling asleep when I read your post, or maybe I'm not clever enough, because your presentation and comments in your sample file are amazing, ... but I've been unable to understand what's the goal.

Would you be as kind as writing a few lines explaining it in again? It seems to be a nice problem.

Sorry for the inconvenience.


Regards!
 
Hi Narayan,

thanks for your reply. To answer your questions...


The MaxRate and Find Rate are always positive values.

The FindRate is always <= MaxRate.


The only reason I have + & - numbers in the Rate-UserDiff & Rate+UserDiff is to handle the daily fluctuation of when a Rate passes forwards or backwards through the MaxRate. Example if my FindRate = 1, UserDiff = 2, MaxRate = 10, Rate = 9.5 then this rate is within tolerance to be returned as true. It is simply my way (maybe not the right way) of capturing the records.


To reduce the size of the Validation query, yes I should improve the date check.

Thanks & look forwrd to your next suugestion.

GB
 
Hi Faseeh,

I am not after just the smallest value with a date range. I am after every rate in a date range that stand alone (providing the previous and next record are false. In the case where multiple records in a consecutive block are returned as true then I only want the smallest within that consecutive block, just like what I provided in the attachment.


regards

GB
 
Hi SirJB7,

I can understand your sleepiness. Thanks for replying. OK what am I trying to acheive?


Every day a Rate is either greater than or less than the previous days rate.

The rate will move for example not more than 1.86 in value forwards or backwards each day.

Thnk of this movement like a clock, for example when we pass through 12 we go to 1. If we rewind slightly we go back to 12.

Now think of the MaxRate being this 12 oclock position.


Now if MaxRate = 10, Todays Rate = 1, Yesterdays Rate = 9.5 then the separation between the two days is only 1.5.


So in my Result column I am trying to find all records that are within +/- of X (the UserDiff value) of the FindRate value over a date.


The complicated part is where I need to return the Rate that is nearest in value to the FindRate value when a block of consecutive rows are within the UserDiff value. See my previous post to Faseeh for further explanation.


Are you awake now? :)

regards

GB
 
Hi ,


Thanks for the clarification.


Taking the same formula given in my earlier post , I find that the following formula , gives the same result.


=IF(AND(F5>MaxRate,FindRate+MaxRate>=E5,FindRate+MaxRate<=F5,R5),ABS(B5-FindRate-MaxRate),IF(AND(FindRate>=E5,FindRate<=F5,R5),ABS(B5-FindRate),""))


The middle portion has been removed , and the date check , =AND(A5>=FromDate,A5<=ToDate) , has been introduced in R5.


Of course , the correct way would be to describe all the checks that need to be implemented , and then use formulae to implement the logic. I'll describe what the above formula is doing , and you can add to it when you post.


1. Check if (FindRate + MaxRate) is between (Rate - UserDiff) and (Rate + UserDiff) ; I find that if this check is implemented , the check (Rate + UserDiff) > MaxRate is superfluous.


2. Check if FindRate is between (Rate - UserDiff) and (Rate + UserDiff)


3. Check if the Date is between FromDate and ToDate.


Based on which of the above conditions is satisfied , different kinds of processing need to be done. I'll put this down in a subsequent post.


Narayan
 
Hi All


As NARAYANK991 has said correctly that date check has been used repeatedly in the formula we can simply place it in the first if() used to check for records only in the specified dates, following formula is a bit shorted and giving similar results:


=IF(AND(A5>=FromDate,A5<=ToDate)=TRUE,IF(AND(F5>MaxRate,FindRate+MaxRate>=E5,FindRate+MaxRate<=F5),ABS(B5-FindRate-MaxRate),IF(AND(E5<0,FindRate-MaxRate>=E5,FindRate-MaxRate<=F5),ABS(B5-FindRate+MaxRate),IF(AND(FindRate>=E5,FindRate<=F5),ABS(B5-FindRate),""))),"")


Till now there is no need for helper column, it will arise as soon as i stuck somewhete :p hehehe


Faseeh
 
Hi Narayan & Faseeh,

thanks to you both for your helpful feedback, which I have made these improvements.


The big question is... how do I get the "Result" to select the correct rows based on my original criteria. Any thoughts would be much appreciated.


regards

GB
 
Hi ,


Can you check the following link ?


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21152


Please do not click on the hyperlink ; copy + paste the entire address in your browser.


I have added 4 columns , one of which is the same as your column G ( labelled Validation ) , but with the simplified formula. You will have to check the results with more data.


Narayan
 
Hi Narayan,

a big thank you. I will test your formulaes and see how it goes.

regards

GB
 
You must log in or register to reply here.
Back
Top