Save Complex Formula with airSlate SignNow

Save complex formula

Haidee here from exile Jeff so we recently did a survey where we asked people what they wanted to learn most about Excel formulas and the number one response to that survey was how to create a complex formula and this is a really huge topic because on the one hand you've got complex formulas that really shouldn't be complex and can be simplified and on the other hand all the way across the spectrum you've got legitimately insanely complex formulas that do really hard things like extract data based on multiple criteria so in this video I want to introduce the topic with two examples the first example is an example of building a complex formula that ends up complex but then later can be simplified and in the second example we build a moderately complex formula that really can't be simplified it's just the way it has to be done in Excel all right let's go do the training okay so just a few words to start off with how you approach writing more complex formulas in Excel first there's a number of things that you should understand you need to understand references excels operators and order of operations logical functions how Excel works with dates and times and you should have a general awareness of the functions that Excel provides we cover all this in formula course that we've got so I'm not going to get into that in this training as far as what makes a good formula you want a formula that's got clear logic it's reliable and robust and in general you're trying to write the simplest possible formula nobody likes overly complex formulas for the approach I recommend that you work in small steps and I'll show you what I mean in this training and that you look for opportunities to simplify and I've got an example of that coming up as well so in the for example what we want to do is calculate an expiration date one year in the future in the same month but at the end of the month so for June 15th 2015 we want June 30th 2016 that's the goal and let's say that you understand a little bit about the functions that are in Excel you know there's a year function so we could pull this data part the original date I'm gonna grab the year and then I'll get the month and then the day and then once I've got that I could using the date function I could grab this value here and add one then put it in the month value then pick up the day value and that gets us at least into the next year but we still have the problem of how do we move to the end of the month now let's say you look around a little bit and then you realize there is a function that might help you there that function is called end of month it's actually EO month and if you give it a start date in this case we want to give it 0 months we want to stay in the same month it will move the date to the end of the month and you know this is actually the answer we're trying to get to and if we write that on one formula we would have this and a month and then inside of that we need to use date and an inside a date we use year we grab the year out of this cell and we add one and then we get the month and then we get the day and and we closed it up and we just have to supply the number of months which is zero and this is our you know moderately complex formula that gets us our date now after you look at this for a few minutes you might realize like Oh end of month allows me to specify the number of months so what this means is this whole formula could be reduced to a very simple formula we could just use end of month and give it the sell and then give it 12 months instead of 0 and that will give us a date now if I change this to say July 15th this is going to give us the right answer and this will - but you can see that this is a much easier formula to understand I'll go ahead and put it down here so you can see how it works and a month start date 12 and when we copy this down you can see we get the correct date so the point is that there's many ways to solve these kind of problems in Excel and sometimes as you work through a problem you may end up with a solution that's overly complex and there might be an opportunity to simplify it all right in this example what we want to do is we want to count the number of words in a cell and there is no out-of-the-box function in Excel that will give you a word count so we'll have to build up our own formula step by step and the way we're going to do that is we're going to notice that when you have words in a cell they're separated by spaces and the number of words is related to the number of spaces the number of words is actually the number of spaces plus one so if we can figure out how many spaces we've got we can figure out how many words are in the cell so to start off I'm going to figure out the length of this text that's 47 characters and then I need to figure out how many spaces there are what I'm gonna do is I'm gonna use the substitute function and I'm gonna point at this cell up here I'll look for a space and replace it with an empty string and that will give me this text here with no spaces and when I copy this down you'll see that we have 40 characters so now I know logically that if I take this number and subtract this number that gives me the number of spaces which is actually not quite the number of words to get that I need to add one more and this will you know this is the basic approach we're going to take in our all-in-one formula the only thing to notice here before we move on is that if I add another space up here then that will increase this will get nine here and that's not the correct number of words and in fact if I add another space here well this this problem will just keep getting worse and we can fix this by changing the way we're counting the original string instead of just counting what's in b5 we can go in and add the trim function here to strip out all extra space that's in b5 before we do a count and now you can see we get back to the original solution this gives us 47 because what happens is we actually strip out this extra space here and the one at the front and then we do a character count and then we can correctly figure out the word count so the entire all-in-one formula looks like this it's going to be Len trim and we're going to grab this value here and then we need to do - and it's Len in this case we want to do substitute pick up the same cell reference and we're looking for a space we're placing it with an empty string and I'm going to close that up and then add one okay that's our all-in-one formula and if I copy this down here and then change the references then we'll get a correct word count all right so I hope you enjoyed that training on how to create a complex formula in Excel the worksheet that I used in the training is attached below this video so make sure you download the worksheet and give those examples a try and if you have any questions or comments about this training then leave a comment below the video I'll talk to you again soon