Stumped...How do you find closest value in a table of data

[TurboJ]

I'm close but just can't seem to figure it out.

General view of the dataset:

DATE#1 DATE#2 DATE#3
Count %Target Count %Target Count %Target

	6/1/2015		6/2/2015		6/3/2015
Row Labels	Load	%Target	Load	%Target	Load
Waste
Ben McIntosh</SPAN>
Payton Haney</SPAN>	4</SPAN>	103.3%</SPAN>
Greg Beasley</SPAN>
Terry Hudson</SPAN>
Todd White</SPAN>	24</SPAN>	97.5%</SPAN>
Ron Weddle</SPAN>	2</SPAN>	92.9%</SPAN>	29</SPAN>	99.1%</SPAN>	41</SPAN>
Zackary Walker</SPAN>					31</SPAN>
Brandon Hamilton</SPAN>			23</SPAN>	99.2%</SPAN>
Paul Russell</SPAN>			30</SPAN>	103.8%</SPAN>	28</SPAN>
Michael Betancourt</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2><COL><COL><COL><COL></COLGROUP>

Much wider range but that is a sample. What I am trying to do is go in there and identify across the entire data set and find THE %TARGET value that is closest to 100%. Thats the first challenge; i'm not familiar how to use index(match(min(ABS) method to find closest values in situations where I have multiple columns of the same data. I can do it for a single column but that doesn't solve my automation problems.

Next, once that is figured out I need to add the logic that finds the closest %TARGET value that also has at least 25 loads. I've done that across the same data set when I was looking for MAX values using a combination of IF & MAX array statements but until I figure out the first part i'm not sure how easy it will be to but that additional critera check.

The joy behind all this is its written against a pivot table in which i have to make my various data range names dynamic to adjust when the pivot tables change since there is stacked subsets of data I have to run the numbers for. I've got most that figured out just takes so much coding. If anyone can help me figure out how to find the closest value in the data set value above that would be a wonderful start.

The biggest thing I am confused on is how I can get a match value for both my "row" & "column" to input into the INDEX function that is used when you use the typical approach to finding the closest value. Any help is greatly appreciated.

 

[Mackers]

Hi TurboJ

My initial thought would be to add two helper columns (four if you want the 25 to be taken into account), the first with the % closest to 100 for that particular row, and the second with a number identifying which column the % came from. I know it's not elegant but it sounds much simpler than writing a very complicated formula to return x and y coordinates of closest to 100.

From your request you seem to have a good grasp of the skills you would require to do this - perhaps using some kind of match(min(abs(range - 1)), abs(range - 1), 0). Does that help at all? If not then someone with more experience than me might be able to help you

Mackers

 

[TurboJ]

Hi TurboJ

My initial thought would be to add two helper columns (four if you want the 25 to be taken into account), the first with the % closest to 100 for that particular row, and the second with a number identifying which column the % came from. I know it's not elegant but it sounds much simpler than writing a very complicated formula to return x and y coordinates of closest to 100.

From your request you seem to have a good grasp of the skills you would require to do this - perhaps using some kind of match(min(abs(range - 1)), abs(range - 1), 0). Does that help at all? If not then someone with more experience than me might be able to help you

Mackers

Thanks, I actually just got done doing that in the intriem. I used similar logic for each column's worth of target payloads combined with a column address (for automation purposes) and then used the same logic on the row of results. Its not clean but it works....i'm still really curious though if there is direct or indirect ways to use INDEX functions on a 2 dimensional array of data since I work primarily with data that is the results of pivot tables so I always have complex data sets.