• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Track the current cumulative result on different items over time

fred

Member
Greetings all,


I have a data on 3 columns

column A: 48 months (eg May-2007, Jun-2007, Jul-2007...Apr-2011)

column B: 4 items A, B, C and D randomly filled out 48 rows (1 item per cell, eg. A,C,C,D,B,A,B,A,C,B,D,D,A,B,C,...)

column C: Scores of 1-5 randomly


I created on column D to find out the cumulative scores for each of the line. So I know the most current cumulated scores on A, B, C and D towards the bottom of the list. This part is easy. But I can't come up with an answer on the two following questions.


Questions:

1. (Major) Is there an easier way to show on the same or a different worksheet where A, B, C, D stand on the cumulated scores on any given month? If this can be done then I can create just one graph showing how the scores changes over time on A, B, C and D


2. (Minor) The list will continue to grow as time passes. How will the formulae in Q1 differ if I keep adding new monthly scores?
 
1. You could put it all in a Pivot table/Chart and it will answer this for you

2. Use dynamic formulas to define the source for the Pivot table

=Offset(A1,,,Counta(A:A),3)
 
Thanks Hui! I thought of pivot too. but i thought there would be other approach to that.


I guess i'll use the pivot and create another data base where my boss can see the scores per item over time on a bar chart.


Thank you very much.
 
you could also use a number of dynamic named ranges, one for each A, B, C, D etc

they would count the number of A's between row 2 and row X

Where X would be based on a cell

You could then link the cell to a slider control and show an Interactive Bar Chart of each measure as you moved the slider

Have a look at a working example here: https://rapidshare.com/files/458745360/Fred_Bars.xlsx
 
Back
Top