a Math Owie
March 1, 2009 10:10 AM Subscribe
How do I determine the trend of situations with multiple scenarios and states week by week?
(go gentle please, I've already embarassed myself by stupidly posting this to the blue. Doh!)
I have a feeling this is an easy problem, but dang if I ain't blocked.
I'm building a scorecard that tracks the state of several situations, each with several scenarios inside it (let's say 4 situations with 8 scenarios each).
The situations each have 4 states they can be in, black, red, yellow and green. The goal is for all situations in a scenario to go green. What I need to do is understand whether a situation is getting better overall between this week and last.
That's a lotta lotta words, which might be better explained by looking at this spreadsheet:
http://spreadsheets.google.com/ccc?key=pjeoQuRDvgeqk9zY5a2XWLw (should be viewable by all)
A hint please? A clue? Thank you!
(go gentle please, I've already embarassed myself by stupidly posting this to the blue. Doh!)
I have a feeling this is an easy problem, but dang if I ain't blocked.
I'm building a scorecard that tracks the state of several situations, each with several scenarios inside it (let's say 4 situations with 8 scenarios each).
The situations each have 4 states they can be in, black, red, yellow and green. The goal is for all situations in a scenario to go green. What I need to do is understand whether a situation is getting better overall between this week and last.
That's a lotta lotta words, which might be better explained by looking at this spreadsheet:
http://spreadsheets.google.com/ccc?key=pjeoQuRDvgeqk9zY5a2XWLw (should be viewable by all)
A hint please? A clue? Thank you!
I'd simply compute a score for each situation and week, something like (3*green+2*yellow+red) and compute the difference between scores for subsequent weeks. If the score increases, you're up, if it stays the same, neutral, decreases - down. Tweak scoring algorithm to indicate different preferences.
posted by themel at 12:16 PM on March 1, 2009
posted by themel at 12:16 PM on March 1, 2009
You can rank your "outcomes" in a numerical order, right?
So you make black = 1, red = 2, yellow = 5 and green = 10 for example. Or just use 1 2 3 4. Then you put a legend on your spreadsheet, and just have a the situation names and categories on the left and then a column per week with a total at the bottom.
posted by yoz420 at 1:52 PM on March 1, 2009
So you make black = 1, red = 2, yellow = 5 and green = 10 for example. Or just use 1 2 3 4. Then you put a legend on your spreadsheet, and just have a the situation names and categories on the left and then a column per week with a total at the bottom.
posted by yoz420 at 1:52 PM on March 1, 2009
Best answer: Rename your scenario conditions from "black, red yellow and green" to "1, 2, 3 and 4."
Then in Week 1 we can say:
4 situations are condition 1
2 situations are condition 2
1 situation is condition 3
1 situation is condition 4
Then in Week 2 we can say:
2 situations are condition 1
3 situations are condition 2
1 situations are condition 3
2 situations are condition 4
Multiply the number of situations by the condition to give each week a score:
Week 1 = (4 x 1) + (2 x 2) + (1 x 3) + (1 x 4) = 15
Week 2 = (2 x 1) + (3 x 2) + (1 x 3) = (2 x 4) = 19
The trend is the difference between them:
Trend = +4
Or more broadly:
If trend is +ve, scenario is improving
If trend = 0, scenario is neutral
If trend is -ve, scenario is regressing
If you don't feel your scenarios are equally-distant from each other (i.e: if upgrading from red to yellow is not as significant as upgrading from yellow to green) you can give your situations different numbers to adjust for that.
posted by so_necessary at 2:36 PM on March 1, 2009
Then in Week 1 we can say:
4 situations are condition 1
2 situations are condition 2
1 situation is condition 3
1 situation is condition 4
Then in Week 2 we can say:
2 situations are condition 1
3 situations are condition 2
1 situations are condition 3
2 situations are condition 4
Multiply the number of situations by the condition to give each week a score:
Week 1 = (4 x 1) + (2 x 2) + (1 x 3) + (1 x 4) = 15
Week 2 = (2 x 1) + (3 x 2) + (1 x 3) = (2 x 4) = 19
The trend is the difference between them:
Trend = +4
Or more broadly:
If trend is +ve, scenario is improving
If trend = 0, scenario is neutral
If trend is -ve, scenario is regressing
If you don't feel your scenarios are equally-distant from each other (i.e: if upgrading from red to yellow is not as significant as upgrading from yellow to green) you can give your situations different numbers to adjust for that.
posted by so_necessary at 2:36 PM on March 1, 2009
Response by poster: I think I detect the trend in the answers, thanks for spelling it out step by step mr. necessary.
posted by daver at 3:34 PM on March 1, 2009
posted by daver at 3:34 PM on March 1, 2009
This thread is closed to new comments.
"I don't understand why situation 3 is neutral. What is the criteria?"
Situation 3 is neutral because we have 1 scenario progressing and 1 scenario regressing.
If you think of the statuses as being organized left to right, (Black, Red, Yellow, Green), moving to the right is progress. Moving to the left is a regression.
Trend should be the total progress - the total regressions.
I have a sneaking suspicion that this problem would be a lot easier if I listed out all the scenarios rather than trying to summarize them...
posted by daver at 10:15 AM on March 1, 2009