Multiple IF or Nested IF function in Excel
The multiple IF function example below can be applied in many situations. Sometimes we wish to calculate the bonus based not only on the sales performance but also on the designation of the consultant. In some cases we may wish to give an employee a conveyance allowance and we wish to do so based not only on his salary but also on his position in the company. Here we use multiple ‘if’, along with, maybe, ‘and’ and ‘or’ functions. Microsoft Excel provides many possibilities with the ‘if’ function to calculate and analyze data as demonstrated in the Excel training video. In case you find it difficult to follow the concept of the multiple ‘ifs’, please refer the ‘if’ function Excel training video.
=IF(B2>=90,”A”, IF(B2>=70, “B”, IF(B2>=60,”C”,”Work Harder)))
In the training video we have assigned grades to students based on the marks they obtain. If a student has greater than or equal to 90% marks, he is assigned grade ‘A’ and so on.
Code example: ‘=if(and(A2=”senior consultant”, B2=200000),B2*10%, B2*5%)’ The above code is interpreted as follows: If the sales person is a senior consultant and his sales performance is $200000 then give him a bonus that is equal to 10% of his sales else the bonus should be calculated as 5% of the sales. A2, B2 are input data and the function is applied in, let’s say, cell C2.
Another code example with modification: ‘if(and(A2=”senior consultant”, B2>=200000), B2*10%, if(and(A2=”junior consultant”, B2>=200000), B2*5%, “Not Applicable”))’. The code is interpreted as: If the sales person is a senior consultant and sales is greater than or equal to $200000, he receives a bonus of 10% of his sales but if he is a junior consultant and his sales is greater than or equal to $200000, he receives a bonus of 5% and if the criteria are not true, then Excel displays a message “Not applicable”. If you had more different designated sales people, you could continue with more ‘IF’ definitions!