Hello,
I'm looking for some help in creating a complex nested array formula for evaluating a few columns of data. I would like to find the sumproduct of two differing rows, based on nested lookups. Here's an example table of data similar to mine:
This is an oversimplified example of what I am trying to do. Products are sold at different weeks throughout the year, but purchased in bulk. Purchases can have discounts applied to the bulk order. I would like to use a formula to calculate the total discount applied to the purchases of each product type. Here is an example of my data and a summary of what I am trying to achieve:
Excel 2012
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
I am trying to create a formula that would replicate the values of cells B14 and C14. In this example, a discount is applied to the purchases (UNITS P) in week 1. Column B indicates the actual week and Column C indicates the week purchased. Column D indicates the weekly volume and column E indicates the volume purchased. Column F is the discount applied to the units purchased in that week.
Currently, cells B14 and C14 are manually summing the product of the UNITS x the DISCOUNT for each PRODUCT that was purchased at a discount.
A formula would have to evaluate column A to find a match for the product in row 12; this match would provide the units in column D for that week. The formula would then have to evaluate which week those units were purchased in (match the row value of column C to column B) and return the discount value for that week (Column F).
In this example, for "ORANGE", the formula would return week 4 and week 7 units. It would then identify week each purchase was made (week 1 and week 4 respectively), and then multiply the units by the discount for those weeks ($5.00 and null, respectively).
I actually have 52 weeks of data, with multiple "PRODUCTS" and "DISCOUNTS" happening all over, which significantly complicates this workbook if it were to be processed manually.
That's a lot of typing.. hopefully I've covered this off with enough clarity that someone can make sense of things.
I appreciate any help in advance.
I'm looking for some help in creating a complex nested array formula for evaluating a few columns of data. I would like to find the sumproduct of two differing rows, based on nested lookups. Here's an example table of data similar to mine:
This is an oversimplified example of what I am trying to do. Products are sold at different weeks throughout the year, but purchased in bulk. Purchases can have discounts applied to the bulk order. I would like to use a formula to calculate the total discount applied to the purchases of each product type. Here is an example of my data and a summary of what I am trying to achieve:
Excel 2012
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | PRODUCT | WEEK | WEEK P | UNITS | UNITS P | DISCOUNT | TOTAL |
| 2 | APPLE | 1 | 1 | 8,360 | 33,398 | $ 5.00 | $ 166,990 |
| 3 | APPLE | 2 | 1 | 8,306 | - | $ - | |
| 4 | APPLE | 3 | 1 | 3,265 | - | $ - | |
| 5 | ORANGE | 4 | 1 | 9,146 | 19,109 | $ - | |
| 6 | APPLE | 5 | 1 | 4,321 | - | $ - | |
| 7 | APPLE | 6 | 4 | 7,150 | - | $ - | |
| 8 | ORANGE | 7 | 4 | 3,418 | - | $ - | |
| 9 | APPLE | 8 | 4 | 8,541 | - | $ - | |
| 10 | TOTAL | 52,507 | 52,507 | $ 166,990 | |||
| 11 | |||||||
| 12 | SUMMARY: | APPLE | ORANGE | TOTAL | |||
| 13 | UNITS | 39,943 | 12,564 | 52,507 | |||
| 14 | DISCOUNT | $ 121,260 | $ 45,730 | $ 166,990 | |||
| 15 | AVERAGE | $ 3.04 | $ 3.64 | $ 3.18 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
I am trying to create a formula that would replicate the values of cells B14 and C14. In this example, a discount is applied to the purchases (UNITS P) in week 1. Column B indicates the actual week and Column C indicates the week purchased. Column D indicates the weekly volume and column E indicates the volume purchased. Column F is the discount applied to the units purchased in that week.
Currently, cells B14 and C14 are manually summing the product of the UNITS x the DISCOUNT for each PRODUCT that was purchased at a discount.
A formula would have to evaluate column A to find a match for the product in row 12; this match would provide the units in column D for that week. The formula would then have to evaluate which week those units were purchased in (match the row value of column C to column B) and return the discount value for that week (Column F).
In this example, for "ORANGE", the formula would return week 4 and week 7 units. It would then identify week each purchase was made (week 1 and week 4 respectively), and then multiply the units by the discount for those weeks ($5.00 and null, respectively).
I actually have 52 weeks of data, with multiple "PRODUCTS" and "DISCOUNTS" happening all over, which significantly complicates this workbook if it were to be processed manually.
That's a lot of typing.. hopefully I've covered this off with enough clarity that someone can make sense of things.
I appreciate any help in advance.
