Using the ‘IF’ function in combination with ‘AND’ in Excel

Using the ‘IF’ function in combination with the ‘AND’ function in Excel: Generally the function ‘IF’ returns a value if some condition is true and another value if the condition is false. For example, if the value of an item is greater than ‘200’, we may wish to return a value like ‘expensive’ in the cell and in case the value of the item is less than ‘200’ we may like to return the value ‘affordable’ in the Excel worksheet cell. We would write the formula in an appropriate cell as ‘=if(A1>=200,”expensive”,”affordable”)’.

However, we may have a situation where two or more conditions must be true before we assign a value. For example, we may have different categories of items that we sell. We may wish to markup all electrical items with a price between ‘100’ and ‘350’ by 40% and for items that are priced below ‘100’ and are in the electrical category , we may wish to markup the sales price by 50%. We may also wish to markup all electronic items in a different manner. Let’s take another example: We may wish to give a manager with a salary greater than or equal to ‘25000’ a house rent allowance of 50% and another manager with a salary of less than ‘25000’ a house rent allowance of ‘60%’. We may also wish to give the manager living in a large city a different allowance than a manager living in a small city. How do we calculate such things using an Excel formula? We combine the power of the ‘IF’ function with the ‘AND’ function. The training video describes the process. Please note that if the condition compares text, we put the values in quotes.

Further reading:

MS Excel: IF Function (WS)