Using Excel for Test Metrics

1
Using Excel for Test Metrics
2
Agenda

• Understanding Excel
• Basics
• Text to columns and back
• Relative and absolute values
• Names
• CountIf / sumIf
• Subtotal vs consolidate
• Paste special
• Autofill
• Formulas if, then
• Audit formulas
• Filter
• Conditional formatting

Application to Metrics Understand the
numbers Types of reports Gas gauge Progress
chart Defect find / fix rate
3
Understanding Excel
4
Basics

• Auto sizing columns and rows
• Entering formulas
• Editing formulas
• Cell formatting
• View or hide formulas, gridlines, row and column
  headers
• Hyperlinks
• Worksheet / workbook relationships
• Status Bar calculator
• Help

5
Text to Columns
-- TO --

• Select the cells that you want to split apart (be
  sure the columns to the right are empty as they
  will be overwritten).
• Click on Data, Text to Columns.
• Choose Delimited.
• Choose Space (or the delimiter that you are
  using).
• Click Finish.

6
Combining Text Fields
-- TO --

• Click on a blank cell (usually to the right of
  the existing cells).
• Type in one of the following formula examples
• A1" "B1 to list first name, then a space, then
  the last name
• B4", "A4 to list last name, then a comma, then
  the first name
• The ampersand () character allows you to
  concatenate text fields together. The quotes
  will show up as text as in for a space or ,
  for a comma and a space.

7
Relative and Absolutes
symbols in formulas allow you to lock a
column or row. This is a must if you try to
copy and paste or autofill a formula. When the
cell is selected in the formula, press F4 to have
it cycle between CR, CR, CR, CR
8
Name a Cell Reference
Make your formulas more readable by naming
cells 1. Click on a cell 2. In the Formula bar
where it shows the Column / Row reference, type
in name and press enter. 3. Reference that name
in a formula. To view the list of names, click on
Insert, Name, Define
9
CountIf / SumIf

• CountIf countif(range,criteria)
• SumIf sumif(range,criteria,sum_range)
• Range the range of cells you want evaluated /
  counted
• Criteria the criteria in the form of a number,
  expression, or text that defines which cells will
  be added. For example, criteria can be expressed
  as 32, "32", "gt32", "apples".
• Sum_range the actual cells to sum.

10
Subtotal vs. Consolidate

• Subtotal
• Dynamic (if linked to source data, when source
  data updates, subtotals update)
• Ideal for a set number of rows (if referencing
  this data in another sheet)
• Adjusts existing data
• Consolidate
• Static (no change when source data is updated)
• Ideal for a varying set of rows (if referencing
  this data in another sheet)
• Results are stored in a different location

11
Subtotal

• Select a cell in the range
• Click Tools, Subtotal
• Choose the reference column
• Choose the function (sum, average, etc.)
• Choose the column(s) to subtotals

12
Subtotals
13
Consolidate

• Select a blank cell (usually a new sheet or below
  the existing data)
• Click Tools, Consolidate
• Choose the function (sum, average, etc.)
• Choose the range(s) to consolidate click on Add
  after each one
• Choose Left Column
• Note if you check create links to source data,
  it will do a subtotal

14
Consolidate
15
Paste Special

• Accessible from the right click menu after a copy
• Some options available from the from the icon
  after a paste

16
Paste Special

• Formulas pastes formulas only, no formatting
• Values pastes the results of the formula (can
  also use F9)
• Formats pastes the format (no data) can also
  use the format painter
• Skip blanks pastes the values of cells
  containing data, skips blank cells
• Transpose transposes the data converts rows
  to columns or columns to rows

17
AutoFill

• Type in the first two items in a series autofill
  the rest by dragging the cells
• Make your own autofill lists under Tools,
  Options, Custom Lists
• Use the CTRL to adjust the autofill
• Downfall the list is static

18
Alternatives to AutoFill

• Formulas, of course ?

19
Formulas if / then

• Use conditional statements in formulas
• Error handling (avoid div/0 errors)
• Create smart formulas that can adjust on the
  fly

20
Formula Audit

• Select the formula you want to audit
• Click on Tools, Formula Auditing, Evaluate
  Formula

21
Filter

• Select a cell in the data range
• Click on Data, Filter, Auto Filter

22
Conditional Formatting

• Based on the value of a cell / cells, adjust the
  formatting on the fly
• Click the cell / cells, click on Format,
  Conditional Formatting
• Note trial and error is necessary in here
  accepts absolute and relative values, accepts
  some formulas

23
Application to Metrics
24
Understanding Numbers

• "Get your facts first, then distort them as you
  please." Mark Twain
• "Facts are stubborn, but statistics are more
  pliable." Mark Twain
• Torture numbers, and they'll confess to
  anything.  Gregg Easterbrook
• 98 of all statistics are made up.  Author
  Unknown

25
What does this mean?

• Choice of formulas makes a difference
• Using averages vs. totals based on number of
  tests will show different results
• One view of numbers is never enough
• pass, fail numbers dont mean much if you dont
  know kind of defects are logged
• Numbers alone are never enough
• Tests cant cover everything
• Gut feel should still mean something sometimes
  numbers are hard to grasp

26
Types of Metrics

• Gas Gauge overview of pass, fail, blocked
  reports
• Progress Report historical view of pass, fail,
  and blocked reports
• Defect Find / Fix Report how fast are we
  finding defects vs. how fast are they being fixed
• Release Criteria what are the criteria to
  release the project
• Pass rate for all components must be 97 or
  higher
• Submit rate of high priority defects must be less
  than x of the average defects logged in the last
  x weeks.
• Stress test GUI errors per 1,000 hours must be
  less than .02

27
Gas Gauge
28
How to build the gas gauge

• Gather the raw data
• Organize it by test area / category / test type
• Create the following for each line
• complete of planned
• pass of planned -- pass of complete
• fail of planned -- fail of complete
• block of planned -- block of complete
• Determine whether to use of planned or of
  complete (may use both depending on the report)
• Determine whether totals should be averages or
  based on the actual number of tests
• Develop the gas gauge

29
Progress Chart Ideal world
30
Progress Chart real world
31
How to build the progress chart

• Determine the total number of tests possible for
  each area
• Determine milestones for 100 execution, 60
  pass, 80 pass, 90 pass
• Weekly, track the number of passes, fails, and
  blocks

32
Find / Fix Rate Ideal World
33
Find / Fix Rate Real World
34
Find / Fix Rates

• Track for all defects
• Track for just high priority (must fixes for a
  product to ship)
• Track number of defects submitted and resolved
  each week
• Chart the results