Objectives by the end you should be able to use logical functions and if not, and or use a combination of functions. Use nested if functions use the sum and average functions, use the AutoSum icon, use the formula text function, use the sum F and sum ifs functions, analyze functions using the functions argument window. Now, we will see how to use some of the logical functions that I have mentioned in Excel like and if not all, and we will try to get some interesting outputs out of them by mixing one function with the other in the sense we will use an IF function inside another IF function. I will try a combination of an R and end function together and see See what interesting output we can make? Let us begin let us consider an example. I like to order a number of things online even groceries and I like to keep a track of when I ordered them and when I received them and the total cost that I spent on them and now, here is my food supply inventory.

I have ordered some tomatoes, black beans, all purpose law etc. and the quantity for the same I have mentioned with price per unit. Now, what I want to do is to calculate the total cost. So, I would write equal to quantity that is cell B three into price per unit that is cell C three and hit on Enter after getting the total cost I would say Select this cell and clicking on this small box with the left mouse button, I would drag it down till the last cell. And I can see that Excel has calculated the total of all the quantity and the price and has given me the total cost of each. Next is I want to know how many items have I ordered.

So in this cell, I would write equal to sum, open bracket and select all these cells, close bracket and Enter. Now I get the number of items that I have ordered. Another way to go about this is by going to the top over here and selecting this AutoSum button. It automatically understands how many cells about it to include for calculating The total and it is showing over here with the dotted lines moving around the self selected, I will simply hit on Enter and I can see the total quantity ordered average price per unit can be calculated by writing equal to a VR AG average open bracket and selecting all these cells, close bracket and hit on Enter. Next is total cost, total cost is equal to some open bracket, sum of all these cells close bracket and enter. With this, my basic sheet is ready for further analysis.

Now let us challenge Excel and see if it can help us understand certain hypothetical scenario Use that we create. The first scenario is, I want to find out if the quantity of items ordered by me is more than 50 or not. So for that, what I'll do is equal to a function, open bracket. By the way, what we can also do is when we write if, and we can see that Excel has given us three options over here, we can choose the option by hitting on the tab key. So now let us write further, we have if the condition is that this cell is greater than 50. And if that is so then Excel should tell me that I have exceeded my budget.

And if that is not the case, and if the number of items ordered is less than or equal to 50, then it should tell me that I am within my budget, and then that in place, I will close the bracket and hit on Enter. So yes, it It is telling me that I have exceeded my budget. Let us analyze this function by going to the formula bar and clicking on effects over here, it tells me that the logical test is the cell B 16. That is the quantity of items ordered. If this condition is true, then it will tell me you have exceeded your budget. Else if it is false, then it will tell you that you are within your budget.

If we are writing any textual message in Excel as an output, we need to put it in double quotes. And that's what I have done over here. Had it been a numerical value, then we would have simply written like this, I will change the first part also. I will write one if it is greater than 50 and zero if it is not. And now it has showing that I have exceeded number So, this way we can use the IF function I will just get back to the previous sentence format. Now, with this basic if sentence in place, I would like to go further and find out if my total cost is exceeding my budget or not.

For that I would write a similar sentence equal to if pressing the tab key, this particular cell is greater than 500. If that is so, I would like Excel to tell me that my total cost is exceeding and if that is not the case, then my total cost is not exceeding my budget, close bracket and enter. So, here also Excel tells me that my total budget is getting exceeded, I will just change this total value to 1000. So, now, it is telling me that the total cost is not exceeding your budget what is If we want to find out in just one cell, that our quantity ordered or our total cost is not exceeding my budget and both have different conditions, the quantity ordered, if it is more than 50 then it should tell me that it is exceeding my budget and the total cost if it is more than thousand, then it should tell me that I am Exceeding my total cost.

So, this is a classic case of an or statement. In order to frame an or statement, we shall write equal to or logical one that is this greater than 50 comma because now we want to write another condition logical to is this greater than thousand for the output of this statement will be either true or false. In our case, it is true because quantity of items ordered is more than 50. Now had this condition been more than hundred, then this condition would have changed to false. What if I say that instead of an or statement, I would like to know, if my quantity ordered and the total cost both are more than a prescribed limit. For example, the quantity ordered is more than 50 and the total cost is going more than thousand.

In that case, let us see an and condition we would require an end formula and we'll write it this way, equal to and logical one, this condition greater than 50 comma, this condition is greater than thousand, close bracket and Enter. And now when we will press enter, it will tell us that this is False why, because although the quantity of items ordered is more than 50, the cost is not going to more than thousand. Hence the condition is false had this total been more than thousand, say for example 1100 then the condition would have converted to true. Now, let us complicate this a little bit more, what we will do now is we will try to write an or statement inside an if condition, what we want to find out is, if the quantity of items ordered is more than 50 or the total cost is more than 500 then it should tell me that I have exceeded my budget instead of writing these two if conditions.

It would be great if we could merge it in one like this equal to if or logic one Is this cell greater than 50 or this cell is greater than 500. If this is true, then tell me that I have exceeded else tell me that I have not exceeded close bracket enter. So, here we can see a very simple example of using an or statement inside an IF function. Similarly, we could have also put an end function inside an IF function like this. So, here it tells me that I have exceeded 50 in the quantity of items ordered and also have exceeded 500 in the total cost of items. And hence, it is showing that output is exceeded.

Now let us try to write a formula in which there is an if condition inside another if condition, such kind of formulas are known as nested, if let me create a scenario for you, I want to know if my number of items ordered are more than 50 and the total cost is going more than 500 then I want Excel to tell me that the number of items is more and the total cost is also more else, if the items are more than 50, but the cost is less than 500 then it should tell me accordingly also, if the items are less than 50, it should tell that the number of items is less than 50. In order to write this in a formula, please go like this equal to if the first test is whether the quantity is more than 50 or not. And if this is true, then I would like to check whether the total cost is More than 500 and not in order to put that we would write if this is greater than 500 then it should tell me that the quantity is more and the cost is more else it would tell me that the quantity is more, but the cost is less close the bracket and the last condition is that the quantity is less close bracket enter in our case, it is telling me Yes, the quantity is more than 50 and the cost is more than 50 and it is showing like this.

We will try different scenarios. What if the total cost was less than 500 say 450. So, now this condition has changed, the quantity is more but the cost is less If the quantity of items ordered is less than 50 say 45. So, here it is showing that the quantity is less in order to go deep inside this function and understand it, we can click this FX and it will tell us that the first condition is the B 16 greater than 50. And then if it is true, then there is another if condition which says that if the 18 that is the total cost is more than 500 then it should show a particular value and if it is less then it should show a particular value else if this condition the first condition is false then it will directly go over here and it will show that the quantity is less like this.

You can form different nested if conditions and create a formula that is desired by you. We have another interesting logical operator Known as the knot function, what this function does is it converts a logical output see a true statement into false and vice versa. Let us see how, let's take average price of unit This time, we'll write this in not statement like this equal to not c 17 greater than 10. In this case, what it will do is we know that it is greater than 10. So it should have been a true if we would have written an if condition. But if I hit on Enter, it will tell me false.

That means it is just converting a true statement to false statement and representing this can be really useful in some of the scenarios. One may require to see what Formula One has written in any particular cell. Excel has provided such a condition and that too, is a formula For example, if I want to see what formula I have written over here, I would have to go to the top and see in the function bar. Instead of that, I could write a formula next to it equal to formula, select formula text, select the joining cells, f 23, close bracket, and enter. Here, it converts the formula that was there in this cell and write it as a text. This can be very helpful in some of the cases.

Let us see how to use sum if and sum ifs function. What sum if does is it adds up a number of cells based on one single criteria. And what sum ifs does is it adds a number of cells based on multiple criteria. Let us take a very small example. I want to add the total cost of All those items whose quantity is more than five, in order to write this in a scenario, I would write equal to sum F, select the range, that is quantity on which we want to put some criteria comma, the criteria in double quotes greater than five comma, the column where we want to some close bracket and enter. So, here, we can see the total cost of all the items.

Let us now create a criteria in which we want to find out the sum of the total cost of all the items where the quantity is more than five and the price per unit is less than 10. We will write equal to sum ifs open bracket. Now here there is a difference is some f v had written the summing range at the end But for some ifs function, we need to write this coming range first, that is the cells that will be added up, comma, the cells on which we would like to put some criteria. So here, I will select the quantity column, comma, I will put the criteria for the same, greater than five. Second, the range of cells on which I would like to put some other criteria, comma, and here I would write the criteria, see less than five, close bracket, enter. Now he'll we can see that Excel is adding up all these cells, wherever the quantity is more than five, that is 123 and four cells, and the price per unit is less than five.

That is, among the cells. Only the cells that satisfy the condition are these too and hence the get the sub summary you have learned logical functions and if not, and or using a combination of functions, nested if functions, sum and average functions using the AutoSum button, formula text function, sum F and sum ifs function analyzing the functions using function argument window.