Solved

Excel Charts to display summary and average in charts

Posted on 2011-02-15
59
855 Views
Last Modified: 2012-05-11
Experts:

So I have another question concerning my  dashboard  This is related to creating a list box that will populate with the total count and average of EACH selected ID#.  I have setup the dashboards with a listbox but just need an expert's guidance on what is the best way to approach this.

Thank you!   NOC-Reports-r18.xlsm
0
Comment
Question by:Maliki Hassani
  • 30
  • 29
59 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 34902851
Please elaborate a bit more.  what is count and what to average - where is the data, etc.?  Share an example, please.  Where is the output going?  Is that in the spreadsheet already?

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34902925
No problem..  I was assuming you would ask for more details.  So this request is due to a question at work today, where a manager wanted to see the total amount of DAC tickets created -C4.  I was able to show the weeks and values but not the total sum or the average of those weeks.  So basically,each chart currently shows weeks and the value of the weeks.  Example: week 48 = 200, week 49 = 205 etc.  I am looking to add to the dashboard a place to include the sum and average of each line item that is being selected.  The output right now is setup to display above the larger chart for each sheet in the dashboard.    Let me know what you think?
0
 

Author Comment

by:Maliki Hassani
ID: 34902928
Correction: below the chart is where I have a listbox setup..
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34903359
Other E-E's may be able to help in the interim.  I might have a chance to look at this tonite, otherwise, late tomorrow afternoon.  I have a big workshop to facilitate and some modeling due - gotta do REAL work, lol

:)

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34903366
Ha Ha..   No problem!  I am working on other dashboard stuff.  No rush...  :)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34903815
In Executive Rollup Data, I see no data for Week 48... (Column DR is blank)... In Surveillance, I'm having a hard time seeing how you come up wtih 200 - is that average or count????  still confusing.  You say you want to sum each line item - so that line item would be a series of weeks, based on the timeline, so youor clarifying comments are still confusing.  But, there's an out - there's some work you can do to get you half-way there in one step.

1st step - for each of the datasets you want count and averages on, go to the datasheets and create a column with exactly that for each of the items that could be selected.  Make one for each time period, just like you made the chart series for each time period.  In fact, you can use those ranges to do your operation on.  E.g., countif(rangereference, > 0) could give you the count.  sumif(rangereference)/countif(rangereference,>0) would give you the average.  Once you have that put together, post your spreadsheet.

2nd step - if you get this far, the next step would be to create a table or some type of box to put your metrics in - so think about how you want the info displayed.

3rd step - after the chart updates, it needs to fire a subroutine to update that info.

Get to step 1 and think about how you want the info displayed, then we can chat if/when you get stuck from there.

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34903853
Thanks I will work on that..  By the way the 200 and week 48 was used as an example.  This was populating the list box to help give you an idea of what I was planning on doing..  I will work on this tomorrow.  Later!
0
 

Author Comment

by:Maliki Hassani
ID: 34911847
Newest Version Is attached..  I havn't made any changes yet in reference to this question..  I was working on the "Monthly dashboard".  Will get to it later tonight.
 NOC-Reports-r20.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34932869
