Making Choices in Excel
Every day we make choices based on simple tests. If it is raining, we carry an umbrella; if it is someone's birthday, we give them a gift. You can make choices like this in your Microsoft Excel worksheets, too.
One way is by building formulas that use a test and its outcome to establish a worksheet; Excel will respond appropriately. For example, if a company charges $5 to ship a box that weighs 3 pounds or less and $10 if it weighs more than 3 pounds, you can create an If function that tests the value of a cell containing the box's weight and calculates the shipping cost. If functions can be nested inside each other to very high levels of complexity.
Other Excel formulas that can make choices include the Vlookup function, which takes a value and searches the first column in a table for the largest value that is not bigger than the value it is looking for. Vlookups can easily return data from even very large tables. And the Choose function takes a value in the range 129 and returns the appropriate entry from its list of values. These examples show an If, a nested If, a Vlookup, and a Choose function at work.
The IF function
An If function has the syntax =if(test, value if true, value if false) and can be used to calculate shipping charges in our scenario, in which a company bases them on the package's weight. In place of values, you can also include formulas in an If function to make calculations based on the test results, as shown in the next example, which tests to see if tax is applicable and, if it is, calculates it.
A Nested If
This nested If function calculates shipping in the situation where a box of 3 pounds or less ships for $5, one from 3.1 to 5 pounds is $10, and one over 5 pounds is $20. You can nest If functions to quite complex levelsbut they become increasingly difficult to understand when you do. In many situations a Lookup formula can be used instead and will be much easier to read and understand.
Here a data table contains weights and the associated shipping price. The Vlookup (for Vertical lookup, as opposed to Hlookup, for Horizontal lookup) function refers to the weight in the first column; it seeks the largest value that is less than or equal to the value being looked up. It then returns the corresponding shipping price from the second column. The syntax of the function is =vlookup(value to look up, table array, column of values to return). Vlookup functions can often replace complex nested If functions.
The Choose function returns an item based on its position in a list. The first value in the formula is the list position you want to returnor a cell reference or formula that points to itand the values are the list items. For example =choose(2,"apple","orange","banana") returns the second value in the listorange. Here the Choose function returns the name of the day of the week. The syntax of the Choose function is =choose(index num, value1, value2, etc.).
blog comments powered by Disqus