Hi all,
I was hoping someone out there could help me understand how exactly a piece of a relatively complex formula that someone a while back helped me on is working. Will do my best to explain.
What the formula is doing successfully:
Taking the Store Number, Item Number, Date, and Sales Amount listed in each record (4 different cells in the same row), and finding the closest Date to the one listed in said record that has a Price value for the same Store Number > Item Number combination on a separate table. It then takes the record's Sales Amount and Divides it by that found Price value to get a Sales Unit value.
I've spot checked the results myself and can confirm the formula is working as it is designed to. However, I now have a need to understand how exactly it is doing that. There is a nested MIN(IF( statement within the formula that I can't quite wrap my head around as it seems to be going against my understanding of how these particular functions work.
What the actual records look like (with formula values in last column):
Record1.PNG
What the table the formula is referencing looks like:
Record2.PNG
The formula within the first cell under "Total Inc Sales (Units)" column:
{=opdata3[@[Total Inc Sales]]/INDEX('Item & Store Lookups'!$J$2:$J$109136,MATCH(MIN(IF((VALUE(opdata3[@[Store '#]])='Item & Store Lookups'!$H$2:$H$109136)*(IFERROR(VALUE(opdata3[@[ITEM CODE]]),opdata3[@[ITEM CODE]])='Item & Store Lookups'!$I$2:$I$109136),ABS(opdata3[@[Intervention Date]]-'Item & Store Lookups'!$G$2:$G$109136),9999)),IF((VALUE(opdata3[@[Store '#]])='Item & Store Lookups'!$H$2:$H$109136)*(IFERROR(VALUE(opdata3[@[ITEM CODE]]),opdata3[@[ITEM CODE]])='Item & Store Lookups'!$I$2:$I$109136),ABS(opdata3[@[Intervention Date]]-'Item & Store Lookups'!$G$2:$G$109136),9999),0))}
The part I am having trouble understanding is immediately after the MATCH statement opens; specifically:
MIN(IF((VALUE(opdata3[@[Store '#]])='Item & Store Lookups'!$H$2:$H$109136)*(IFERROR(VALUE(opdata3[@[ITEM CODE]]),opdata3[@[ITEM CODE]])='Item & Store Lookups'!$I$2:$I$109136),ABS(opdata3[@[Intervention Date]]-'Item & Store Lookups'!$G$2:$G$109136),9999))
The IF statement doesn't make sense to me. The logical_test piece is taking the value of the Store Number "=Item & Store Lookups" then multiplying it by the Item Number "=Item & Store Lookups", but neither of those individual statements make sense to me as returning anything other than TRUE or FALSE, nor is it anything other than the multiplication of two numbers from what I can tell, which doesn't meet the criteria of a logical_test piece of an IF statement.
Any help would be super appreciated. Let me know if I have not provided enough info.
Bookmarks