let me know when you're ready to proceed.

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34934099
Hi Dave:  Man, it has been a long but productive week.  Hope work went well...  
So here are my updates:
- Added scrolling features to the listboxes1. (couldn't get the rest of the boxes to scroll) :(
- Charts automatically show trend lines
- Made small charts dynamically pullin with what managers requested
- Monthly tab includes 4 reports that I run.  (Need to change the tab name to 30/60/90 since some reports are not monthly but 90 days.
- Fixed error handling issues

Now for the purpose of this post.  Yesterday, I tried to figure out what is the best way to display the total counts and averages without bring headaches to the both of us..  When you look at the attached spreadsheet you will see that "Executive Rollup Data"  now has several column names added (Far right).  3 groups: 1-Counts, 2-Averages, 3-Combo of both 1 and 2. Column 3 is the bread and butter of the listbox3 in the Executive dashboard.  Listbox3 displays all of the headers like ID#, 1yr, 6m, 3m, 1m, and the values of each going down.  The listboxes still has the functionality to select and chart is updated too.  

So here is the issue.  I created SAMPLE data (numbers) to show this concept.  However, I couldn't seem to get the offset formula to work with a count and the average.  If you could please take a look and provide me with a working formula I will be able to duplicate this on all sheets.  I mean I tried yesterday and I just couldn't get it it work.  :(  I have an engagement dinner to go to later but will definetly keep in contact if you get a chance to review this further.  Thanks Dave!
 NOC-Reports-r21.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934554
What other boxes are you trying to scroll (sheet and which one)?

Where can I find the sample data that shows what the output looks like?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934556
ok - I think I found it in your table...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934571
ok - give the basics here.  What is your DEFINITION of COUNT.  Explain cell JK7.  Also for Average -cell JR7.  I'm missing something or we're speaking different lingo.  I think of COUNT as the number of items - e.g., counting # of weeks that have data, etc.  You obviously see COUNT as something different and I can't seem to reverse engineer your calculus...

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34934577
Module2 contains the main script and each disboard has the call out at top. Trying to get listbox 2 and 3 to scroll aswell.
0
 

Author Comment

by:Maliki Hassani
ID: 34934578
Module2 contains the main script and each disboard has the call out at top. Trying to get listbox 2 and 3 to scroll aswell.
0
 

Author Comment

by:Maliki Hassani
ID: 34934596
Don't worry about my made up numbers I just through something in the there.

Ok count is the total amount for 1 month, or 3 months, 6 months.


Example. Let's make up fake values.  Week1 = 50 week 2 = 70, week 3= 50. so the count would be 170 and average =57
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934602
PS I modified to make run MUCH faster - actually appears instantaneous:

Sub unhideall()

Dim WS As Worksheet
Application.ScreenUpdating = False
    For Each WS In Worksheets
        WS.Visible = True
    Next
Application.ScreenUpdating = True
End Sub

PS - why are you hooking mouse hover over the listbox?  The functionality of doing multiple selections and scrolling seem to be messed up with this.  What are you trying to do?

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34934612
Can't really answer that.. Code came like that... I researched.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934660
I'll look at your count/average now.  If you would, share with me what you were trying to do with that listbox.  If you can't answer that, why are you using the code? :)

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34934688
I just wanted to have the ability to scroll in the listboxes. Using the the mouse instead of the listbox slider. :)
0
 

Author Comment

by:Maliki Hassani
ID: 34934689
I just wanted to have the ability to scroll in the listboxes. Using the the mouse instead of the listbox slider. :)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934716
Ok - updated for COUNT and AVERAGE.

When you have a cell displaying a range, the way to use that range in a formula is with the INDIRECT function.  Average = SUM / COUNT - you might want to put an IF on the count to ensure no DIV!#0 errors!

Dave
NOC-Reports-r22.xlsm
0
 

Author Comment

by:Maliki Hassani
ID: 34934724
Sa-weet!  I will take a look later.. Thanks..
0
 

Author Comment

by:Maliki Hassani
ID: 34934725
Sa-weet!  I will take a look later.. Thanks..
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934731
Your listboxes DO scroll.  Still not sure why you're hooking in the mouse.  Let me backtrack to the posted question I didn't respond as others had.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34934743
Ok - I found the question and know what you're trying to do - scroll with mouse wheel.  Let me review what you have...


:)  Really jazzing it up!
0
 

Author Comment

by:Maliki Hassani
ID: 34935612
Yes the scroll with the mouse..  Gotta try to impress the big boys.. lol
0
 

Author Comment

by:Maliki Hassani
ID: 34935641
Wow, I never even heard of indirect in a formula.  Man you have some cool tricks.. Looks great!
Now, i am not sure why listboxes 1 is not allowing me to scroll , I think I will use the old script.  Checking out the ListBox1_DBLClick now...
0
 

Author Comment

by:Maliki Hassani
ID: 34935669
OMG, I never realized that the double click to clear was there all along.  I am such a goof!
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 41

Expert Comment

by:dlmille
ID: 34935670
you have to turn off the mouse hook stuff for the double click to work with ListBox1.  I never got the wheelmouse to work and stopped looking at other options (I was about 80%) when you said you got it to work no problem... lol
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34935675
and the below code looks wierd - VK_UP for both???

            If wParam = WM_MOUSEWHEEL Then
                LowLevelMouseProc = True
                If lParam.mouseData > 0 Then
                    PostMessage _
                    lListBoxhwnd, WM_KEYDOWN, VK_UP, 0
                    PostMessage _
                    lListBoxhwnd, WM_KEYUP, VK_UP, 0
                Else
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34935677
If you still have problems, here's a link that makes more sense (using rotation of wheel mouse):  http://www.xtremevbtalk.com/archive/index.php/t-178071.html

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34935679
I guess I am not sure what the mouse hook means?  Is that where you can see the selection move down as you mouse scroll down?
0
 

