Function to Find Closest Value without Going Under
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi There!
I'm trying to replicate something that I did for testing in Excel.
Basically, I have a large list of values. For the attached example, they are just....
{3,6,9,...,30}.
These really represent ranges. In my other table (in the simplified example), I have values that are between 3 and 30 but are random, so...
{3,6,6,12,12,18,21,21,24,30}.
I want to return the closest value from the first table for each value in the second without going under.
In other words, the first table reads 3 but represents the range of values 3<=6. Therefore, when I have a random value of 3 (or 4...), it would simply return 3.
The function in Excel to do this would be an array:
={MIN(IF($A$2:$A$11>=$C2,$A$2:$A$11))}, where
$A$2:$A$11 = Value of Ranges (First Table)
and
$C2 the cell containing the value.
Solved! Go to Solution.
- Labels:
- Fuzzy Match
- Join
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @LindonB ,
To achieve that, I have used a batch macro to get the closest value one by one.
Let me know if that works for you.
Best,
Fernando Vizcaino
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello @LindonB , here is a solution with the comment included in the workflow.
(If this post helps, then please consider it as the solution to help the other members find it more quickly).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @WilliamR,
Thanks a million. I used a similar join approach. My only concern is generating so many unwanted rows, given the size of my data set. Still, that does seem to be the best approach! Thanks again.
