Send Many Formula with airSlate SignNow

Send many formula

okay awesome thank you so much for joining today's loud webinar my name is Ben and I am a customer success manager here at Monday comm and today we're going to talk about how you can use a formula to boost your workflow and to provide you with more insight into the data on your boards understand that the formula column can be quite intimidating for most users and this webinar is going to both show you how to use a formula column which is actually much less scaring than it might seem and most of all help you explore the benefits that you can get from using this column we're going to start quite basic and then expand to many other use cases I understand that and the majority of the people have never played around with formula column 8 so this is a great way to start and it may get a bit complicated and I will be sharing this deck with you as well as the recording so you have nothing to worry about you can look at it again afterwards okay so what are we going to do like I said we're going to start at the basics about the column the logic behind it we're going to discuss the options in using this column as well as the features that are not yet available I'm never going to discuss the different use cases both specifically on tickets comma tickets that we have received along with many tricks that we are going to use to there that can help you boost your performance finally we're going to address some common mistakes once you know what to look out for it'll be able to break down all of the intimidating aspects of this formula and you'll be able to track all this great column on any of your boards finally we going to have a 25 minute last rent I to answer all of your questions so I'll be randomly stopping throughout the presentation to answer the specific questions that you have the reason for this is the formula column can get quite confusing and so we all have a lab question at the end as well as a potential random stop just to explain everything okay awesome so the first thing that I want to do is present our use case for the day your quick introduction into this use case and the board and from me you will see how the formula column helps me enhance the board to a new level I am a huge comics books fan just a little bit of background about myself and so I am exclusively selling superhero t-shirts now each t-shirt has its own specific pricing there are each order comes in through its own specific way now this is the high-level overview of every single order that is coming in over here we have the lower level with every step the styling waiting assembling labeling etc etc all the way until delivery over here we have the costs this is how much it's costing me to keep Joey Joey as an employee it's been working in this time tracking column and this is an awesome feature only in the pro plans I would recommend paying around is that if you haven't yet also and how we derive a lot of information from all over this and the next so the next thing that I want to show you is quickly what this formula column looks like so this is what the column looks like as you can see on the left hand side we have all of the columns as you can see how formulas are included we have you can add the drop down column a date column due name phone number email address and notes so you can have a lot of different options of columns with in this formula column and there are limitations and we're going to discuss that at a later point for example timeline is not a column that is available from me I'm going to close the columns and I'm going to go into the functions these are all of the possibilities that we can have within the formula column now I'm going to go how about over something random like - and as you can see on the right we have a summary of the formula and an example the example is extremely important because this shows you exactly how they expect you to build this formula so it were see you so what you do is you would add in the - you open the bracket the five you break it up by a comma by a comma and then you adds a three and you close it don't worry I will be explaining this into more depth but this is the general overlook of what a formula column looks like okay great now the first thing that I want to do is figure out the types of cost calculation where would that be important well let's go back to the specific use case Captain America every single time someone orders Captain America they an automatic situation comes up which tells me it's 30 dollars why is that important because each one of these options I have a different cost so Captain America is going to cost me 30 Iron Man is going to cost me 20 and throw is going to cost me 15 and then I knew times all of these but amount of shirts that they want so how do we go about this well there's a few ways to do this like all populations and like all calculations there are many different ways to go about it so these are just the different examples I can show you that flexibility so the first thing would be the typical way to do it you have the variable one you have variable two and you put a star into signal and multiply now I just want to have a quick disclaimer of it yeah I'm sorry freaking as you can probably tell by my accent and so back home we refers to this as a bracket this as a bracket and more specifically this is a curly bracket I understand that some of you use other other words I don't want to confuse myself and therefore confuse you so I'm going to beg you to bear with me in this okay great so the first way is we can have variable one variable to end times it over there and and this will give us the exact result that we're looking for but many people do not know that star is a representative for tags or a dash is a representative for - and so for that very reason we have these formulas so I'm going to go and I am gonna take down the column I'm then going to find the exact thing that I'm looking for so right now I'm looking to multiply so I find a new multiplying as you can see as I click on it it automatically populates with an open and a closed bracket over here once I do that I then go and look at what's example is telling me to do it's telling me to add a variable I'm in variable 2 and break it up with a comma so I go to about columns and I find the variable that I'm interested in so the first is cost is shared quantity I'll break it up with a comma and then I do cost per product and I set it and as you can see all of these are automatically populated elizabeth is asking how you can turn subtitles on I highly apologize for my accent as far as I know there are no subtitles but bear with me and I will speak slowly sorry about that ok so this is how we would automatically populate this over young now that is great and that is very nice and that gives us answer over here but we don't want to go ahead and start putting in the cost per product every single time that we need to all right we want this to be done automatically what I mean by that I want to say that if the user picked Captain America is the customer pick Captain America I wanted to automatically populate as 30 if they pick a man I wanted to be automatically populated as 20 and if they pick store I wanted to be automatically populated in 15 now I'm just going to stop over here quickly again the formula Aaron is asking if the formula total is in dollars as you can see the total income here is in dollars and here it has not been specified as dollars you don't want to add it in Ovation I'll explain why I let a point you simply need to click on the Sun button and I can pick dollars as my unit and as you can see it automatically populates so yes that is how you go ahead and do that okay great so what I am trying to do is I'm trying to get this to automatically populate I can do this through an if statement so what the first thing that I need to do is I need to define my variables so the first thing number this is being created through a drop-down column and the first variable the condition that I needed to find is if it equals so then it's gonna cost 15 if it equal if the drop down equals IMM then it's gonna cost 20 and if the drop down equals Captain America then it's going to cost me 30 now I can go ahead and start this formula by simply finding the if statement that is discussed and as you can see in open and close brackets comes in automatically and from there we build us this calculation now I understand that this looks quite intimidating once we start breaking it down you're going to see that it is not nearly as intimidating as you think and let's get started so the first thing that I do is the automatic if statement has been put in and an open and a closed bracket as been put in from there I picked the drop-down column that I am interested in I add an equal sign and I write so now as you can see I've put quotation marks over storm I then say and I will address why I did that in a second I then say it's the drop down column equals storm then I want you to generate the number 15 for me or if an equals Ironman then I want you to generate 20 for me and if it equals Captain America then I want you to generate it if it equals none of those then I want you to give me is ever now a common mistake that we often see throughout the tickets that we have received is whether or not you should put a be the quotation mark over here or over here well let me simplify it quotation marks are exclusively used for Strings strings are basically anything that has more than just a number so as you can see store overhead is not just numbers and therefore I'm adding the quotation marks the first thing that I want to generate is not $15 but just for skin and that is why I don't if I wanted to be $15 then I will be adding this and then I would be adding the quotation marks but please note if you do that then the system is no longer able to use that 15 as part of the calculation okay great now another thing that I want to highlight right here is to always make sure that for every Open bracket that we have we have a closed bracket so yeah this seems pretty simple we have 1 2 3 open open brackets we have 3 closed ones yeah we seem to be good right now I understand that this doesn't look too intimidating right now you're going to see in the future why we need to do that the next thing that people want to know is if it's possible to combine a calculation and an ester so where would that be important well for example let's go back to the original situation of Asia and now I want to figure out if any given order goes over and above the break-even point or if it is under so what is a break-even point it is basically anything that goes above the fixed cost so a fixed cost would be lets say I need to employs fourteen people to come into my t-shirt manufacturing company and start working now if they are making 100 t-shirts a day or if they are making three t-shirts a day they are still going to be charged they still going to have to earn the same amount and so I need you to see if any given order goes over and above that or not so I want to see if this order is bigger than this so I'm going to go back here so as you can see I've used this with a new statement if the shirt quantity times the cost per product is bigger than the fixed cost which is a column so this is drawing information from the column then I want you to say break even if it's not then not there yet now why did I put a comma of a closed bracket over yeah well in order to let you know that I first need to explain the point of the brackets the records are used to provide clarity in the order of operations the order in which several operations should be done in a mathematical expression so what am I saying I'm saying that first I need u times shared quantity and cost per product and once you've generated that that's what I want you to see if it is bigger than the fixed cost or if it is smaller so effectively we can be looking at it like this if X is bigger than fixed cost and break even if not then not there yet so so these can also be used in order to help you simplify the information this can look quite intimidating it doesn't have to be once you start breaking things down I just want to stop for a second and go into the questions see if there is anything pertaining to the specifics formula that I just wrote we have some compliments on my English which is flattering but not what we're looking for right now okay it looks like the question don't have exact reference to this I will be addressing them all at a later point calculating the billable hours so this is also a common question that we get from a lot of our customers now it seems relatively simple in order to calculate but people do run into a few issues and so I want to address them head-on Roger so what do I mean by that well we have three employees over here Joey Monica and Rachel we have their managers but we're not going to focus on that each one of them comes into work every day and starts their clock and says how long they are working now also according to their negotiation skills they have different amounts that they are earning per hour and we want to go ahead and we want to figure out how much they are actually earning and how much we actually need to pay them at the end of the day so what is the formula that we use to do this this is what it looks like again it looks intimidating it's really quite simple why do we have round written here why are we randomly divided by 3600 and why is there one at the end let me explain the time tracking column generates the information in seconds so even though we can see this chart in hours when we use it in a formula it is actually presenting the number that we have in seconds so we divide by the amount of seconds they are in an hour in order to give us the result that we want once we have done that so once you have closed this bracket and generated the result there within times it by price per hour and we get the answer that we watch pretty simple but why is there the rounded well the round you can basically think of as a beautician it comes here just to make things pretty and make things look better the round tells me how many how much my answer is going to be to what decimal point so because I've written one here it's going to be to one decimal point if I change this to two it's going to be two decimal points and if I change it to 3 will be 3 what run all right but if I go ahead into this call exactly the one that I explained to you earlier and I changed this number right at you to look over here you can see that it changes to three hundred and sixty point three five instead of four now if I change it to two three well this stays the same but this comes to three variables so because I'm not interested in all of those decimals I'm keeping it through round two one so all that you we need to do is thumbs column one by column two we remember to divide by 3600 in order to make everything look neat and professional we use the route okay now many people want to know that how can one calculate the remaining budget of the die so what do we mean by that once you've worked out that Joey over here is earning three hundred and sixty dollars we've only budgeted four hundred dollars so we now need to work out what the difference is how much more Joey can work before we say to him that somebody I think it's time that you take a break so how do we do that well this is the calculation that we need the first thing as you can see that is this is the original calculation that we used so I'm sure you all understand it the time time-tracking divided by 3600 times price per hour and then reminds all of that by the total budget but this is also a perfect example for me to explain to you and the use of brackets the brackets gives us those stages so the first thing that we need to do is work out what this answer is so let's say this answer is 360 once we've done that then we can - it by the total budget and only then do we round it to the right decimal point so the brackets are there to help you with the stages now this is also a great opportunity to show you why the brackets can be confusing the foolproof trick that I do is I simply count now we understand before I show you what I mean by that we understand the rationale of why the brackets go where now I want to show you how to make sure that we have the right ones and I simply count them out of open brackets to close so we have one two three four open brackets and we do the other way around where how many clothes do we have one two three or that is that where four and four seems like we are on the right track so this is a great trick that I use that helps me a fortune most people think I am a genius when I fix a formula when someone sends in a question really I'm just adding in a bracket somewhere so beyond the for that okay great the next thing that people always want to know is if it's possible to calculate the J's since the doctor or basically play around with things this is actually a question that I see in the question section now so I'm glad that I could address it what do I mean by that well basically this lets say an order comes in on the 21st of August and I have promised my customer that it will be at the door by this by 30 working days right so how do I go ahead and work that out to see if I am over or if I'm about or really just how many days that is well this is the formula for that now we have days right now what is days days is a function that we have created and I'm going to go ahead and find that to show you and what the days does is it retains the number of days between the two dates now this is a perfect example to show you why the example is so important what it does is it tells me he had the right days with an open and closed bracket and I told me to write the end date column first name first break it up with a with a comma and then the start date and that will give me the difference between these days okay great so now we understand in theory that I need to go with the latest date - that which the days already does by the order date but the question is is what is today well today is another function that we have created and I'm going to go and find it for you over here and what it does is it generates the day of well what whatever the day is and now this is updated daily she's our add-in today it's going to be the 25th of September tomorrow it's automatically going to change to the 26th and what it does is because this is a function that has been created it automatically generates an open and a closed bracket do not worry about that leave that blank ignore that it can just be like that so I'm effectively do is simply saying today from the order date how many days are there in between but if I'm going to be 100% honest with you which most the time is the right thing to do this isn't exactly what the users asking for this isn't what we want because we want to know how many business days it is since the order because we know is in these 35 days within these seven days there's been X amount of weekends and we promised her to be in X amount of business days so how do we do that how do we calculate the number of days between a start and end date excluding the weekend well this is how we do it again you can start by ignoring the round it is there to make things pretty and as you can see this is the original sum that I created so now we know the total and we know that the total is 35 days now what have we done in this bracket over here well the first thing is we found the week now week number is exactly like today or days it is a function that we are created and what it does is return to the early week number of a given date so what does that mean let's say the given date that we're talking about is the first of December that is the 48th week of the year and so 48 would be what is generated so what I'm going to do is I'm saying generate for me the week of today so let's say that is 42 right so we can rule effectively right 42 over here okay and then we want the week number of the order date so that can effectively be let's say it is 38 and now we say 42-38 that gives us 4 and what we're saying is times this by two because there are two days in every single two weekend days in every single week and now we have an answer of eight once we have an answer of eight remind us that by the original sticky and we get the answer that we were looking for so that is this over here 25.5 again I rounded it to one decimal place and as you can see this is the order of Asia now I understand that this can be quite intimidating and so I want to go over this again what did I do I took original the original formula which is the total number of days in between all of this I then - stood by the week number of today - the week number of the order date times - the amount of weekend days within that giving time frame so it's in four weeks where - in each day that is a - a - a by the total number of them ok great now the last formula that I want to go ahead and show you before I start talking about common mistakes and limitations is a status based on multiple conditions so what do I mean by that I have this order over ya and I want to know if it is over X amount of days not including the weekends including the weekends whatever your formula - signs as well as the fact that it is not being delivered then I want you to let me know that we are over time and if we are not over time then well done so how do we go ahead and do that well the formula basically looks like this it's the simple if statement so just for right now get rid of the end and you can see that if something equals 2x then over time if it doesn't equal to X then well done but we've added another function here called end and what N does is it makes sure that both variables are true if both variables are true is basically what we're saying then give me overtime if not give me well that so variable one is if days since order is over 4 is bigger than 4 and you the camera vision the delivery status is equal to waiting then we are clearly over time if the Dyson's order is 15 but the order status is completed or central you love it or whatever I wrote and decided then give me well done because it doesn't make a difference of over the date this has already been completed and vice versa if it's still waiting but we're under the days and we're not over time we still have time in order to do so I hope that makes sense now I want to discuss the common mistakes and this is simply to prevent you from getting overwhelmed once you understand these mistakes you will be able to address it and you won't have new and be intimidated by this column anymore so first of all this is case sensitive so I'm going to find a formula and exchange what I mean I addressed this earlier but I want to address it again everything in this column is case sensitive what we are doing over here is we are telling the this system to look for a drop down look for the the drop down cold drop down but if I make it a lowercase T it is going to be looking for this drop down perpetually and it's never going to find it because it is case sensitive so you know copy or paste or make sure that you are spelling it correctly because this is a common mistake that we see the same thing happens over here what we are doing is we are telling it to look into this drop down and generate the answer over here but if it cannot find Iron Man with a capital I or a lowercase I or whatever the situation is it's not going to find it it's not going to generate 20 which means I'm not going to get the total income of Asia etc etc so please be on the look out for case sensitive the second thing is commas we didn't actually get into this as much as I probably should have highlighted to come there to break things up so if the drop down equals Thor then give me 15 or if the drop down equals Iron Man then give me 20 it is quite often that we see something like this because the customer the user is unaware that you need to start breaking it up and this system doesn't recognize what you are then asking from so pay attention to the colors they are quite important but also quite intuitive so you can understand we also have brackets now brackets are super important and I went over this a larger is a perfect example of how you can use it we have two closed brackets over here here always in this large one over here so basically this can help you both understand the the part and what you need to do as well as help the system so the first thing we're going to calculate is the time divided by 3600 then the that answer times four hours about price per hour and then only then are we going to - that by the total budget so this just breaks the stages happy and gives you a better understanding into into what you are asking the system to do the last thing is quotation marks and I'm going to go back to the if statement that I discussed earlier and these these quotation marks are especially important for strings for something that needs to be generated that isn't just numbers like I said earlier if this was going to be a $15.00 then because it is something other than just numbers it would have to have the quotation marks if it was but if this happens this means that you're not going to be able to use it in the calculation so what I would recommend is just generate the 15 Oh Misha without adding in the dollar sign and then add it in manually like this at the end you can even type your own so that I was talking about hours over here and as you can see automatically populates I'm going to go back to others just so people don't get confused again with the South African accent okay great these are the common mistakes that we often see and so I would keep that in mind when you start exploring this awesome con the next thing is the limitations that we have when you start playing around play around with the type of columns that we have over here as you can see we have a vast majority of the columns but things like Tomlin for example aren't one of the columns that we can use turn on specifically is because it's confusing for the system it doesn't understand if we format the start date end date the days in between and so that isn't one of the possibilities the next is cross item and column summary so what do I mean by that well basically the formula works across an item meaning that I can say 14 times by 30 equals 420 what I cannot do is say 14 times by 20 equals 45 which obviously it does not I can not say 14 times 5 equals 3 I can do it across that item only another thing that I can do now I can't do it sorry is use this this footnote at the bottom the footnote cell cannot be used into the formula yet this is something that has been requested a lot and I am a huge fan of this for example the ability to be able to work at the percentage that this order is in all of them on unfortunately this is not yet a possibility so just keep this in mind that you can do it across the item loss we cannot change the status column my absolute dream would be to say that if revenue is over a hundred K for example make up an enterprise account and then change the status unfortunately this is also not possible so keep that in mind okay great now before we go into the Q&A I want to show you our formula column sorry our article now this is a great place where you can start looking at the different formulas and they give you when you would use it they explain how it would come across how you need to build it so once you're getting started I really recommend that you come into this article and start looking at the different possibilities you can always send a message to support Amanda comm or give us a call we're here 24/7 and we can help you understand why the common why the formula column isn't working where you got stuck where you're not understanding yet and I'm really just appreciate you guys spending the time with me please ask all of the questions that you have in the Q & A so it doesn't get lost in the chat and I'm going to address all of them that I can and you can also ask questions regarding just my general workflow for example I want to show you something quickly how we can get the users to automatically the customers to automatically for listening well we can do this through the form the form is basically a way to take every single one of your columns and embed it on to a general website so when people fill in all of these variables and so they fill in the fact that they once thought himself it will automatically be generated over here we can also make it automatically generate though the order origin as a website and so immediately without medium to put in any information all of this can already people I read hope that this has showed you how awesome this formula column is it is probably my favorite column on this platform and I hope you guys think so too okay great we are coming to the end of this presentation I really really appreciate the fact that you all take your time to to to learn about the formula column I'm personally a big fan and I hope you guys are also now and I'm sorry I didn't get to answer everyone's questions but if you have any more questions you can send a message to support also I would really recommend for everyone that isn't on the program to play around with this formulas try it out for two weeks for free and also play around with the time tracking this is also on the pro plan and I think it is a really cool feature that helps many many many many of our customers and have a great day