using excel for test metrics l.
Skip this Video
Loading SlideShow in 5 Seconds..
Using Excel for Test Metrics PowerPoint Presentation
Download Presentation
Using Excel for Test Metrics

Loading in 2 Seconds...

play fullscreen
1 / 32

Using Excel for Test Metrics - PowerPoint PPT Presentation

  • Uploaded on

Using Excel for Test Metrics. Agenda. Application to Metrics Understand the numbers Types of reports Gas gauge Progress chart Defect find / fix rate. Understanding Excel Basics Text to columns and back Relative and absolute values Names CountIf / sumIf Subtotal vs consolidate

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Using Excel for Test Metrics' - Gideon

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Application to Metrics

Understand the numbers

Types of reports

Gas gauge

Progress chart

Defect find / fix rate

Understanding Excel


Text to columns and back

Relative and absolute values


CountIf / sumIf

Subtotal vs consolidate

Paste special


Formulas – if, then

Audit formulas


Conditional formatting

  • 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
text to columns
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.
combining text fields
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.
relative and absolutes
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 $C$R, C$R, $CR, CR

name a cell reference
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

countif sumif
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", ">32", "apples".
  • Sum_range = the actual cells to sum.
subtotal vs consolidate
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
  • 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
  • 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
paste special
Paste Special

Accessible from the right click menu after a copy

Some options available from the from the icon after a paste

paste special14
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
  • 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
alternatives to autofill
Alternatives to AutoFill
  • Formulas, of course 
formulas if then
Formulas – if / then
  • Use conditional statements in formulas
    • Error handling (avoid div/0 errors)
    • Create “smart” formulas that can adjust on the fly
formula audit
Formula Audit
  • Select the formula you want to audit
  • Click on Tools, Formula Auditing, Evaluate Formula
  • Select a cell in the data range
  • Click on Data, Filter, Auto Filter
conditional formatting
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
understanding numbers
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
what does this mean
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 don’t mean much if you don’t know kind of defects are logged
  • Numbers alone are never enough
    • Tests can’t cover everything
    • Gut feel should still mean something – sometimes numbers are hard to grasp
types of metrics
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
how to build the gas gauge
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
how to build the progress chart
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
find fix rates
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