Question: Can I expand the usefulness of other functions that perform logical tests?
Answer: Yes, with the AND function
Why: One can test many different conditions by nesting the And function with the IF statement.
Process (Excel 2003, 2007 and 2010):
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.
One common use for the AND function is to expand the usefulness of other functions that perform logical tests.
1. To qualify for a bonus an employee must exceed $1000 in sales and have 3 or more years in service to the company. Furthermore they should also belong to the Sales Department.
2. If those initial conditions are met the deserving employees will get $100 for each year served plus 5% of their total sales amounts.
3. Select cell F2 and type the following formula;
=IF(AND(C2>1000,D2>=3,E2=”sales”),(C2*5%)+ (D2*100),”No Bonus”)
4. Press enter and copy the formula down
5. The result will be as given below
Enjoyed this Excel Tip?
Join over 13,000+ subscribers in receiving our weekly tips and tricks in your inbox and to get access to all our Excel on Steroids Tips and Tricks e-books.