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