How to present 50 different variables in %

[mrzoogle]

Hi Excel gurus,


I was doing this analysis where I have 50 different variables (columns) and they are %calculated (between 2 variables) on daily basis for 5 months (152 rows).


Now I am confused what would be the best way to present the data in a way that would stand out.


50 different graphs will look clear but I don't think that's the best approach


Can you all help me guide the best approach to this problem please.


I have attached an example file for you to reference.


http://www.mediafire.com/view/?5mu58b18gjdqbna


Thanks for your time.


Regards,


Z.

 

[fred]

May be if you can tell us what these "ID", "%" mean in real life, then we may have a better idea of what you are trying to present. it's dummy data anyway. so your secret is safe.

 

[mrzoogle]

Hi Fred,


Apologies, I missed it in the spreadsheet. They are different product ID's, and % are performance by day.


Thanks,


Z.

 

[Jack]

mrzoogle


I would think you want to summarise or highlight certain results by ID# and then have a summary to highlight across the 50 ID#s so you can see across this high number of things to manage. You would also want an interactive graph to see trends.


I would leave the data as is in it's own worksheet and create a new worksheet for all this analysis. First create a table downwards of ID nos and across the page have headers for the say up to 12 criteria important to you. The results in this table might be dates or row numbers from the data worksheet. Might be MAX or MIN results, days or row numbers where certain threshholds were met or not met, averages and other statistical measures. Perhaps days where the threashold was met 3 days in a row could be reported as text, whatever of course I have no idea what is important in your job. Because you will probably have multiple results for some results eg days where % was > than x% would may need results of dates or row numbers to be text so you can show any number of results. This will be the trickiest type of result to formularise. Conditional FOrmatting can also help here as you have 50 lines and say 12 columns so a lot of results and it will help in the highlighting task.


(note an alternative here is you could do this kind of analysis in say 12 lines above each of the ID columns in the data sheet by adding room at the top - its better to keep data and results seperate but if you are not an advanced user yet this might be easier to construct the formulas you need)


Secondly I would have another table that highlights across the ID#s and brings in or calculates the ID and stats important to see in one place - maybe it's lowest and highest performing ID#, best and worst average, maybe it's highest numbers of instances of being over or above a threshold. Whatever measures you need to draw your attention to ID#s that need to be seen.


As part of this table or near this table have an interactive graph with ideally a list box allowing you to select whichever ID#s you want to show on the one graph.


This site has examples of all the formulas and techniques you would need I would think but if you mark out what you want the end to look like you can tackle them one by one.


Good luck you will learn a lot doing this one.

John

 

[Hui]

Mr Zoogle


You may want to look at one of the techniques I use and have described here; http://chandoo.org/wp/2010/11/04/analysing-large-tables/


You can add all sorts of statistics to the retrieved Data as was described above instead of just charting it.

 

[mrzoogle]

Thanks Hui & John for the awesome ideas. I will try these techniques and will let you know the results.

Let me know if you have more idea on presenting huge dataset.


Regards,


Z.