The #1 Excel formula issue - Data structure | Excel Off The Grid

in this video we're looking at a critical topic that nobody ever talks about and that is how the shape of data affects how we use formulas inside Excel and we're looking at this across three different data layouts we have a pivoted data layout we have an unpivot data layout and a multi-table layout How does each of these affect formulas inside Excel let's go find out the data that we're working with is lease data we have the property name then we have whether it's residential or commercial after that we have the lease term and that's on a per lease basis after that we have the lease payments per year and that is on a different level of granularity because it's on a per lease but also a per year basis and it's these two different levels of granularity that will cause us our biggest issues for all of our data layouts we want to calculate the same numbers we want to calculate the leas payments for 2024 and 2025 and we want to calculate the AR lease term and both of these are only for the commercial lease types now because this video is about comparing data structures we're going to try and use a consistent set of formulas throughout so we might use filter and sum rather than a sufs but that's why because we want to try and be consistent in the approach that we take we also have a rule and that rule is that we need to create formulas that whenever we add new data for Years or new leases that that formula will still work okay let's go and head over and look at our first data structure the First Data layout is pivoted that means that we have a category horizontally across the top now this layout gives us a challenge because it means we can't select any specific column inside our table to do so we'd hardcode those specific years so let's go and see how we can do this our first calculation is going to be the total lease payments for 2020 24 and 2025 now we need to start by finding out which years are 2024 or 2025 so we're going to check where our header row is bigger than or equal to our value in cell D20 when we press return on that you can see every single item says true but that's not true and this is because the header row of a table is always text but our value in D20 is a number therefore we need to convert that header row into a number for that we're going to add minus minus onto the start of our reference you can now see that we get trues and falses but also hash value that occurs where our header row is a text value because we can't times that by minus one and then minus one because it's text so what we need to do is to add an if error if error Open Bracket and then if there is an error we can return false we'll close that bracket and press return so we now get trues and falses right now let's go and add our second condition which is where it's less than or equal to 2025 so for this we're going to copy our previous formula We'll add some brackets to make sure that this evaluates correctly we'll then paste our formula and we want this where it's less than or equal to D21 so now we get ones and zeros where those values definitely match okay next we now want to wrap this in a filter function because we want to filter our entire table where our column is 2024 or 2025 and then we can close that bracket so that now gives us all of those values but we only want it where it is commercial so let's add in another filter function filter Open Bracket so we want to filter on that previous return value but we only want that where our type is equal to our value in cell d23 we can now close that bracket and now we only get the ones which are the right year and also the right least type and finally we can now wrap this in the sum function and that is the value that we want so by working with pivoted data it can be quite difficult the formulas we have to use can be quite Advanced now let's go and have a look at the average lease term well that's much easier equals average of our filter and we want to filter our lease years where our type is equal to commercial so we'll close the filter close the average and there we go we've now calculated the right number okay let's go and add this onto our score chart to some on the pivoted data by column was actually quite tricky because we wanted to make sure that the data would expand but to calculate on that least term that was quite simple so let's give this overall data structure a 4 out of 10 our second data structure is UN pivoted now because we have a single table and our data is now on a lease and year basis that means we've had to duplicate our lease term so let's see how this impacts our calculations for our sum across years this is reasonably simple we can use the sum of the filter and we want to filter the value column and we want that where our Open Bracket year is bigger than or equal to 2024 and then we also want that where that same year is less than or equal to 2025 and we also want that where the type is equal to commercial so we'll close that bracket we'll close the filter we'll close the sum we'll commit that and we now get the right value but how do we calculate the average lease term now that we have duplicate values in our data set well we need to start by calculating a unique list of property names so equals unique Open Bracket and we want to filter our property name and we want that where our type is equal to commercial we can close the filter and then close the unique that now gives us our property names we now need to look up the least term for those properties we're going to use x lookup so we want to look up that property name we want to look that up from the property name and then we want to return the lease years and then we can close that lookup and then we'll press return so that now just gives us the years for our lease properties which are equal to Commercial and then finally we can then add the average calculation on that equals average Open Bracket and then we will close that at the end and now that calculates the correct value for us right let's go add this data structure onto our score chart calculating the sum of the lease payments was no problem but it was the average that then became difficult because we had duplicate values so let's give that another four out of 10 hi there I'm Mark from Excel Off the Grid and we're on a mission to help more people automate their work with Excel because nobody should have to work late just because they have rubbish systems at work so you can help us on this Mission all I want you to do is to click the like button and also subscribe to our Channel this means that more people get to find out about Excel of the grid and more people get to work less and spend more time doing what they love the final data structure is multiable we've normalized our data so that we don't have any duplicate values apart from the unique key which is the property name which exists in both tables but it means we have a table for the leas term and a table for the leas payments right let's go and see what kind formulas we can use on this data layout we'll start with equals sum of our filter and we want to filter our value column and we want that where our year is bigger than or equal to 2024 and we want that where our year is also less than or equal to 2025 now we can't just use our leas type because that exists in a different table therefore we need to perform a lookup for that we're going to use x lookup so we're going to multiply that by the X lookup Open Bracket and we want to look up our property name from our property table and we want to return the least type we'll close that bracket and we only want that where it's equal to commercial so we'll close that condition we'll close the X lookup we'll also close that sum and filter and when we commit that we now get the correct value now let's go and calculate our average well that is reasonably simple equals average of our filter and we want the value of our least term where our type is equal to commercial we'll close the filter close the AR average and commit that and that now calculates the correct value as well so both of these were reasonably simple all we needed to do was to look up our lease payments from our other table to make sure we got the correct lease type right now let's go and add these onto our score table the sum of the lease payments was reasonably simple all we had to do was look up our lease type and also our average was quite simple so let's give this a score of 7 out of 10 so we can see that actually by having multiple tables where each of those tables is in a normalized structure reduces the complexity that we need to use for formulas so does data structure matter yes it does now you might be thinking but we can't control the data structure we just get the values that we get and then we use them well that's where power query comes in that's the tool that helps us shape data to be exactly the shape that we want so we can easily perform calculations inside Excel and that's it in this video we've seen data structure really matters even on a simple data set so next time that you receive some data from someone think about is this in the right format for me to easily calculate on it and then fire up power query so you can get it into the ideal format thanks for watching I hope you enjoyed it now it's time to click there so you can watch some more Excel goodness

Channel: Excel Off The Grid

Views: 6,664

Keywords:

Id: xvtoX6Q9GLo

Length: 11min 20sec (680 seconds)

Published: Thu Jun 20 2024