Wednesday, April 6, 2011

Microsoft Excel: If a simple data analysis capabilities


Total most frequent functions in Excel to use to be very useful IF function to the Excel workbook tricks must be. IF function to test whether or not the condition is true or false and to perform actions such as calculation and data entry. Search Excel entries, how often, sort or manually enter additional data you must data filtering or audit? IF function can be evaluated automatically data or creating a condition-based.

You can condition formulas, values, or text.

Entries will be evaluated, cells, formulas, values, or text may be > results may answer formula, value, or string. An example may display click OK in the amount of the budget more than 5%, and otherwise show "over".

First, look at the IF function structure (Syntax). Other Excel features that we start = (equals), and then function name, such as parentheses it = (. Is the structure of the logic and IF functions.

= IF (testing/evaluating what you are, what to do if true, what to do if false)

Formal syntax and structure is a: = if (logical expressions, [true], [value_if_false])

For example, total more than $ 1,000 in our case, where the then entered in the $ 100 bonus, formula cells to calculate analysis; otherwise the bonus is not. Looks like this formula: = (B2 > = 1000, 100, 0) B2 appraised value. Like other formulas, results are updated when the value changes. And you can copy to calculate the additional value of this calculation down or sideways as well as other formulas.

Data = filter, such as to check the results of the IF function can be text entries you can (B14 = E14, OK, "auditing is required" and)... When this sample function in the values of two cells are the same results "OK", "required audit" answer is otherwise. Note that the enclosed text entry outside of quotation marks commas, quotation marks to create a string). Note that if you add a space after each comma is not the problem.

But wait... more! Nested function

All evaluation is only one condition to limit the two different actions. During the three or more potential for example, based on the value or range of levels must be calculated for different. This is nested or more if you call the function. To nest other functions within the IF function if necessary, to create a logical criterion. For example, the IF function, applicability result of total or average functions.

For example, if the following acts with these options.

Value is $ 25,000 less than the 10% multiplies. Value is at least $ 25,000 50000 dollars less than the 20% multiplies. Otherwise (value is $ 50 k than even great), multiply by 30 %

The formula looks like.

= IF (H3

Note: theworksheet function to the nest just an equal sign, the first nested in a function statement if required i.e. the second formula, does not equal signs have symbols. You can use up to 64 levels (levels 7 and Excel 2003 in the only with Excel 2010 and Excel 2007) many nesting, but nested formula is!

No complicated functions must be complex

Some are ready for the fun of many functions? IF function AND, OR and combination of which can function to create several conditions must be true (or), and vice versa every expression is true (and), only one expression to apply more detailed assessment is not (not) to appropriate.

It is a simple methods that describe how to create nested functions Excel function breakouts with own especially. This example is as follows: this is to test has been designed.

= IF (and (B2 > 750, B2 = = 1000, 100, 0)))

Cell B2 is 750 to 1000 less than (and) If you enter 75 cells. Cell B2 is more than 1000 years enter 100 cells. Otherwise, enter 0 (the part not true or false).

To add option in Excel IF function, grab in Excel IF function to create your own detailed reference to: http://www.softwarepro.com/tips/handouts.htm#excel








Dawn Bjork Busby Microsoft Office Specialist (MOS) master certified instructor and certified expert certified instructors, Microsoft application professionals (paid), Microsoft Office and as the software Pro ? and Microsoft Certified Trainer (MCT) is. Dawn software speakers share smart ways to use software effectively through her work as a trainer, consultant, and author of six books. Software for more tips, tricks, strategy and technology the found at http://www. SoftwarePro.com.


No comments: