How to Create a Conditional Formula in Excel 2007
When a user first experiments with the conditional formulas in Excel 2007, it can seem foreign. What does true and false have to do with a spreadsheet where number reign supreme? However, conditional formulas are actually quite simple once a person uses then a few times.
Open an existing or new Microsoft Excel 2007 document. Click "Start" from the main operating system menu. Then choose "Programs" from the start menu. Select "Microsoft Office" from the programs menu. Next, choose "Microsoft Excel" from the Microsoft Office menu. Now either choose "New" from the main menu or locate the existing Excel document to use for a conditional formula.
Establish the parameters for the conditional formula. For example maybe a person can create a conditional formula for an invoice discount. That way if the invoice is paid in under a certain number of days then a client would receive a percentage discount.
Structure the conditional formula. In this example say that if the invoice is paid in less than thirty days, the customer receives a five percent discount. The conditional formula would look like this: IF((C5-B5)<30,D5*0.05,0). Where C5 is the date the invoice was received, B5 is the date the invoice was issued, <30 is the date cutoff for the discount, D5 is the amount of the total invoice and 0 represents the non-discount condition of the formula.
Type the conditional formula in the spreadsheet. A person can either type the formula in or use the conditional function. To use the conditional function click on the "Formulas" tab and choose the "Logical" option from the format library.
Enter the conditional formula in the "If" formula function dialogue box. Once the "If" function dialogue box appears enter the previous formula parameter in the provided input boxes. In the "Logical_test" box enter "C5-B5<30", then enter "D5*0.05" in the Value_if_true box and finally enter "0" in the Value_if_false input box. The result of this should be the same as the typed formula.
Test and save the Microsoft Excel 2007 spreadsheet. Be sure that the conditional formula is working correctly by observing the results and check them against the known data. When the formula is satisfactory, save and close the spreadsheet.