Daily Downtime Charts

Hello!

I have been asked to set up a "Dashboard" that automatically updates the graphs once the data has been entered. I have one sheet as the Data Entry and the next sheet as the Dashboard.

How do I go about doing this?

Can someone explain how I can do a bar graph say for the "HewSaw" section that updates automatically with the biggest downtime first in descending order when the new entry for each day is entered?

A graph that organising the worst downtime in order from all sections as a whole and updates automatically?

Thanks in advance for all contributors

Jonathan

Hi Jonathan,

First of all, I can tell you that your totals are not formatted correctly! You have used h:mm:ss - you need to change this to [h]:mm:ss. This will ensure that you see the total hours - at the moment, you are only seeing the remainder if the hours add up to more than 24, 48 etc.

Secondly, what are you trying to chart?

I assume the downtimes, with time on the vertical axis and dates on the horizontal axis.

I assume that you only want the TOTAL line charted not the 25 individual reasons.

Do you want to start at 9/6/2014 and have the chart expand daily? You have 90 days so far, and 165 days by the end. It will be a very crowded chart and you won't be able to read the x axis. What do you really want to show on your dashboard?

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Hello Dave

I have almost completed the Dashboard it needs a little fine tuning then I think am there

I have graphed top 10 reasons for each area but I am struggling to graph the top 20 downtime reasons from the site as a whole (top 20 from all areas) I could make a seperate table for this but then if I update my reasons the seperate table wont update then the graph

Any advice?

Thanks Dave

Jonny

Hello Dave

I have almost completed the Dashboard it needs a little fine tuning then I think am there

I have graphed top 10 reasons for each area but I am struggling to graph the top 20 downtime reasons from the site as a whole (top 20 from all areas) I could make a seperate table for this but then if I update my reasons the seperate table wont update then the graph

Any advice?

Thanks Dave

Jonny

Hi Jonny,

Not sure what you're after, but here are a couple of ideas using LARGE() and INDEX/MATCH to create the top 20 and get the description that matches.

If you look at the DATA INPUT sheet, you will see a few small changes:
1. I changed the format for the elapsed time in the HeeSaw section. I also changed the Downtime Percentage formula to get rid of the div 0 error.
2. I added a helper column to Column FS which gives the name of the work area as well as the reason.
3. I added a column C for the total time. I had to move this out of the way for the formula on the Top 20 Charts tab to work. (The Totals would be picked up in the top20 by the LARGE() formula).

This approach will change whenever you add new information to your Data Input Sheet.

This should give you a few ideas at least!

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Dave

You have been really helpful with your answers

I have attached my latest so far I cant seem to get the reasons for the Top 20 and I only have 4 times working in the "Top 20 Data" tab

I am using the D column for the Top 20 so I can get it in percentages

Do you know anything about Macros? My dashboard has a Macro to refresh it everytime I view it but it has seemed to stop working now?

Many Thanks

Jonny

I have the Top 20 working now!

Thanks very much!

p.s any help with the Macro issue much appreciated!

I have the Top 20 working now and the Macro fixed!

Thanks very much for the help! So useful!

Jonny

You're welcome Jonny!

I think from the last of your posts that you have everything sorted.

Don't forget to mark the thread as closed if you are now satisfied.

DAC