1 / 32

# Using Excel for Test Metrics - PowerPoint PPT Presentation

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

Related searches for Using Excel for Test Metrics

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

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

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

### Using Excel for Test Metrics

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

Paste special

Autofill

Formulas – if, then

Audit formulas

Filter

Conditional formatting

### Understanding Excel

• Auto sizing columns and rows

• Entering formulas

• Editing formulas

• Cell formatting

• View or hide formulas, gridlines, row and column headers

• Worksheet / workbook relationships

• Status Bar calculator

• Help

-- 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.

-- 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.

\$ 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

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: =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

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

• 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

Accessible from the right click menu after a copy

Some options available from the from the icon after a paste

• 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

• Formulas, of course 

• Use conditional statements in formulas

• Error handling (avoid div/0 errors)

• Create “smart” formulas that can adjust on the fly

• 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

• 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

### Application to Metrics

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

• 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

• 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

• 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

• 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

• 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