- 661 Views
- Uploaded on

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

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

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

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

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

$ 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

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

- 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

- 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

Accessible from the right click menu after a copy

Some options available from the from the icon after a paste

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

- 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

- Formulas, of course

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

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

Filter

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

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

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

- 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

- 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

- 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

- 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

- 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

Download Presentation

Connecting to Server..