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 condtion 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 formual 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 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.
|
|