Any of the logical functions found in the FORMULA Tab can also be used in conjunction with the IF Function. For our Company, there were two discount codes for $25.00 off in two different advertisements. To show the Coupon Discount use the OR Function within the IF Statement because we are checking for Coupon Code “SAVE25” or “25OFF”.
- In Cell B7 enter =IF(OR(B6=”SAVE25″,B6=”25OFF”),”-$25.00″,0)
- The logical_test is saying if B6 equals SAVE25 OR 25OFF, then return a value of -$25.00 but if not then enter 0.
- Copy the formula across Row 7.
Note: When nesting logical functions inside each other it’s always best to work backwards. First, enter the OR Function and make sure that it is working, nest that within the IF statement afterwards.
Date and Time
The Date and Time Functions can be nested within IF functions just like the logical functions. In this example, the company has set a goal to send out the Desktop version within two days of the customer placing the order.
- Start with the DAYS function to count how many days are between the Ship Date and the Order Date. Enter =DAYS(B10,B9) into B11 and you should get a value of 0 because the Online version is downloaded instantly.
- Nest the DAYS Function into an IF Function by entering =IF(DAYS(B10,B9)<=2, “Pass”,”Fail) this function is saying if the number of days between the ship date and the order date are less than two, return a value of Pass, if not return a value of Fail.
- For our example, only the Desktop version will actually be shipped out, the IF function is used to first see if the Item is Online or Desktop. Just after the equal sign on the DAYS function enter IF(B2=”Desktop), then copy and paste the IF Function from Step 2 as the value_if_true, “N/A” and close out all parentheses. Now if the Item is Desktop it will perform the DAYS Function to yield a Pass or Fail, however, if the Item is Online it will yield an N/A.
- Copy the formula across Row 11 and all blank Items will also yield an N/A in Row 11.
The worksheet is now designed to auto-fill across rows as orders are entered. To protect the formulas and workability of the worksheet you could lock Rows 1,3,5,7,8, and 11 along with Column A. The user would enter only the Item, Quantity, Coupon Code, Order and Ship Date and the worksheet will auto-fill all the other cells.read more functions at http://dair2.com/excel-lookup-function/