Can someone design this Google Sheets bug list template for me?

post by Senarin · 2019-02-09T06:55:51.652Z · score: 4 (3 votes) · LW · GW · 4 comments

This is a question post.

Contents

Project:

My local rationality group is going to perform a structured test of the 'Hammertime' sequence. We are making a "bug list" of various problems in our lives for this sequence. (For those familiar, this variant of the bug list is loosely inspired by the FMEA) We assign, on a scale of 1-5, three different values to each bug: "Impact" (on our life), "Frequency" (of occurrence, or need of occurrence for things we need to do), and "How cheap is the cost" (in time/effort/money, where 1 is most expensive, and 5 is cheapest). The products of "Impact" and "Frequency" will all be totaled together, resulting in our "RPN" ("Risk Priority Number"), and this is the number we will be testing before and after performing the sequence, to measure its success.

Goal:

As I adjust the assigned values of each bug, eliminate bugs, and add new ones, I want the spreadsheet to calculate the product of the first two values, and auto-sort the list by this number, giving me a live priority list for addressing the bugs. I also want the ability to sort the list by the product of all three numbers, allowing me to optionally factor in the difficulty. This will result in 6 columns:

1-Name of Bug

2-Impact

3-Frequency

4-"Cheap Cost?"

5-Product of 2 and 3 (default auto-sorting)

6-Product of 2, 3, and 4 (optional sorting)

As well as a final row (or column, or whatever) with the total sum of Column 5's values (the "RPN").

Problem:

I suck at spreadsheets, having never learned them. I already made an attempt, but failed. I could put the time and effort into learning this, but I don't foresee using this skill again, so it seems more optimal to use someone else's skills for this.

answer by Michael Roman · 2019-02-09T12:56:09.025Z · score: 6 (5 votes)

Here is a sheet that should work. You can sort by clicking the green triangle in Column E and sorting Z->A. Or once you copy this to your account, I created a script you can add that will auto sort after you add the cost value.

https://docs.google.com/spreadsheets/d/1fOunDA5uFF7juVjyP2ZUKAWp0j7X_AJ1WuIdnI_HS7I/edit?usp=sharing

4 comments

Comments sorted by top scores.