Author Comment

by:Maliki Hassani
ID: 34935684
It is weird because with the new version you provided only the executive dashboard has a scrolling issue the rest of the spreadsheets are fine.
0
 

Author Comment

by:Maliki Hassani
ID: 34935692
I am just going to use version 21 and make the adjustments to unhideall and the formulas (indirect). Everything else should be good to go. Any new ideas for the dashboard?
0
 

Author Comment

by:Maliki Hassani
ID: 34935694
What are your thoughts on how the dashboard is turning out?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34935943
Looking good, Lance.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34936409
I was able to get v22 to work by destroying LB1 and recreating it.  The double click works just fine to delete selection... Will work on your next query coming, re: doing the other listboxes with wheel control...

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 34936668
Here's the repaired v.22

Dave
NOC-Reports-r22.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34936750
I'm ready for the next post :)

Cheers,

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34937777
Great stuff!  Very pleased with everything.  Just curious how hard would it be to only show the data in listboxes3 for only the specific ID# that is being graphed?  I am thinking that showing all the ID data at once is too much and would be better under the graph, showing only what is selected to be viewed.  If this is headache buster then we can move on.  
Here is the attached file with some template mods. NOC-Reports-r23.xlsm
0
 

Author Comment

by:Maliki Hassani
ID: 34937794
More thoughts on this:  Then that would mean that the buttons for 1w, 1m, etc would have to update the Count/Avg.  Perhaps this is ian dea that should be thrown away.. lol
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938833
Let me think on this.  Are you posting a query on getting the other Listboxes to work with mouse wheel?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938839
I have a v.23 you're going to want :)
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938853
On your question with the count/avg.  What data would you want to plot - count or average?  No changes to option buttons would be necessary, I don't think, as that just sets the date range for the plot.  All you need to do is create the DATA for that date range (all you've done at this point is display COUNT / AVERAGE for a date range - that already aggregates it).  What data would you plot?  If you know that, you have to create that series in your datasheet, and then have pointers to it (ranges for 1 year, 6 months, 3 months, etc.).  Then, you call the chart just like any other, except with that range.

Got it?

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34938873
The data that I would want to plot is not determined by the count/avg.  The count/avg is just an add on to the selection of the desired chart a person wants to display.  
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938882
Ok - what data would you want to plot, if someone were to select on that listbox?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938893
Would it be the same as ListBox1??
0
 

Author Comment

by:Maliki Hassani
ID: 34938896
Basically, someone clicks on example E2 and clicks on the "Update Chart" button.  We will have an update chart and a listbox that will show what ever the count and average for what is being shown in the chart.  They click on 1 month button, now we have an updated chart and a listbox that shows the data for the 4 weeks. etc
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938899
I guess that's what its already set up to do.  Perhaps I don't understand the question?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938905
When I read back to your post, your idea is to select something to plot (if a single item), then listbox3 displays that data table?

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34938908
Right now we have listboxe3 showing ALL of the executive ID's with count and averages for 6 columns (1 month , 2 month etc). I don't want to see all that info.  Only display such info based on what is being graphed, and what button is being selected.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938917
So the table in ListBox 3 has only the data range that is selected off the main chart object buttons, but for all the ID's?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938920
Just like it is now, but 2 columns - ID, and Data (but only for the date range currently being plotted)?
0
 

Author Comment

by:Maliki Hassani
ID: 34938921
Yes, that is correct..  

I just changed my idea which would include multiple selections if the case, and only display the count/ averages based on what weeks are shown in the graph.
0
 

Author Comment

by:Maliki Hassani
ID: 34938930
Yes, and only the ID's that are being used to plot the graph at that time.
0
 

Author Comment

by:Maliki Hassani
ID: 34938941
I will be moving list boxes 3 some where near the main chart and making it transparent.  This will allow more dashboard space for other info..  How hard is this task?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34938962
not hard at all.  check your email.

Dave
0
 

Author Comment

by:Maliki Hassani
ID: 34938965
oh.. lol
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now