Excel Magic Trick 1493 Part 2

Excel Array Formulas Allocate Invoice Header Amounts To Transaction Line Table


In this video learn how to take Invoice Shipping and Discount Amounts from an Invoice Level (Header) Transaction or Fact Table and allocate those amounts to the Invoice Line Item Level Fact Table so we can use the Line Level Product Criteria (Filter) to create a Shipping and Discount Amount Report by Product. This is a typical business problem where the grain (granularity) of the two Transaction / Fact Tables is different and in order to use a filter or condition / criteria from the Line Level Transaction / Fact Table we must first allocate the Header Level amounts to the Line Level.

We use Excel Array Formulas to solve this problem. We use the Excel Spreadsheet Functions SUMPRODUCT, SUMIFS and VLOOKUP. We see how to simulate relationships with Excel Spreadsheet Formulas using the SUMPRODUCT function and the SUMIFS Function with a Function Argument Array Operation.


Download “Excel Magic Trick 1491-1500” Excel-Magic-Trick-1491-1500.zip – Downloaded 484 times – 10 MB

Be the first to comment

Leave a Reply