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