I've never really used array formulae in Excel; as such, I'm not entirely sure if I'm using them correctly, or if I'm doing something else wrong.
A version of the spreadsheet I'm working on is attached, so you can follow along.
Basically, I'm trying to have Excel pull aggregate information out of the main table, then use that data to pull a number out of a different table.
This is what I'm trying to do, more thoroughly explained:
(1) have Excel determine the number of tests per "Lot", then go over to the side table, find the appropriate column (based on that number of tests) in the table to the right;
(2) then find the average compaction % of all the tests within a given "Lot," find the difference between that and the target (94%), then use that number and find the first value in the column (found in step 1) that is "greater than" the average compaction % deviation;
(3) and, finally, after finding the appropriate value in the column selected in step 1, I want it to return the corresponding value from the right-most column of the table Excel is reading to cell I9. Once the process works, I'll just drag it down and it will do the grunt work for me.
Long story short, I'm so close I can smell victory, but it's giving me a weird result (not to mention my final equation is long and clunky), so any help would be appreciated. Details are in the attached workbook.
Sorry if this post is rather long-winded, but I didn't want to just throw the spreadsheet out there and ask someone to fix it without any explanation.
Thanks!
Bookmarks