Posts Tagged ‘Excel 2010 Training’

h1

THE ‘IF STATEMENT’

November 22, 2010

The ‘If Statement’ is often used for basic reporting in Excel. It provides an easy method of assessing the truth of an  assertion and then returning one of two values depending on whether the assertion is true or false.

Syntax: IF(logical_test, [value_if_true], [value_if_false])

In this example an ‘If Statement’ tests whether the value ‘MyDate’ is larger or equal to the date above the target cell; the result is either an actual or a budget value depending on the truth of the assertion:

Advertisements
h1

RETURNING A VALUE WITHIN A RANGE OF VALUES

October 5, 2010

Everyone knows how to return a value from a list where a matching value is found in a corresponding list; we use a vlookup function. Well with 2 tweaks a vlookup can also return a value where the matching value falls within a range of values, like in the case of a tax table or sales commission table.vlookup - approx. match

The following points are critical to the successful use of this technique:

1) If range_lookup parameter (last parameter) is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value (first parameter) is returned.

2) If range_lookup is either TRUE or is omitted, the values in the first column of table_array (second parameter) must be placed in ascending sort order; otherwise, vlookup might not return the correct value

h1

NEW IN SUMMARY – EXCEL 2010

September 8, 2010

The following is a list of features new to Excel 2010 and which will be explored in future blog posts:

  • 64-bit version
  • Sparkline charts (very groovy)
  • Slicer (new tool in Pivot Tables)
  • Office button changes including an ‘Office Backstage’ screen
  • Conditional formatting enhancements
  • Image-editing enhancements
  • Screen capture tool (for inserting images)
  • Paste Preview (before committing the paste)
  • Ribbon customization (after much anticipation)
  • Calculation engine efficiencies
  • VBA Enhancements

Craig Juta, Excel Trainer