Excel offers various logical operators to check if a particular condition is True or False. In conjunction with logical functions (IF, AND, XOR, OR, NOT) in Excel, these logical operators check multiple conditions at once and perform operations on the cells accordingly. In this segment, you will see some more logical functions in Excel.
In the next video, let’s hear from Vivek Dhumal to get a better understanding of the IF function and how it is used to perform operations on the data.
In the video above, we saw the following process:
- The general syntax for an IF statement is:
IF(logical_test, [value_if_true], [value_if_false]) - We created a column called shipping type, which categorises the order as local or outstation. If the origin city and destination city matched, it is a local order. Otherwise, it is an outstation order. The function
IF(D2=N2, "Local", "Outstation")performs this categorisation. - Next, we split the total logic_cost into local_cost and outstation_cost using the formulas
IF(Q2="Local", P2, 0)andIF(Q2="Outstation", P2, 0), respectively.
In the next demonstration, you will learn a few more logical functions that Excel offers.
To summarise, we used the IFERROR function to avoid error values while calculating the local and outstation costs. We then calculated the count of orders and total/average cost using the COUNTIF, SUMIF, and AVERAGEIF functions. The image below shows all the formulas used in the demonstration.
Here are some of the key pointers we saw through this segment:
- In the sheet, column Q contains the shipping type as local or outstation; columns R and S have the local and the outstation costs, respectively.
- In this example, we considered only a single criterion to get the count/sum/average. If we want to check multiple criteria in Excel, there are functions called SUMIFS, COUNTIFS, and AVERAGEIFS. The function COUNTBLANK is used to get the number of blank values in each column.
- In the demonstration, Vivek applied a filter on the logistics cost and showed the error values
#VALUE!. Even if we hadn’t been aware of the error values and calculated the total logistic cost, we would have got the error#VALUE!. In such cases, you can trace errors using the Evaluate Formula or Error Checking feature in the Formula tab to find why the error has occurred. You had already learnt about this in your preparatory module on Excel.
Attempt the following questions to check your understanding of the concept.
Note: Use Superstores_data to solve the following questions.
Let’s move on to the next segment, where you will learn about Conditional Formatting.