Send Custom Formula with airSlate SignNow

Send custom formula

okay so this video is about conditional formatting using a formula so we'll be using conditional formatting in not very basic ways but a little more advanced ways so one of the basic conditional formatting is you may want to do is highlight all the costs for your products that are above a certain amount so everything that's above $100 maybe you want to highlight them so the way you do it usually because this is simple task you want to format conditional formatting and after I highlighted it right so I'm going to pick a rule here and I'm going to say if it's greater than 100 pick the color so here you choose the formatting and we can see how everything is highlighted in this color now which is our basic conditional formatting so that's easy to do now what if we have a little different situation now we still want to check the same thing we want to check if the coast is above 100 dollars but what we're trying to highlight is the product name so if the product cost is above 100 dollars like in this case we would like this to be a different color all right so we can you we can do this using a formula so the first thing we have to do is create the formula so you can make it anywhere you like but usually it's the easiest to make it right next to your data and then we'll just remove this one this word once we're done we'll just remove this entire column so I'm going to start with an equal sign and I need a logical test so what I'm going to do I'm going to ask if this which is in D 2 is greater than 100 and that gets me true so if I send this down now everything that's above 100 is getting a true and everything else is getting false this is what we call logical test we're asking if the content of the cell is greater than the number we have now the cool thing about this is that when you get true surfaces you can use this for conditional formatting so if you can come up with a formula that will get you true or get your false you can use this for conditional formatting and whatever gets you true is going to color the cell whatever gets you false is not going to color the cell in this case everything that's true is going to get colored now what I need to do at this point I have to grab my formula that gets me my trouser forces so I'm I'm highlighting the first one on top which is in our second row so I'll go ahead and copy this escape out of this I don't want to change anything here now I'll go ahead and highlight all the product names because that's what I want to conditionally format go on a format conditional formatting and here as format cells if I'm going to change it all the way down to custom formula and I'll go ahead and paste my formula right there that I had pick my formatting whatever that's going to be up to you you can obviously change a custom format so I'll go ahead and hit done and that colors ourselves if they're greater than 100 now again this is not tied to this formulas we're just doing this to help us out we don't need it I can just delete that data altogether and now you can see how we get this highlighted in red when something happens in a different column so for example if I go ahead and change this coast to 120 this is automatically red because well the conditional formatting is dynamic so that's the way it works so that's how you can highlight something based on something that happens in a different cell now let's take this this to the next level so that was something their basic with it so now let's move to this expense tab so in this tab we have some expenses and we have some projected amounts for those expenses and then we have some actual amounts for those expenses so now what we're trying to do we're going to highlight a number if our actual is greater than projected so if we spend more than we projected for it we want to highlight that in red so the first thing we're going to do I'm going to just create a formula now we're going to actually highlight the actual numbers if they're greater that's what we're targeting we could highlight really anything we want but right now actual I guess makes the most sense because if the actual is higher we want to see we want to be aware so we're going to start with an equal sign so again we have to create a logical test that will output true or false so how can we do that so if the actual which is my C to cell content if it's greater than B 2 which is this if the actual is greater than projected we would like to know about it so that's my logical test I'm going to eight enter I'm getting a false because it's not greater right so I'm going to double click to send this down so you can see false false false this one the actual is higher then the projected so true true false true true true so now we can use that for conditional formatting now we'll probably not going to do this one although we could have done that one as well so here we are so now we have our logical test again I'm going to go get the first formula when I copy the whole thing including the equal sign escape out of this now I'm going to highlight and by the way the way highlight matters you want to make sure you highlight from because I'm grabbing the first formula I want to make sure I have that from top down and this way the active cell stands on top which is on the same level where my first formula was so great I've highlighted all those actual numbers I'm going to go on their format conditional formatting format cells if we'll switch this to custom formula is and paste our formula right there switch this to a different formatting that seems to be fine so and go ahead and hit done now I don't need this column as usual then just get rid of it now we can see right away if our actual amounts are greater than our projected amount just like that again using a logical test now we could have used the same formula to highlight the X show amount here so if I go under format conditional formatting think I still have it copied so we'll see so I'll go ahead and cause the formula is try to paste my formula okay great so we did have it copied so do this maybe change the formatting to something different I don't know whatever that's going to be it doesn't matter now the formatting class no go ahead and I like that so now we have this also highlighting as our amounts are higher than our projected amount now if it's not higher let's say the maintenance turned out to be 399 see there's no formatting so 400s should still not format because it'd be close to now anything about 400 we'll get our conditional formatting which is because it's higher than projected so we get dead so that's the way you can do this now let's take it to another level I'm going to go to this second one let's try to make it a little more complicated so let's say we want to know every time the actual amount is 5% greater or less than the amount that we project it okay so first of all let's make this dynamic so I'm going to go ahead and type 5% right there on top so this way we'll be able to change this value as we need it let me use this pain format to copy the formatting from here so I'm going to click here actual paintbrush click on this one so that will give you a green color I still have to change this to percentage so I'll go under this number formatting make it a percent formatting so 5 percent great now that's what we're going to use now we want to make sure that we every time it's 5 percent greater this number we want to be aware of it right or 5 percent less for that matter so the first thing we'll have to do is figure out how do we calculate the percent difference between the actual and a projected our formula is going to be equals so I'm going to start with the parenthesis so it's going to be the actual amount minus the projected amount close the parenthesis and whatever I get as a result I want to divide it by our projected amount that's going to be our percent change or percent difference let's put it this way so again the actual minus projected divided by projected hit enter zero percent obviously seven thousand seven thousand there was no difference so now I can go ahead and double click a send this down we can see differences in our percentages so for example we spend negative five point seven one basically five point seven one percent less than our projected amount and in this case we spend 18 percent more than our projected amount and so on right so at this point now the tests that we have in our hand is that this is the percent difference now we want to know when this difference is either higher than five percent or less than five percent then we project it so first of all two make that negative positive thing that we have going on I'm going to first convert all this to absolute value I'm going to do that using the ABS function and put this whole thing inside of the ABS function which will convert all of those two absolute values so send this down there this or negatives will come become positive now great so now that we have this the next thing we want to do is to check if this is greater than this five percent that we've defined or not so this is the entire thing so after the result I'm going to ask is this greater than this five percent because this five percent is on top and I want to be able to copy this formula down without this 5 percent moving down together with my formula because it's stuck on this top cell I want to lock it hitting f4 which will add my dollar sign lakhs for my d1 cell they hit enter it says false it's not greater than five percent difference now this one is greater than four percent this is not this is greater this greater it's not greater this is not and so on and the total is not greater than four five percent difference so now we've made our logical test which gets us our true surfaces now all we have to do as usual just go ahead and copy our entire logical test hit escape right so now I want to highlight the actual numbers if they are greater or less than that percentage amount who have decided so I'm going to do conditional formatting again use a formula cause the formula paste my formula there and let's give it some sort of color I don't know maybe I'll change the text color as well maybe lighter background here we are so I'm going to go ahead and click done that's our conditional formatting now I don't need these so I'm going to clear that because our formula is still going to work now the cool thing about this we tied this now to the cell on top so what that means is that now maybe we decided that we want to get this alert every time something is a more than 10% difference rather than 5% so all I have to do is just switch this to 10% and now dynamically updates so now if the difference is the 10 over 10 percent more or temper temper cent less then it's going to highlight in red so that's what we get I can also switch to 1% where we're going to get more highlights of differences and so on so this way we can use conditional formatting we can also tie our formatting to another cell value that as we're changing that cell value our actual formatting is changing based on that so that's a few ways to use conditional formatting with a formula that should give you at least the basics of how you can accomplish that and that's it for this class