Data Analytics using Excel

hello everyone good evening welcome back to the session I hope I'm audible on the screen visible to all of you am I audible on the screen visible to all of you could you give me a confirmation please before we begin yes thank you so much for the confirmation right so second day of our training data analytics using Microsoft Excel yesterday we had understood what is data analytics we had spoken about what is Microsoft Excel how to install it I had shared the links using which you can install the tool on your machine right if you do not want to install Office 365 you can go ahead and install and install even the other alternative tool that we have which is WPS office anything is fine you can use one of it to practice the concepts now um what exactly we did yesterday was we just got familiar with the tool with the nomenclature Etc right we just um I introduced you to the concept that we have a home page where um we can choose the blank worksheet to start off with our work this is the Excel spreadsheet Excel is basically a spreadsheet application where you will see a lot of columns a b c d e these are columns and these are rows one two three four five Etc are the rows and every cell here each block that you see is a cell and each cell has its own address so this particular Block's address would be G seven why because it is in column G and the row seven so cell is basically an intersection of the row and the column okay then we talked about the big five right those five basic functions which everybody is supposed to be familiar with what what are they one is sum the other is average the Min function the max function and the count function okay so this is what we had done yesterday we will um do a quick recap on these things and then we shall proceed okay all right so here I have some data with me and this is uh the employee details okay of 360 digit mg now we have some questions that we are supposed to answer so we have the employee ID name of the employee salary sales amount so let's say this is the sales person and the sales that they made now we need to get the total salary so what does it mean I need to get the summation of the data in the salary column so as we learned this yesterday it's pretty simple and straightforward we can use the sum function of the entire data present in this range so the range begins at C5 it ends at C10 so the complete range you can see there this is the total sum of salary now if we have to find out the average of the salary on an average what is the salaries that we are giving to our employees average of the daytime this particular range will give us the value what is the highest salary being offered we will use the max function maximum of the data in this range and we need to find out what is the minimum salary so that would be the min minimum of the data in this particular range so maximum is 2000 minimum is 1600 1500 summation is ten thousand two hundred and the average is seventeen hundred now we need to find out the count so that will be the count function okay count of the number of records that are there in that range okay this is the range that we are looking at total six so far it is all good now we have been asked to find out the commission the commission rate is two percent of sales whatever is the sales that the sales person makes They will receive two percent commission on that okay so how do we do this we can use simply multiply okay use the operator the multiplication sign so equal to whatever is the data that is present in the sales amount multiplied by two percent as simple as that multiplication whatever is the sales that they have made multiplied by two percent would be the commission that they will receive that the person would receive so two percent of the sales will be given as the commission which is 50 here now I we need not type in that formula every time okay here also I have to come compute two percent of the sales amount here also I need to show two percent of this amount so in that case we don't have to retype this formula this is the formula right which we can see on the formula bar we don't have to retype then what to do simply I will go to this corner here this corner you can see like a square dot right over there and when I double click on it it will copy the formula down okay you can either go there and hold the handle and drag it down so the same formula will get applied to all those records or we can simply double click on this handle and the formula will get copied down okay now if you see this is D2 multiplied by two percent right this would be D6 multiplied by two percent look at the formula there D6 multiplied by two percent why so that is because Excel automatically performs something called as relative referencing okay so there's a new term that we are looking at here New Concept relative referencing so the moment I double click here it copied down the formula but it is not doing D5 multiplied by two percent everywhere it has automatically shifted the reference to this cell look at that D6 multiplied by two percent what about this it would be D9 multiplied by two percent so why is the reference to the cell getting updated automatically as we move down the row number is getting updated automatically has been moved down the cell reference that happens by default in Tableau is called relative referencing hence it worked okay so here what happened is the two percent of commission that we are talking about has been hard coded in the formula I have hard coded two percent so if someone has to change that let's say tomorrow we decide to give three percent as the commission then we'll have to go and edit it over here so rather than doing like that we can actually store the commission percentage as a separate value so let's say I would like to store it as a separate value over here in H5 okay so it is for now three percent or let's keep it as two percent initially I want it to be two percent this is the commission value two percent all right so I need to refer to this rather than hard coding two percent whatever is the percentage that will be given by the users over here I will have to refer to this and then accordingly compute the percentage so what will I do is rather than doing a multiplication with two percent I will multiply the sales amount with the value that is present in this particular cell okay we are removing the hard coding part and we are making it reference another value present in another cell and multiply it with that so the sales amount in D5 will be multiplied with the commission rate which is given in H5 and the product would be the commission amount which will come in this particular cell where I've typed in the formula okay I hope you all got this so now when I click on the enter key you will see how it has computed 50 percent and if I copy it down how do I copy it down by doing a double click on this handle the rest of them are all giving zero why are they all giving 0 let's see let's see what Tableau did here what did it do what is the formula it has used here it is doing relative referencing so when we copy the formula down when we move down to the sixth row where the sales amount is 3000 D6 even the commission rate has relative referencing by default right so this also has moved down now it is referencing H6 and we do not have any data in H6 what about this let's see here d8 and its reference referencing h8 okay which is not correct there is no value given in h8 and therefore it is zero the commission itself has become zero similarly if I check here what happened d10 so I'm very happy that it is picking up d10 that is what we wanted right we have to compute the commission given to this particular salary so d10 is perfectly fine but the rate the percentage commission rate is present in h 5 not in h10 over here relative reference is fine with respect to sales amount it must perform relative referencing which Excel does by default but when we are talking about the commission rate it can't be it is not supposed to be relative referencing it has to refer to one particular cell at any given point of time it must always refer to this particular cell and not move it is not relative to something it is an absolute reference that we are supposed to make okay so relative referencing might not help all the time sometimes we use something called as absolute referencing okay now what is absolute referencing I hope you'll understood relative referencing how the reference to the cell will keep changing depending on which row and which column we are in whereas absolute referencing is used when you have to lock in a cell at any given point of time if I have to refer to that particular cell no matter what I have to lock in that cell then we use something called as absolute referencing so how do we do that H5 is supposed to be made absolute I need to lock that value in place so while the mouse pointer is here on H5 you can see that it is on h5it blinking near H5 I will just press down the F4 function key okay f for function key so when we press down the F4 function key what happens is you can notice how before the column reference there is a dollar sign and before the row reference also there is a dollar sign that we can see means this is referring to this particular cell at any given point of time okay we have logged it in place this has been locked in place the reference has been made absolute now over here while this is selected if I go ahead and press the function key again you can see the column reference is relative but the row reference is logged only the row has been logged dollar sign before the row number again if I do F4 the row is relative but the column is locked as H you can see the dollar sign before H and if I do F4 again it just goes back to H5 which will become relative referencing now again I will go and hit F4 this is how you lock it in place okay so here we need the com the cell itself to be absolute both column and row are locked referencing to a particular cell so this is going to help me and this is called absolute referencing okay uh when you have logged only column or if you have logged only row then that is referred to as mixed referencing okay where we will keep either the row absolute and the column would become relative or we keep the column absolute and the row will be relative okay if both are absolute we are locking a cell in place that is absolute referencing if both of them are going to be changing that is relative referencing if one of it is constant the other would change that is mixed referencing so we will be seeing examples on mixed referencing further into the training but today we are focusing on relative and absolute so relative I hope is clear now let's see how absolute is going to work as you can notice that we have logged the commission rate by using dollar Edge dollar five this now if I copy down that formula by doing a double click on this handle if I copy it down you can see now look at the formula corresponding to it D6 multiplied by commission rate H5 if I come here D9 okay the sales amount is relative referencing so that is changing whereas the commission rate has been fixed to be absolute referencing okay so that's the difference between relative and absolute referencing which is a very very important concept uh in Excel now what we shall do is compute the total salary so what would be the total salary of the employees the actual salary plus the commission they that they receive the commission that they received because of their performance so total salary is equal to directly we can add because it's just two numbers we'll add directly this Plus the commission okay so it is C5 plus E5 for this particular employee Veronica what about the remaining employees it has to change instead of again writing C6 plus E6 C7 plus E7 what will we do we will simply double click here and the autofill feature you know magically it just fills in everything why because it is relative referencing okay so 1800 plus 60 1860 what is the formula that Excel has used here C6 plus E6 this is 1500 plus 44 1544 what's the formula used C7 plus E7 so on and so forth okay so yeah so that is about the functions that we learned yesterday a recap of it as well as a New Concept which we understood just now that is the difference between relative referencing and absolute referencing each of them have their own benefits okay so with this we will move on to the next section which is understanding the if statements okay if it comes under logical statements it is a logical statement it basically tests a condition and when the condition is true it will return certain things certain value and when the condition is false it will return some other value so when we simply type in if and we open the bracket look at the arguments that it is expecting look at the syntax so The Logical test okay first argument that we have to give is The Logical test and depending on that test if it is true if the test is true then what value has to be used or what has to be done would be the second argument and in case the condition or the test that we perform here fails then what is supposed to be done if it is false then what is it that we are supposed to do okay so it is a logical statement now there are many uh other variants in if we have SIMPLE if statement we can use nested ifs we can use something called as IFS now if you would like to check whether on the version of excel that you are using whether it supports ifs ifs is a relatively new function so though if any of you is using very old versions of excel like 2013 or 2010 2007 something like that you might not find it anyway a quick way in which you can check whether or not this feature is available on the version of excel that you are using is you could simply go to the formulas tool menu okay over there and under logical it is a logical expression so just go to logical and you can check from here I have IFS okay you can check from here whether or not you people have ifs if it is there you can use it if it is not there also no problem you can continue using initiatives okay so what is it it is a logical statement which tests a certain condition if the condition is true it will do something if the condition is false it will do something else we can specify both what has to be done when the condition is met and what has to be done when the condition is not met so here today we are going to explore all these logical Expressions which are available now here is a data source where we have names of employees and their salary as well as their job rating so be let's say this is based on the earth annual performance Whenever there is annual compensation review that happens depending on the performance of the employees the organization would give them ratings let's say 5 means it is the best okay best performance and one means that employee has not performed well at all the least performing employee is given one okay how are we how are we rating then 5 means the best performance and one is the lowest performance the least performance from an employ from an employee okay salary is numerical yes salary is basically currency okay salary is currency so it has been formatted as you can notice if you go back to the home ribbon right this is salary and it has been customized but even if I just leave it as currency it will work but my system Again by default it is India so it's showing the Rupee symbol I formatted it to be compatible with us the currency symbol I will take is US dollar okay so this is numerical now you can notice that we have some numbers decimal places are here to remove the decimal places we can simply click on this icon which will reduce the decimal places okay this is numerical okay this this we can say that it is a number it contains numerical data again we don't need to give any decimals here this is the accounting or the currency value and this is General General meaning text okay now we are supposed to give bonus to the employees I said rating 5 means best and and the worst performance or the least performance will given a rating of one okay now how do we give the bonus depending on the salary that they have made I am supposed to compute the bonus but it should be depending on the salary made by the employees and sorry not salary made I have to give them a bonus depending on their job rating okay the depending on the job rating and the final take-home salary would be the salary plus whatever bonus they receive together will be their final so we can do it based on if condition I will keep showing you step by step how to use if here if sorry equal to if the moment I selected you can see the function has come and what is The Logical test it's asking me what is The Logical test I would like to check the value in the job rating column this one for each person the rating is given so for first employee it is C2 if it is equal to 5. okay if the job rating which is in cell C2 is equal to 5 no this is my logical test I want to check whether it is equal to 5 and if it is true what to do let's give a bonus of 5000. and if it is false zero meaning what what is the statement going to do only when the job rating is 5 only to those employees who obtained a job rating of 5 we are giving them a bonus of five thousand everybody else is going to receive zero no bonus now look at this there is no value here why because this over here this employee's job rating is 1. but I need to do it for all the employees right so what to do simply I will double click on the handle here and it will copy the formula down so only where the job rating is 5 bonus has been given to the employee rest of them are not given any bonus at all that is what it is showing us okay now let's say uh this is not very fair right only if they get job rating five they get bonus otherwise no bonus it's not doesn't seem very fair so what I will do is let us give everybody okay let us give everybody um some bonus or the other now how do we how to do that we can use something called as nested if it is basically multiple if statements if inside an F okay if inside and if nested if so what will happen how do we write that if C2 equal to 5 then I'll give 5000 that is pretty much Clear which is my true condition but under the false condition we will write one more if statement okay because there is another condition to test I will again say if Again The Logical test what is The Logical test if the value here is equal to 4 then let's give a bonus of three thousand okay comma what if that is also false what if the job rating is not 5 and not 4 then another if I'm going to check if the job rating that is there in this column is equal to 3. then let's give them a bonus of fifteen hundred that is if it is equal to c 3 then what we'll do we'll give them a bonus of 1500 comma what about the false condition another if if C2 so I'll just copy this I'll click here okay if C2 is equal to 2 okay if it is equal to 2 then I would like to give a bonus of a thousand rupees comma what if that is also false we'll just make it 0. means what if they are getting a job rating of 1 then we are going to give 0 as the bonus and then one thing that we have to remember here is we have to close as many brackets as we open it is mandatory to close as many brackets as we open so here I have opened one two three four brackets so I'll have to close four now when I hit enter what happened the formula got applied what is the job rating of this person it is one therefore that person did not receive any bonus we are not giving any bonus for one right it is zero now if I just copy down this formula you can notice how it has populated the values so wherever Whoever has a job rating of 4 okay Whoever has a job rating of 4 we said we will give a bonus of 3000 which happened here Whoever has a job rating of five the bonus is 5000 which is our first condition three we're getting fifteen hundred and G operating two they're getting a thousand okay over here and wherever job rating is one the last condition zero okay this is nested if statements how we can test multiple conditions using the nested if statements however this can be a little cumbersome and here I had just four conditions to check for imagine if you had a lot of such conditions then it would get pretty complicated it would get a little cumbersome so what other option do we have in the latest versions of excel there is another function called as IFS okay ifs is a slightly more let's say refined way of testing multiple conditions okay it is a more concise way of testing multiple conditions but as I told you this feature may not be there in very old versions like 2010 and 2007 and all you have to check how will you check whether or not it's available as I told you you can either go to formulas and check under the logical you know section whether or not it is present the other option is just type it equal to if and just check whether or not it's there if you find it in the list it means on the version of excel that you are using this function is available if you do not find it it means it's not available if it's not available then you have to go ahead and keep using the nested if there is no other work around there um so now there is one more thing that I would like to change here in this formula if you notice the values be it 5000 or beat 3000 beat 1500 beat 1000 they are all hard coded isn't it there's a lot of hard coding that has happened over here now rather than hard coding we will put these uh bonus values in a separate table okay and reference it so that we can avoid the hard coding because if I have to change the bonus let's say when they are getting uh when the job rating is 5 the bonus has to be increased to ten thousand then at every point I'll have to go and change it or I'll have to change it here right so instead what I will do is here I'll bring it here so there let's say I have one table where the performance rating and what should be the bonus given to the employees based on their performance is stored separately yeah so that is exactly what we are doing now here priyadarshini right uh yeah that is exactly what we are doing here so we're going to create another table as you can see where we have the performance rating and we will refer to these values in our formula rather than hard coding them how did I bring space between dollar and the numerical the numericals that is the default okay it happens by default the moment you use the currency as your data type currency and if you use um us currency it hum it happens automatically if you use Indian currency then you can see the Rupee symbol is close to the number right but if it is us then there will be a space between the dollar sign and the value yeah okay so I'm going to change my formula now I don't I'll just delete this whole thing okay this is not what I'm going to do no hard coding anymore nestatives itself But Here by referencing to another column okay so equal to if if the value in this particular column is equal to 5 that's my condition I want to check whether it is 5 if it is 5 if it is true then I need to give a bonus which is here okay now if it is false then what to do and what about this should this be relative referencing or should it be absolute referencing if I leave it as G3 it becomes relative referencing and next time when I move on to the next row if I copy the formula down when it's referencing the second row this will also move down so as this reference moves down this reference also would move down and we might get incorrect output so for now I will leave it as relative referencing okay let's leave it as G3 only a comma and what about if it is false we have another condition to test we have another condition to test where we will check whether C2 is equal to 4 if yes then I am going to reference this cell to uh to obtain the bonus and again another condition to check if the value in C2 is equal to 3 okay then I'm going to reference this cell for getting the bonus again if the value in in this cell is equal to 2 then I'm going to reference to this particular cell to obtain the bonus okay and here [Music] um I will leave it as 0 for everything else for everything else let's reference this set okay for everything else means for one if it's not five not four not three not two so one we're referencing this cell and remember to close as many brackets that have been opened four brackets have been opened so four of them will get closed enter this person is job role uh job reading one so obviously the bonus is not there now if I copy this formula down it will work to some extent but it does not work beyond that right it went wrong why did it go wrong because look at the formula now look at the formula that Tableau has used for this cell it is referencing G4 G5 G6 G7 what about the previous case we started the referencing from G3 right we were referencing G3 for uh job role equal to for performance rating five we referenced G3 but when I moved to the next it is referencing G4 and when I further move down when I come here it's referencing G9 we don't have data there it's referencing these five cells in the formula so because of relative referencing which is the default referencing okay we uh this problem has happened so what are we supposed to do I am supposed to use absolute referencing whenever the performance rating is five I should always refer to this one particular cell I need to lock that cell in place okay I need to log that cell in place so I will go to G3 when c 2 equal to 5 if job rating in the job rating column is 5 then I should always reference to this particular cell lock it in place how just F4 key so I will go there and press down F4 function key I am locking the performance the bonus sells in place okay I'm going to block that also and I'm going to do absolute referencing for this also and lock it in place okay absolute reference so I hope you all understood the difference between relative reference and absolute reference now it will work now if I copy down the formula it would work why because no matter where I go to which row I go I have logged the performance rating reference the bonus reference has been logged to those particular cells all right so we have also seen relative and absolute referencing with another example over here and here's the formula in case you want to just have a look at it so we have been looking at simple if and now we looked at nested if statement but as you can see nested if is where you give if Insider if Multiplex which can get a little cumbersome now we wanted to avoid that right we said what is an alternative way of writing this in a slightly more concise manner that is where ifs will come into picture we will look at ifs now okay now we're going to look at iOS how do I write ifs I already have this but how do I write IFS equal to IFS so ifs if you can simply call it ifs also then look at the syntax what are the arguments it's expecting of course we have to give the logical test right logical test one and value if true one so logical test one is C2 equal to 5 then I have to reference this cell and I am going to use absolute referencing okay comma look at that now after comma look at what is it asking not value is false with if the next argument was what should happen if the value is false but here it's asking for logical test too so directly I can type The Logical test to what is my logical test to this oh sorry not this note this equal to 4 then then what to do if it is equal to 4 I need to give so and so as the bonus and I'm going to use absolute referencing for it comma then it's asking logical test 3 what is logical test D I have to check whether the job rating is equal to 3 and if that is true what should I do I need to give this as the bonus and it should be made an absolute reference comma next logical test logical test number four logical test 4 is is this value equal to 2 if true if true then I'll reference this and make it an absolute reference then what to do how do we handle for one okay how do we handle for one what I will do here is not handling for now let me just close it okay let me just close it and I have an error for one because I have not applied anything for one I'll copy down the formula rest of the places it's working fine okay the rest of the places it is working fine okay but wherever I had rating of 1 for which I did not handle in my formula it's not able to do anything it's saying you have not told me what to do and it's just giving me an error what is the error that I'm getting here not applicable or not available any any means something that is not available okay now how do we handle this there is one very interesting way of handling it in this particular scenario for one I have to do something so how I can give it is I will give an argument through comma 0. Now how does this work how does this work what is true that I have given at the end of that if ifs if statement and how does it work let's apply it first you see Zero it's not any any mode it is 0 and if I copy down this formula you see zeros I have handled the any error how did I handle and what is happening over here so this is the test right is is C2 equal to 5. if it is equal to 5 the condition would become true otherwise the condition is false if I double click on this okay this particular one let me uh just calculate it you can either hold on F9 function key it is showing us what the value of the test condition or we could simply click on this calculate now so is this condition true or not C2 equal to 5 is it true or not let us calculate it is a false condition okay C2 equal to 4 is that true let us calculate that is also false so basically the these tests are giving us true or false right C2 equal to 3 Let's see what is that going to result in false because this is one right it is false C2 equal to 2 what is it going to return let's calculate false it is also giving me false so how does this formula essentially work how does this formula the ifs formula work is whenever the first true condition is met whatever has to be done based on the whenever true condition is met it will um you know go ahead and do whatever is supposed to be done for True condition and that's it it ends over there all right so only when this first condition is false it goes to the next condition and it will check this is also false then it checks for the third condition if this is also false it goes to the fourth condition so it will keep checking the conditions the tests that we have written these are called logical tests right it will keep on checking the logical test until it encounters true okay the moment it encounter is true which is the test condition if the test condition becomes true then it will return so and so value so when all of these conditions are false I am saying by default it is true rather than writing C2 is equal to 1 which would have become true right if I had written C2 equal to 1 it would have become true and then it would have given 0 but instead of writing that it's like catch-all okay let's say there are two there are multiple conditions to test for um like for Phi you have to do something for 4 you have to do something for three you have to do something and for everything else you have to do something for everything else getting it here are everything else is just one but what you have a lot of conditions for everything else what to do we are just going to give zero understood I hope you'll understood this can be a little tricky but I hope you'll understood so let's check for um let's check for this particular row okay this particular member so what is the formula for this one look at that here C3 this is the test condition C3 equal to 5. is it true if I calculate no it's false so if this is false what will excel do it will move on and check test the next condition logical test two I'll press F9 this turned out to be true so because logical Test 2 has become true it will just perform whatever has to be performed over there value if true what is the value we are supposed to take if it is true the one that is there in G4 the one that is there in G4 is taken okay over here you will see that when I click on enter under four 2500 would come it does not go and test the remaining conditions so when all the conditions that have been specified all the tests result in a false value we are hard coding true for everything else and giving 0. all right I hope the purpose of true and 0 and how it is handling everything else it's like catch-all for one scenario do this for the second scenario do this and for all the other scenarios catch all the other scenarios using this true condition and give zero that is the kind of thing which has happened here okay now by giving an absolute reference by providing the bonuses in a separate table what is the advantage that we are going to get suppose the bonus changes let's say tomorrow I decide to give five thousand okay suppose the bonus changes then I just have to change it over here nowhere else it will get automatically applied everywhere else you see suppose now suddenly we decided to give bonus even to those employees who are getting a performance rating of one they will get 100 rupees then what happened what will happen everywhere else it gets reflected okay but here also I'll have to change because here I didn't do it based on a test I did it based on the true condition in this case 100. and let me copy that formula down okay is ifs clear to everybody um no no it's not as simple as simply the number of brackets that you open and close rohini just to observe this particular formula every time we are giving an F and check putting the condition if if isn't it this is what happens if you are using nestatives IF inside and if then inside that another if then inside that another if this is and if inside another if not a separate if by itself independent one no these are necessary so innermost one will be computed first this also takes more time because the innermost if condition is tested first then this one will be looked for then this one like that it is basically checking a lot of things Necessities okay whatever sequence it goes but with ifs let me send you this formula in the chat and now compare it with this formula that we have written okay so when there are a lot of conditions ifs would be better when there is only uh there are a few conditions you can go with this dips if there is only one condition then you can go with a simple so true option that we give under ifs it is a nice it is a beautiful way to catch the default value very well you can cache the default value with that true option that is available okay so now let's move on to a few more if statements okay so this is my data the let's say there is some data wherein we there are different types of products like coffee tea herbal tea and espresso being sold in different states so four to five states of data I have here and they had me uh this is the statistics let's say of 2013 and 2012. okay for two different years I have the statistics of the sales of a variety of products tea coffee herbal tea and espresso across different states and in each of these states there is a Target that is given to them which is called as Budget Sales or simply we can rename it I'll just call it Target sales okay it will be easier that way okay let's say this is the Target sales and the actual sales also is there this is the let's say actual sales okay now if you look at difference I have obtained the difference between the actual sales and the Target sales look at difference it is basically this I'm taking the data in E2 and subtracting D2 okay I'm getting the difference by subtracting these two so if the difference is negative what would it mean if the difference is negative it could mean that the actual sales is less than the Target sales isn't it we could not reach the target the target was higher and actual made is lesser therefore the difference is negative and wherever the difference is positive it means the actual sales is better than the Target that was given okay we are saying that if the difference is positive it means that the actual sales is better than the target we have gone beyond the expectation the that particular state in the sales of that particular product has gone above the expectation it has gone beyond the Target and over performed it performed well okay now okay there is one question um yes the ifs statement that we had talked about right you can um you can reference even a cell over there okay swarupasya you can definitely reference the cell even without without hard coding the value so one moment I'll show you thank you then I'll have to just type it in okay you can simply lock it in place over there and get it and suppose I update this number now from zero suppose I change it and give a bonus of 100. you see how it got updated in the formula so rather than hard coding your value over here you can definitely go ahead and reference the cell okay make it an absolute reference so that whenever the value here changes accordingly it will get updated let's say we increase this to 5000. you see how it gets reflected here suppose I increase this to thousand it gets reflected suppose I increase this to to five okay that is the idea basically that is why we are referencing a table okay before we proceed to the next set of uh topics or things that we are going to cover is this clear whatever we have discussed about simple if nestative in IFS so let's understand this data we are just looking at the various products that are sold across different states across two different years we have the goal and we have the actual sales we have and difference is computed here it is not a member it has been computed if the difference is positive it means that state is doing well it has achieved the target if the difference is negative it means it could not reach the target now we are familiar with the max function and the Min function Max and Min are clear to us isn't it it gets the maximum value it gives the minimum value suppose if I just type in Max of the data present in this particular column it will give me what is the maximum sales it is seventeen thousand five not five over here now over here that is the maximum series okay so if I want to get the minimum I just have to replace Max with Min and it will give me the minimum sale value minimum value in that column which is one thousand one two nine all right so Max and Min present in a column we can get it but how do we get it based on certain conditions that is what I am doing here that is what I'm going to show you over here so I need to find out what is the maximum sales not for the overall data not for the entire data but I need the maximum sales made by the state of Colorado in the year 2013. okay in the year of 2013 what is the maximum sales that this particular state made then how to give that condition equal to we have a function called Max ifs now again this function may not be available on older versions it is there in uh relatively newer versions now what is it that we have to mention what are the arguments that it expects okay what are the arguments that it expects first max range we are asking it to look for the maximum value but in way where from where is it supposed to choose the maximum that range I need I wanted to choose the maximum value of sales present in the actual sales column so I'm going to selected do a command shift down Ctrl shift down for the whole thing to be selected so I want it to identify the maximum value present in this column e e 2 to e49 is my data from the data comma okay we need the maximum value in that range but based on what condition right we want it based on certain conditions so when I hit comma you see what is the next argument criteria range one okay I need to look for something in which range am I supposed to look for it there is a condition that you will mention and I am supposed to look for that condition somewhere where am I supposed to look for it in which range am I supposed to search for the true condition so that range let's say is the year okay look at the year column look at the ear column that is my range one and what is the criteria it is supposed to be 2013. so I'm going to select 2013. of course we could have typed 2013 but we are referring referencing to a cell so that we can change the value in this cell and accordingly we get the maximum value comma now it's asking what is the next criteria range okay now I want Excel to look at the data in the state column okay look at the data in the state column and then look for that particular state which is mentioned here and get the maximum just look at the formula what is it trying to tell let me redelete everything else it gave us but let me just remove everything else we will continue with this data set tomorrow also so look at what has happened I am telling give me the maximum value from where should I get you the maximum value from the data that is present in this range in this column actual sales okay and the maximum value has to be obtained from the max range based on what all conditions and I have two conditions what is my first condition it I need the maximum from the year 2013 second condition is for the state Colorado okay so where is your present that is the next range where Tableau has to look for it has to look for 2013 in this range so that is my criteria range one and what is it looking for for whatever year I specify in H3 what is the second criteria range whichever column has the names of the states and what am I asking it to look for I am looking for whatever state is mentioned in i3 if I do this and apply you can see it has obtained the maximum sales for Colorado from 2013 which happens to be eight thousand four four six I hope you'll understood okay now what what if my condition changes I want to find out what is the maximum sales of Florida it is bringing from 2013 the maximum is six thousand one not seven but I want to know what is the maximum sales of Florida in 2012. it changed it is five eight six seven so in 2012 if we look at Florida the maximum is five three five eight six seven this one here five eight six seven okay so by not hard coding the values in the formula what did we achieve we are referencing a particular cell to apply conditions whatever year I want I can mention here whatever state I might want I'll mention here it will look in the year range for that particular year in the state range for that particular State and it is getting the maximum value here we have given two conditions like that you can keep giving as many conditions as you want okay I hope you all understood this formula so we shall wind up here today our Focus was mainly to understand the if conditions we shall continue with the same thing tomorrow also we have learned that we have understood the difference between relative referencing and absolute referencing we understood simple if statement nestative statements ifs statement and one small example I have given you on using the maxifs so tomorrow when we meet we will look at minutes we will look at countifs we will look at sumifs okay after that we shall look at conditional formatting so that would be the agenda for tomorrow yeah that's it from my side you can let me know if you have any questions yeah about sharing the data sets yes please do join this WhatsApp group I will post all the data sets in the WhatsApp group are you all there in the WhatsApp group whoever is not there please join and I will be sharing the data sets through the WhatsApp group and if you have not yet subscribed to the Channel please do subscribe if you would like to refer back to this recording here's the link to the recording I'll send I'll resend the WhatsApp group link age today Tableau will be from 8 to 10 correct yeah Vijay Kumar we will do a recap on massive condition tomorrow okay in our recap we will cover maxif again post which we will look at minif we will look at countif and sumifs so that that will clarify all the doubts that you have so whoever is not there in the WhatsApp group you might want to join it and whoever has not yet subscribed please do subscribe to the channel you can watch today's recording day two's recording in this link okay which I have sent you all so you can use this link to refer back to today's recording do we get a certificate I don't think there is any certificate being provided however I will confirm it tomorrow okay anything else any of you otherwise we can end the meeting here and see you tomorrow the mccarthik Tableau is a paid session that is a paid session for people who have enrolled for time okay all right thank you all so much then we'll end the meeting here thank you bye