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

Using Excel for Test Metrics

782 Views Download Presentation
Download Presentation

Using Excel for Test Metrics

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

  1. Using Excel for Test Metrics

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

  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 $C$R, C$R, $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", ">32", "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. 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

  13. Paste Special Accessible from the right click menu after a copy Some options available from the from the icon after a paste

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

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

  16. Alternatives to AutoFill • Formulas, of course 

  17. Formulas – if / then • Use conditional statements in formulas • Error handling (avoid div/0 errors) • Create “smart” formulas that can adjust on the fly

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

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

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

  21. Application to Metrics

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

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

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

  25. Gas Gauge

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

  27. Progress Chart – Ideal world

  28. Progress Chart – real world

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

  30. Find / Fix Rate – Ideal World

  31. Find / Fix Rate – Real World

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