I have two tables CompareValues and CompareAgainst.
Every record in the CompareValues table has data in every field except UnderFilter. This is where I would like to run a test against CompareAgainst producing a 1 or 0.
Every record in the CompareAgainst table is user entered and blanks are allowed, and count as a sort of 'Include All'.
Example1:
If there's only one CompareAgainst record, and every field is blank except the filter field, which is 99999, and every record in the CompareValues table has a cost under 99999, then every record will result in a 1 in the UnderFilter field.
Example2:
Given the CompareAgainst record described in Example1 above, and a second record where the Var1 column has the value 'A_5' and a filter of 88888 (and no other data in the other fields). The 99999 filter will apply to all CompareValue table records except those with a Var1 of A_5. Those will use the 88888 filter. Any A_5 records with a cost between 88888 and 99999 should return zeros
If I were able (and had the desire) to use nested IFs, the priority order would be:
1)Cost
2)Var1
3)Var2
4)DateBegin<=[@Date]<=DateEnd
5)(DateBegin<=[@Date] AND DateEnd=BLANK) OR (DateBegin=BLANK AND [@Date]<=DateEnd) --> equal priority.
I'm not sure I'm explaining that clearly so I added a tab to the attachment that might help explain.
**If the result is actually mulitple Filters that a CompareValues record 'qualifies' for, the cost should compare against the minimum filter value, and then return 1 or 0**
I've tried a SUMPRODUCT, but was only able to determine how many filters a record could potentially qualify for.
I then tried a MIN(IF( array formula by removing the SUMPRODUCT and making the CompareAgainst[Filter] the ValueIfTrue, but was returning some values that didn't make sense, and I'm not familiar enough with array formulas to troubleshoot effectively. This formula is what is saved within the attachment. I colored some boxes red, as in these, the B_1 records should not have be returning 1260, and I don't know why this is being returned.
Obviously, once I'm getting the correct filter in place, a simple IF( can output the 1 or 0.
Not sure if I'm getting really close (feels like it) or if I'm just making it more complicated than it needs to be, or do I maybe need to seek out a VBA solution? There's a couple VBA threads that appear as though they may be pertinent, but I only have a very basic understanding of VBA.
I'm going to be working on this all weekend until I get an answer, so I shouldn't stray too far away from the forums. Please feel free to let me know if I can clear anything up. Thanks in advance!
Bookmarks