Advanced Array Formula: FILTER or SUMPRODUCT to Simulate a Relationship Between Two Tables! EMT 1748

our sales formula is retail price times 1 minus the revenue discount times the quantity that's no problem if we can add a column and build a formula like this we're going to round everything but we're going to look up the price multiply by one minus the revenue discount times the quantity we copy that down and then we make a pivot table but what if we could simulate the relationship between these two tables in a single cell array formula now we'll see how to do it with the old school sum product and the new school [Music] filter all right we're going to start our old school formula out with the old school lookup function vlookup i need to look up alpine comma within that full table right there and this is an excel table so i use my diagonal black arrow to put in d product the table name comma the item i'm trying to retrieve is in the second column so i put a 2 and we're doing exact match so you can put false or a zero now if i hit the f9 key that delivers a single value the single price this is not an array formula yet control z but what i really want for every row in this table is the net price given the discount for each row so now we multiply not times the column of discounts but in parentheses one dollar minus the column of discounts now as soon as i put a column of values into this formula and the array of items is going to be operated on by a math operator that's when we jump into the realm of array formulas so close parentheses if i highlight just this part in f9 you see it's delivering an array of items so you know that was an array operation each one of those items is the net price equivalent so that means for the first record two pennies of discount but we pay for every one dollar of price 98 pennies control z now if i hit f9 i get the discounted price for every row in the table control z now i multiply times quantity and when i hit f9 those are the row by row line sales values now we only want to pick out some of them we only want the ones for alpine so ctrl z at the end we'll multiply in parentheses how many of the items in the product column are equal to alpine close parentheses if i highlight this and hit f9 i get trues and falses only the rows where there's a true will we get the line item sales for aspen control z if i hit f9 i get numbers for aspen and zeros now i need to round control z so after the equal sign round and at the very end comma we're rounding to the penny so we put a 2. close parentheses f9 there's the rounded amounts now we need to add ctrl z since we're doing old school if we use the sum function we have to use that special keystroke control shift enter and i don't want to do that i want to use one of the cool functions in old school sum product that understands array calculations so any time you're adding the result from an array calculation put it inside of some product then you don't have to use that keystroke just enter or control enter because i'm going to put the formula in the cell and copy it down i go to the last cell and hit f2 that old school is looking good now if we're going to do new school well first off we get to use x lookup to look up the product comma and we give it in lookup array the column with the match items or the product names comma and the return array those are the items we're trying to go and get close parentheses that's delivering a single value times in parentheses 1 minus the whole column but wait a second that's the old school right where we have that column now we're going to filter it to get a smaller list of numbers so i'm going to say hey filter take that array comma and then include we have to look at product and ask the question are you equal to aspen now here's the interesting thing right here that include that has to work over the whole column just like the sum product formula but that one operation there will deliver a smaller list and then both the subtraction and the multiplication will work across fewer values now we close off that times the filtered quantity comma and we have to do another full column checking against the product are you equal to aspen close parentheses if i hit the f9 key well there's the unrounded values without all the zeros a much shorter list control z we'll have to round it fewer values we're rounding that's an array calculation two where the array calculation is a function argument array operation comma two close parentheses f9 there's our rounded amounts now because we're in microsoft 365 it understands array formula so we can just put it in sum and enter or control enter and copy it down so there's the new school there's the old school now this is a small data set i went ahead and tested these formulas on 200 000 rows of data and guess what there wasn't much of a difference i was surprised i thought some product would take longer but filter and some did 316 milliseconds which is not very much time didn't take very long to calculate some product took 345 only a 9 increase so that one took less time to type out this one took more time to type out and it's a longer formula but it looks like it calculated a little bit faster all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos that come from excel is fun all right we'll see you next video

Channel: ExcelIsFun

Views: 8,157

Rating: 4.9354839 out of 5

Keywords: Excel, Highline College, Mike Girvin, excelisfun, excel is fun, excel fun, excel magic, Excel Formulas, Excel Functions, formulas and functions, Excel Magic Trick 1748, EMT, EMT 1748, Microsoft 365 Excel, Office 365, Excel 365, FILTER Array Function, SUMPRODUCT Function, Simulate a Relationship Between Two Tables, Array Formula, Advanced Array Formula, Single Cell, Sales Report from two tables, Single Cell Sales Report, Report without helper column, Dynamic Spilled Array

Id: hAAWqGhRJz4

Length: 6min 58sec (418 seconds)

Published: Tue Aug 10 2021