“Of Mice and Megabytes Supplement”
EXCEL BASICS CONTINUED
Previous || Next
Previously, we created a simple Excel form containing one line item, its price and quantity, the sales tax, and the grand total. This week, we’ll alter the form into a quasi-receipt whereupon sales tax is calculated based on the ship-to state.
Open the sample file you saved from my previous column’s installment. If you don’t have it, click here download the file from this site (file name: rafu_example.xls).
WIDEN A COLUMN
First, widen one of the columns, column C: Place your cursor in the column heading area directly between the C and D. Your cursor will change shape from the large plus sign to a doubleheaded arrow bisected with a black line. Click ONCE and, while holding down the mouse button, move the doubleheaded arrow to the right. Widen the column width to roughly twice its original size. (You can also change the height of any column using the same technique.)
INSERT A ROW
Second, add a new row between the existing rows 2 and 3: In the row headings on the left side of the worksheet, click once on the number 3, resulting in the highlighting of the third row. Click the menu item Insert|Rows. There should be a new, blank row 3.
ADD A CONDITIONAL FORMULA
In cell C3, type Ship-to State. Then modify the sales tax formula by clicking cell D4. Change the formula to read: =IF(d3=”CA”,d2*0.0825,0). This is called a conditional formula and here’s how to decypher it: If the contents of cell D3 (the Ship-to State) equals “CA”, then calculate the sales tax at 8.25%. Otherwise, the sales tax is zero. It’s very important that the formula contains no spaces and that it includes an equal number of open and closed parentheses.
Test this formula by changing the contents of D3 to read CA. Note that entering CA, cA, or ca will produce the same results. However, entering any other text – Calif, AZ, MS -- produces a zero sales tax amount.
THE POWER OF FORMULAS
Formulas are powerful spreadsheet tools. For example, we could use the following to calculate then round off the sales tax amount:
Or, this formula provides for a 15% discount if the quantity is five or over:
- =if(c2>=5,(b2*c2)*.85,b2*c2)
Interpretation: If the content of C2 is equal to or greater than 5, then multiply the result of B2 times C2 times .85, otherwise, just multiply B2 times C2. The placing of the parenthesis is of paramount importance here.
For more information about formulas, select Help|Microsoft Excel Help. Then click the Answer Wizard tab and enter the word formula.
HOW TO COPY FORMULAS
You can copy formulas to other cells by using the cut-and-paste method. Or, try this: Place the cursor over the cell containing the formula. Click once and while holding down the mouse button, drag the cursor downwards, highlighting the cells you want to copy the formula to. Click Edit|Fill|Down. The formula copies itself to the highlighted cells while maintaining the relative cell referencing.
Re-printed from the Rafu Shimpo, August 2002. Copyright©2002 Rafu Shimpo. All rights reserved.
|
