Excel Magic Trick 1085

DESCRIPTION

What if you are not allowed to have a helper columns to lookup Price and Region with VLOOKUP? And you need to calculate Total Revenue by Region?

This video shows how to create a “Relational Database like” Array formula to calculate Revenue by Region when the fact table (transaction table) has only Product Name, Units Sold and Sales Rep and you have a lookup table to get Product Price and a second lookup table for Sale Rep’s Region.

This video shows how to create a PowerPivot-like formula without PowerPivot!

(00:08 min) Look at Helper columns to solve this.
(02:10 min) Simulate VLOOKUP Helper Column In Array Formula for Number using SUMIF function
(07:23 min) Simulate VLOOKUP Helper Column In Array Formula for Text using LOOKUP function
(09:03 min) Total Formula for Revenue by Region

Lookup Adding. SUMPRODUCT Lookup Adding. Simulate Lookup Helper Column in Array Formula. Helper Column in Formula. Simulate PowerPivot Formula without PowerPivot

WORKBOOKS

Download “Excel Magic Trick 1082 - 1100” Excel-Magic-Trick-1082-1100.zip – Downloaded 37 times – 2 MB




Be the first to comment

Leave a Reply