Using excel for test metrics
1 / 32

Using Excel for Test Metrics - PowerPoint PPT Presentation

  • Updated 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

Agenda l.jpg

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

Basics l.jpg

  • 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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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

Subtotal l.jpg

  • 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

Consolidate l.jpg

  • 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 l.jpg
Paste Special

Accessible from the right click menu after a copy

Some options available from the from the icon after a paste

Paste special14 l.jpg
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

Autofill l.jpg

  • 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 l.jpg
Alternatives to AutoFill

  • Formulas, of course 

Formulas if then l.jpg
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 l.jpg
Formula Audit

  • Select the formula you want to audit

  • Click on Tools, Formula Auditing, Evaluate Formula

Filter l.jpg

  • Select a cell in the data range

  • Click on Data, Filter, Auto Filter

Conditional formatting l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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 l.jpg
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